Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have two excel sheets with million records and they have missing value and type with one to many mapping in rows. Below is table for reference:
Table heading: item, item#, method, value.
Additional columns "type match" and "value match" to decide if "type" and "value" is identified in both the sheets.

Sheet B
item	item#	type	method	value
a	    1	    dog     exp	    1
a	    1	    cat     exp	    1
b	    2	    mat	    exp	    1.1
b	    2	    dat	    std	    1
b	    2	    fat	    exp	    1
b	    2	    cat 	exp	    0.8
b	    2	    dog 	exp	    1.1
c	    3	    dat	    std	    1
c	    3	    fat	    exp	    1
d	    4	    fat	    exp	    1.25
d	    4	    cat 	exp	    1.1
d	    4	    dog 	exp	    0.9
f	    6	    sat	    exp	    1

Master Sheet A
item	item#	type	method	value	type match	value match
a		1		dog 	exp		1.1		TRUE		FALSE
a		1		cat 	exp		1		TRUE		TRUE
a		1		bat		exp		1		Not Found	NA
a		1		rat		std		1.1		Not Found	NA
b		2		rat		std		1		FALSE		NA
b		2		bat		exp		1		FALSE		NA
b		2		mat		exp		1		TRUE		FALSE
b		2		dat		std		1.2				
b		2		fat		exp		0.8				
b		2		cat 	exp		1.1				
b		2		dog 	exp		1				
b		2		yak		exp		1				
b		2		track	std		1				
b		2		owl		exp		1				
b		2		ram		exp		1.25				
b		2		sat		exp		1				
c		3		mat		exp		0.9				
c		3		dat		std		1				
c		3		fat		exp		1				
c		3		cat 	exp		0.83				
d		4		fat		exp		1.1				
d		4		cat 	exp		1				
d		4		dog 	exp		1				
e		5		mat		exp		1.09				
f		6		sat		exp		1.1				
g		7		rat		std		0.9				
g		7		owl		exp		0.83				
g		7		ram		exp		1				
g		7		sat		exp		1				
g		7		mat		exp		1.1				


Please suggest any alternative/suggestions thanks!

What I have tried:

I have tried to do vlookups, hlookups and index match but when i do auto-fill, it breaks due to missing item in Sheet B
Posted
Updated 28-Jul-22 4:56am

1 solution

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
 
Share this answer
 
v2
Comments
Ajeet S 2021 28-Jul-22 14:44pm    
Not sure if i follow.
To clarify on the doubt, what i had presented is a sample of data in sheet a and sheet b and both are table values types.
The columns, type match and value match are where i intend to get TRUE or FALSE or NOT FOUND or NA for missing match. I hope this clarifies.
I have tried CONCAT, but for concat only will help if you have string type values in column but i also need the value column to be matched to get the delta. If you notice there is many to one mapping in sheet a and sheet b. If you notice as an example item a is present in sheet b for certain ‘type’ but has different values in column ‘value’.
CHill60 28-Jul-22 15:05pm    
Concat will work with numerics, at least it did in the version of excel I use.
Looking at your example you are not just matching in type or value but on item + type and item + type + value, so you would need two working columns and two match formulas.
Your logic for true/false/not found/na is not clear
Ajeet S 2021 29-Jul-22 5:23am    
To clarify further, let's take first row in both sheet for item a. In sheet a it has 4 rows for item a and 2 rows in sheet b. Now on the sheet A i added columns 'type match' and 'value match' to decide if it matches in sheet b. To calculate 'type match' we match if 'type'='dog' in both the sheets for 'item' a and hence mark as "TRUE". If this is true, check what further value is in column 'value' in both sheets. If it is correct, mark column 'value match' to "TRUE" else "FALSE"

Here 1 row in Sheet A has 1.1 and 1 in sheet B hence FALSE in column 'value match'.

I hope this makes sense. The table with those examples are self explanatory.
CHill60 29-Jul-22 5:31am    
You have item# 1, rat = not found
Item# 2, rat = false
There is no type rat in your sample, but two different results
As I said, your logic is not clear.
However, the technique I have described will help you identify matches/missing items
Ajeet S 2021 1-Aug-22 1:33am    
Yes, for item# 1 rat is 'not found' as there is no row corresponding to item# 1 in sheet B.
So the logic should compare if item#1 is present in sheet b, if so, check whether 'type' is present or not and so on and so forth....


Anyways, i tried your CONCAT logic, and i get #NA.
=MATCH(Table4[@CONCAT],Table5[[#ALL],[value]],0)

Table 4 is sheet A (without 'type match' and value match' columns) and Table 5 is Sheet B.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900