How to compare two set of list with formula

[john5599]

Hi,

How to compare 2 list with find match and mismatch with formula. Can some help me.



Sheet4[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LED TRIM PANEL LIGHT 5W RD 6500K[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Lts Glannz Solar 1.5w Led[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10 pcs[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LED TRIM PANEL LIGHT ROUND 20W 6500K[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]LTS Solazen Led Solar Wall Light 3.2W Wh[/TD]
[TD="align: right"]1 pcs[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LED TRIM PANEL LIGHT SQUARE 20W 6500K[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LED Trim Panel Light 5w RD 6500K[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]16 pcs[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LTS GAINZ SOLER 1.5W LED[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LED Trim Panel Light Square 20W 6500K[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]8 pcs[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LTS RANGER 30 RECHARGE LED TORCH 3W[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]NU Bulb 10W865 B22 3 Star[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]96 pcs[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]NUBULB 10W 865 B22 3 STAR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]96[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LED Trim Panel Light ROUND 20W 6500K[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]8 pcs[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]WALL LIGHT STYLO UPTO 14W CGL B22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]LED Light Lumeno D/L 5W CDL RD[/TD]
[TD="align: right"]12 pcs[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Wall Light Stylo Upto 14W CFL B22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]8 pcs[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]LTS Ranger 3D Recharge LED Torch 3W[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10 pcs[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

 

[Snakehips]

@john5599

Provided that you don't have duplicates then, maybe like....

Excel 2010
	D	E	F	G	H	I	J	K
1
2	Match	LED TRIM PANEL LIGHT 5W RD 6500K	16			Lts Glannz Solar 1.5w Led	10 pcs	Miss
3	Match	LED TRIM PANEL LIGHT ROUND 20W 6500K	8			LTS Solazen Led Solar Wall Light 3.2W Wh	1 pcs	Miss
4	Match	LED TRIM PANEL LIGHT SQUARE 20W 6500K	8			LED Trim Panel Light 5w RD 6500K	16 pcs	Match
5	Miss	LTS GAINZ SOLER 1.5W LED	10			LED Trim Panel Light Square 20W 6500K	8 pcs	Match
6	Miss	LTS RANGER 30 RECHARGE LED TORCH 3W	10			NU Bulb 10W865 B22 3 Star	96 pcs	Miss
7	Miss	NUBULB 10W 865 B22 3 STAR	96			LED Trim Panel Light ROUND 20W 6500K	8 pcs	Match
8	Miss	WALL LIGHT STYLO UPTO 14W CGL B22	8			LED Light Lumeno D/L 5W CDL RD	12 pcs	Miss
9						Wall Light Stylo Upto 14W CFL B22	8 pcs	Miss
10						LTS Ranger 3D Recharge LED Torch 3W	10 pcs	Miss
Sheet1

Cell Formulas
Range	Formula
D2	=IF(E2="","",IF(SUMPRODUCT(($I$2:$I$10=E2)*($J$2:$J$10=F2&" pcs"))=1,"Match", "Miss"))
K2	=IF(I2="","",IF(SUMPRODUCT(($E$2:$E$8=I2)*($F$2:$F$8&" pcs"=J2))=1,"Match", "Miss"))

Hope that helps.

 

[john5599]

Hello Sir

Thank you for your reply, I am attaching the link of sample workbook. For me I am not getting the right answer as you showed on the screen shot. I am getting only mismatch at both side,

can you Please check.

https://www.dropbox.com/s/sh07iaocnjgsadg/Book123.xlsx?dl=0

 

[Fluff]

The problem is that col J does not have values like 16 pcs, it is simply formatted to show that.
Therefore try
=IF(I2="","",IF(SUMPRODUCT(($E$2:$E$8=I2)*($F$2:$F$8=J2))=1,"Match", "Miss"))

 

[john5599]

The problem is that col J does not have values like 16 pcs, it is simply formatted to show that.
Therefore try
=IF(I2="","",IF(SUMPRODUCT(($E$2:$E$8=I2)*($F$2:$F$8=J2))=1,"Match", "Miss"))

Thanks for your help

 

[Fluff]

You're welcome