index? match tool?

[Elise Nerlove]

hi,

I have 2 very long lists and i need to make sure my list matches the master list.....if it doesnt i need it to highlight or say "NO" or something...i am thinking i use a match tool? index? below is an example...help is much appreciated as i do NOT want to do this manually lol. thanks!!! -elise

master list my list
UPC TYPE UPC TYPE
12345 specialty 12347 specialty
12346 specialty 12345 specialty
12347 non-specialty 12348 specialty
12348 specialty 12346 specialty

 

[Andrew Poulsom]

You can use a formula like:

=IF(SUMPRODUCT(--(A$2:A$5=C2),--(B$2:B$5=D2)),"YES","NO")

in E2 copied down.

 

[taurean]

Different approach but should work (using MATCH),
=IF(MATCH(C2,$A$2:$A$5,0),IF(D2=OFFSET($B$1,MATCH(C3,$A$2:$A$5,0),0),"OK","NOTOK"),"NOTOK")
And your hunch (using INDEX and MATCH),
=IF(INDEX($A$2:$B$5,MATCH($C3,$A$2:$A$5,0),2)=$D3,"OK","NOTOK")

 

[snoopyhr]

Or maybe this approach,

Excel Workbook
	A	B	C	D	E	F
1	Master List			My List
2	UPC	TYPE		UPC	Type
3	12345	specialty		12347	specialty	Not on the Master list
4	12346	specialty		12345	specialty	Matched to Master
5	12347	non-specialty		12348	specialty	Matched to Master
6	12348	specialty		12346	specialty	Matched to Master
Sheet1

My question though , what if the no. of items on the master is more or less than the no. of items on your list. Just curious what outcome you are looking for in that case.

 

[Elise Nerlove]

hmmmm, im getting "no match"/"no"/"not a match to master" for EVERY single one i must be doing something wrong, grrrrrrrrrrrrrr!!!

 

[snoopyhr]

Elise Nerlove,

If you are using the formula I posted you need to enter it as an ARRAY, that is instead of pressing enter, you need to hold down CTRL SHIFT and then press ENTER.

 

[Janb]

Hi,

Not the solution at this moment, but I suggest to buy "Slaying Excel Dragons" written by Mike Girvin (ExcelIsFun).

Page 300 starts with "MATCH Function (to check whether item is in master list)".

Kind regards,

Jan

BTW: I'm a great fan of ExcelIsFun and MrExcel. But Mike got the ability to explain everything right and clear for the beginner and intermediate user. Hopefully everyone will buy his book. It's really the best one (beginner / intermediate).

The more experienced user will prefer Bill's approach (I think). But again, both are the best Excel masters I know at this moment (just my opinion).

 

[GaryWMn]

Mike Girvin's solution will be slick.

Here is another way to accomplish what you want using COUNTIF(). If you add the formula to both tables, you will see the comprehensive view of whether everything on one is also on the other.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">master list</td><td style="text-align: right;;"></td><td style="text-align: right;color: #0000FF;;"></td><td style="text-align: right;;"></td><td style=";">my list</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #0000FF;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;;">UPC</td><td style="font-weight: bold;;">TYPE</td><td style="font-weight: bold;color: #0000FF;;">OnMyList?</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">UPC</td><td style="font-weight: bold;;">TYPE</td><td style="font-weight: bold;color: #0000FF;;">OnMaster?</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12345</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;">12347</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;background-color: #FFFF00;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12346</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;">12345</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">12347</td><td style=";">non-specialty</td><td style="text-align: right;color: #0000FF;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;">12348</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">12348</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;">12346</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">12349</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;background-color: #FFFF00;;">FALSE</td><td style="text-align: right;;"></td><td style="text-align: right;;">12344</td><td style=";">specialty</td><td style="text-align: right;color: #0000FF;;">FALSE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=COUNTIF(<font color="Blue">A:A,E4</font>)<>0</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=COUNTIF(<font color="Blue">E:E,A8</font>)<>0</td></tr></tbody></table></td></tr></table><br />

Try it out and let us know if it works for you.

Gary

 

[Elise Nerlove]

ok, im sorry to keep on this issue lol, but everything i am trying is not working. . .ive reached out to an IT person in house, but they are out until tmrw.

Gary, i dont think your formula is giving me exactly what i want to see....i have a list of 1668 individual UPCs coded as specialty/non-specialty as well as jar/tub....I was just given a list of the same UPCs and coding options coded by a contracted IT worker....however, i'm noticing some discrepencies between our lists....so i need to identify each UPC where we have a coding discrepency....i was hoping this would be like a one minute project using some fancy excel function coupled with conditional formatting....but its been dragging out all day

snoopyhr - i used ctrl, shift, enter...still getting all "Not on the Master list"

Janb, thanks for the tip...excel is my life, i will be purchasing.

 

[snoopyhr]

Here is same but using range name and conditional formatting, hope it works for you.

Excel Workbook
	A	B	C	D	E	F
1	Master List	*	*	My List	*	*
2	UPC	TYPE	*	UPC	Type	*
3	12345	specialty	*	12347	specialty	Not on the Master list
4	12346	specialty	*	12345	specialty	Matched to Master
5	12347	non-specialty	*	12348	specialty	Matched to Master
6	12348	specialty	*	12346	specialty	Matched to Master
7	11234	specialty	*	11234	specialty	Matched to Master
8	12221	specialty	*	12220	specialty	Not on the Master list
9	11110	specialty	*	11110	specialty	Matched to Master
Sheet1

#VALUE!

</td></tr></table></td></tr></table> <table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >F3</td><td >1. / Formula is =MATCH(D3&E3,UPCMASTER&TYPEMASTER,0)</td><td style="background-color:#00ff00; ">Abc</td></tr><tr><td >F3</td><td >2. / Formula is =A3=""</td><td style="">Abc</td></tr><tr><td >F3</td><td >3. / Formula is =ISERROR(MATCH(D3&E3,UPCMASTER&TYPEMASTER,0))</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr><tr><td ><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#ff0000;background-color:#fffcf9; color:#000000; "><tr><td ><b>Names in Formulas </b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Name</td><td >Applies to</td></tr><tr><td >F3</td><td >TYPEMASTER</td><td >=OFFSET(Sheet1!$B$3,,,COUNTA(Sheet1!$B:$B)-1)</td></tr><tr><td >F3</td><td >UPCMASTER</td><td >=OFFSET(Sheet1!$A$3,,,COUNT(Sheet1!$A:$A))</td></tr></table></td></tr></table></td></tr></table>