I have no idea what you are trying to do (hint: you should improve your question to make it clearer) but when I am trying to compare worksheets I do the following
- Add a new column in each sheet
- Formula in column is
= CONCAT(A1:E1)
(adjust the column range as necessary)
- That column will then contain a long string consisting of all of the data from those columns
- drag down to the bottom - if you have gaps you can jump to the gap with Ctrl-Downarrow and manually drag over the gap,
- alternatively, sort the data so that the gaps are "removed"
- Alternatively, select columns A-E and use Ctrl-T to convert the range into a table. Right click, Insert Column Right, add the formula and it will be auto-populated to the bottom of the sheet, including the gaps
- You can then do an Match to identify the mismatches E.G.
=MATCH(Table1[@Column6],Table2[[#All],[Column5]],0)
If you didn't want to use CONCAT you could calculate a hashvalue for each row and compare those - see Community Wiki answer at
VBA hash string[
^]
EDIT: Here is more detail about how to use the formulae
On Table4 add two columns
ItemType
with the formula
=CONCAT([@[item'#]],[@type])
and
ItemTypeValue
with the formula
=CONCAT([@[item'#]],[@type],[@value])
You can then use those results to derive your required information e.g. other columns with
=IFNA(MATCH([@ItemType],'Sheet B'!F:F,0),"Not Found")
=IFNA(MATCH([@ItemTypeValue],'Sheet B'!G:G,0), "False")
These are just examples - you will probably need to extend the formulae further. Note the use of the
IFNA
function to handle #N/A - I.E. "Not found" scenarios