Compare and match multiple cells

FaezMH

New Member
Joined
Oct 14, 2019
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

I have a problem of doing comparison for product code by using array as shown below. Previously I am using mainly OR and EXACT to do the job as I do it by 1 sheet per CPU model. If I were to combine multiple CPU models in a sheet, I figured it out that I will need array to do the job. I tried out with Index and Aggregate but still could not get it right. Allow me explain the scenario:

1) the intention is to input the CPU model at H2 and from there it will look for all the related data (invoice and Prod_code1~3) at the Record List. Once each new Prod_code is entered, it will compare with the last entry of that particular model by invoice and prod_code. Will have to take into account of model because similar model series may share some parts as shown in the case of Opti780 and Opti781.
2) the result will return as Match or otherwise and check whether the new prod_code entered at H3, H5 and H7 is New or otherwise.

I understood this is may be tough and I have search forums and I guess not many wanna do it this way. Hope to get reply from the experts here.

Many thanks.
Faez

compareall.xlsx
ABCDEFGHIJ
1RECORD LIST
2CPU_modelInvoiceProd_code1Prod_code2Prod_code3Enter CPU_modelopti780RESULT (compare to last entry in the list)NEW CODE? (Y/N)
3opti701016212014-11232D178B-0310134003-32L82F3Enter latest Prod_code12014-11232D3MATCHN
4opti701016232014-11232D178B-0310134003-32L82F3
5opti701016192014-11232D178B-0310134003-32L82F3Enter latest Prod_code278B-031014NOT MATCHN
6opti78034552014-11232D178B-0310134003-32L82F3
7opti78034582014-11232D178B-0310134003-32L82F3Enter latest Prod_code34003-32L82F9NOT MATCHY
8opti78034532014-11232D178B-0310134003-32L82F3
9opti78034572014-11232D178B-0310134003-32L82F3
10opti99027002014-11232D178B-0310134003-32L82F3
11opti99027092014-11232D278B-0310134003-32L82F3
12opti99027072014-11232D278B-03101A4003-32L82F3
13opti701016252014-11232D278B-0310134003-32L82F3
14opti701016202014-11232D278B-0310134003-32L82F3
15opti78034582014-11232D278B-0310134003-32L82F3
16opti99027052014-11232D278B-0310134003-32L82F3
17opti701016252014-11232D278B-0310134003-32L82F3
18opti99027052014-11232D278B-0310144003-32L82F5
19opti78034582014-11232D278B-0310134003-32L82F6
20opti78135512014-11232D378B-0310134003-32L82F7
21opti78034532014-11232D378B-0310134003-32L82F6
22opti78135522014-11232D378B-0310134003-32L82F7
23
24
25
Sheet5
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
Hi Faez,
I don't quite understand your example. How are the desired results for Prod_code2 and Prod_code3 shown in your example related to opti780? Those two product codes aren't found in the data table associated with opti780.

Perhaps I misinterpreted something. Are your inputs H3, H5, and H7 for the latest product codes? And then when you enter a model in H2, would you like the blue cells in columns I and J to shown whether those latest product codes match any entry in the data table for the H2 model (opti780 in this case) and whether the product code has been used elsewhere in the table for any model (i.e. it would be a new code if it doesn't appear in the data table)?
 
Last edited:

FaezMH

New Member
Joined
Oct 14, 2019
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi Faez,
I don't quite understand your example. How are the desired results for Prod_code2 and Prod_code3 shown in your example related to opti780? Those two product codes aren't found in the data table associated with opti780.

Perhaps I misinterpreted something. Are your inputs H3, H5, and H7 for the latest product codes? And then when you enter a model in H2, would you like the blue cells in columns I and J to shown whether those latest product codes match any entry in the data table for the H2 model (opti780 in this case) and whether the product code has been used elsewhere in the table for any model (i.e. it would be a new code if it doesn't appear in the data table)?

Hi KRice,

So sorry, I missed out telling you guys that H2, H3, H5 and H7 is referring to the latest or new list I received. This is because my actual list is long however to quick check these 4 pieces of info will allow me to make a decision. Your understanding is correct. If the code entered is not match and not in the existing list then it will be considered as new.

Thank you.
Faez
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
Let me know if this is what you had in mind. The user inputs are the blue cells and the outputs are shown in the green cells.
MrExcel20200908.xlsm
ABCDEFGHIJ
1RECORD LIST
2CPU_modelInvoiceProd_code1Prod_code2Prod_code3Enter CPU_modelopti780Result (compare to last entry in list)New Code? (Y/N)
3opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code12014-11232D3MATCHN
4opti70102014-11232D178B-0310134003-32L82F3
5opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code278B-031014NOT MATCHN
6opti7802014-11232D178B-0310134003-32L82F3
7opti7802014-11232D178B-0310134003-32L82F3Enter latest Prod_code34003-32L82F9NOT MATCHY
8opti7802014-11232D178B-0310134003-32L82F3
9opti7802014-11232D178B-0310134003-32L82F3
10opti9902014-11232D178B-0310134003-32L82F3
11opti9902014-11232D278B-0310134003-32L82F3
12opti9902014-11232D278B-03101A4003-32L82F3
13opti70102014-11232D278B-0310134003-32L82F3
14opti70102014-11232D278B-0310134003-32L82F3
15opti7802014-11232D278B-0310134003-32L82F3
16opti9902014-11232D278B-0310134003-32L82F3
17opti70102014-11232D278B-0310134003-32L82F3
18opti9902014-11232D278B-0310144003-32L82F5
19opti7802014-11232D278B-0310134003-32L82F6
20opti7812014-11232D378B-0310134003-32L82F7
21opti7802014-11232D378B-0310134003-32L82F6
22opti7812014-11232D378B-0310134003-32L82F7
Faez
Cell Formulas
RangeFormula
I3I3=IF(INDEX($C$3:$E$22,AGGREGATE(14,6,(ROW($A$3:$A$22)-ROW($A$2))/($A$3:$A$22=$H$2),1),1)=H3,"MATCH","NOT MATCH")
J3J3=IF(COUNTIF($C$3:$C$22,H3)=0,"Y","N")
I5I5=IF(INDEX($C$3:$E$22,AGGREGATE(14,6,(ROW($A$3:$A$22)-ROW($A$2))/($A$3:$A$22=$H$2),1),2)=H5,"MATCH","NOT MATCH")
J5J5=IF(COUNTIF($D$3:$D$22,H5)=0,"Y","N")
I7I7=IF(INDEX($C$3:$E$22,AGGREGATE(14,6,(ROW($A$3:$A$22)-ROW($A$2))/($A$3:$A$22=$H$2),1),3)=H7,"MATCH","NOT MATCH")
J7J7=IF(COUNTIF($E$3:$E$22,H7)=0,"Y","N")


Click on the clipboard icon in the upper left (at intersection of row/columns) to copy this working example to your clipboard for convenient pasting into your workbook.
 

FaezMH

New Member
Joined
Oct 14, 2019
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Let me know if this is what you had in mind. The user inputs are the blue cells and the outputs are shown in the green cells.
MrExcel20200908.xlsm
ABCDEFGHIJ
1RECORD LIST
2CPU_modelInvoiceProd_code1Prod_code2Prod_code3Enter CPU_modelopti780Result (compare to last entry in list)New Code? (Y/N)
3opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code12014-11232D3MATCHN
4opti70102014-11232D178B-0310134003-32L82F3
5opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code278B-031014NOT MATCHN
6opti7802014-11232D178B-0310134003-32L82F3
7opti7802014-11232D178B-0310134003-32L82F3Enter latest Prod_code34003-32L82F9NOT MATCHY
8opti7802014-11232D178B-0310134003-32L82F3
9opti7802014-11232D178B-0310134003-32L82F3
10opti9902014-11232D178B-0310134003-32L82F3
11opti9902014-11232D278B-0310134003-32L82F3
12opti9902014-11232D278B-03101A4003-32L82F3
13opti70102014-11232D278B-0310134003-32L82F3
14opti70102014-11232D278B-0310134003-32L82F3
15opti7802014-11232D278B-0310134003-32L82F3
16opti9902014-11232D278B-0310134003-32L82F3
17opti70102014-11232D278B-0310134003-32L82F3
18opti9902014-11232D278B-0310144003-32L82F5
19opti7802014-11232D278B-0310134003-32L82F6
20opti7812014-11232D378B-0310134003-32L82F7
21opti7802014-11232D378B-0310134003-32L82F6
22opti7812014-11232D378B-0310134003-32L82F7
Faez
Cell Formulas
RangeFormula
I3I3=IF(INDEX($C$3:$E$22,AGGREGATE(14,6,(ROW($A$3:$A$22)-ROW($A$2))/($A$3:$A$22=$H$2),1),1)=H3,"MATCH","NOT MATCH")
J3J3=IF(COUNTIF($C$3:$C$22,H3)=0,"Y","N")
I5I5=IF(INDEX($C$3:$E$22,AGGREGATE(14,6,(ROW($A$3:$A$22)-ROW($A$2))/($A$3:$A$22=$H$2),1),2)=H5,"MATCH","NOT MATCH")
J5J5=IF(COUNTIF($D$3:$D$22,H5)=0,"Y","N")
I7I7=IF(INDEX($C$3:$E$22,AGGREGATE(14,6,(ROW($A$3:$A$22)-ROW($A$2))/($A$3:$A$22=$H$2),1),3)=H7,"MATCH","NOT MATCH")
J7J7=IF(COUNTIF($E$3:$E$22,H7)=0,"Y","N")


Click on the clipboard icon in the upper left (at intersection of row/columns) to copy this working example to your clipboard for convenient pasting into your workbook.

Hi KRice,
Exactly, this is what I had been looking for. Thank you so much. I checked my earlier formula and indeed there were couple of mistakes. This really helps.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,893
Office Version
  1. 365
Platform
  1. Windows
@FaezMH
If your version of Excel 365 has the FILTER() function, then here is a shorter alternative for the column I formulas.

FaezMH 1.xlsm
ABCDEFGHI
1RECORD LIST
2CPU_modelInvoiceProd_code1Prod_code2Prod_code3Enter CPU_modelopti780Result (compare to last entry in list)
3opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code12014-11232D3MATCH
4opti70102014-11232D178B-0310134003-32L82F3
5opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code278B-031014NOT MATCH
6opti7802014-11232D178B-0310134003-32L82F3
7opti7802014-11232D178B-0310134003-32L82F3Enter latest Prod_code34003-32L82F9NOT MATCH
8opti7802014-11232D178B-0310134003-32L82F3
9opti7802014-11232D178B-0310134003-32L82F3
10opti9902014-11232D178B-0310134003-32L82F3
11opti9902014-11232D278B-0310134003-32L82F3
12opti9902014-11232D278B-03101A4003-32L82F3
13opti70102014-11232D278B-0310134003-32L82F3
14opti70102014-11232D278B-0310134003-32L82F3
15opti7802014-11232D278B-0310134003-32L82F3
16opti9902014-11232D278B-0310134003-32L82F3
17opti70102014-11232D278B-0310134003-32L82F3
18opti9902014-11232D278B-0310144003-32L82F5
19opti7802014-11232D278B-0310134003-32L82F6
20opti7812014-11232D378B-0310134003-32L82F7
21opti7802014-11232D378B-0310134003-32L82F6
22opti7812014-11232D378B-0310134003-32L82F7
Sheet2
Cell Formulas
RangeFormula
I3I3=IF(LOOKUP("z",FILTER(C3:C22,A3:A22=H2))=H3,"","NOT ")&"MATCH"
I5I5=IF(LOOKUP("z",FILTER(C3:C22,A3:A22=H2))=H5,"","NOT ")&"MATCH"
I7I7=IF(LOOKUP("z",FILTER(C3:C22,A3:A22=H2))=H7,"","NOT ")&"MATCH"
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Peter's improved version reminded me of an alternative to find the last matching position for making the match comparison. This borrows from Peter's construction, but omits the FILTER function and instead searches for a "2" which will not exist, so the last matching position will be returned...
MrExcel20200908.xlsm
ABCDEFGHI
1RECORD LIST
2CPU_modelInvoiceProd_code1Prod_code2Prod_code3Enter CPU_modelopti781Alternative
3opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code12014-11232D3MATCH
4opti70102014-11232D178B-0310134003-32L82F3
5opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code278B-031014NOT MATCH
6opti7802014-11232D178B-0310134003-32L82F3
7opti7802014-11232D178B-0310134003-32L82F3Enter latest Prod_code34003-32L82F9NOT MATCH
8opti7802014-11232D178B-0310134003-32L82F3
Faez
Cell Formulas
RangeFormula
I3,I7,I5I3=IF(LOOKUP(2,1/($A$3:$A$22=$H$2),$C$3:$C$22)=H3,"","NOT ")&"MATCH"

If you have FILTER, that is probably the more convenient option.
 

FaezMH

New Member
Joined
Oct 14, 2019
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Peter's improved version reminded me of an alternative to find the last matching position for making the match comparison. This borrows from Peter's construction, but omits the FILTER function and instead searches for a "2" which will not exist, so the last matching position will be returned...
MrExcel20200908.xlsm
ABCDEFGHI
1RECORD LIST
2CPU_modelInvoiceProd_code1Prod_code2Prod_code3Enter CPU_modelopti781Alternative
3opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code12014-11232D3MATCH
4opti70102014-11232D178B-0310134003-32L82F3
5opti70102014-11232D178B-0310134003-32L82F3Enter latest Prod_code278B-031014NOT MATCH
6opti7802014-11232D178B-0310134003-32L82F3
7opti7802014-11232D178B-0310134003-32L82F3Enter latest Prod_code34003-32L82F9NOT MATCH
8opti7802014-11232D178B-0310134003-32L82F3
Faez
Cell Formulas
RangeFormula
I3,I7,I5I3=IF(LOOKUP(2,1/($A$3:$A$22=$H$2),$C$3:$C$22)=H3,"","NOT ")&"MATCH"

If you have FILTER, that is probably the more convenient option.
Hi KRice and Pete,
Thanks.
Amazing, both ways works perfectly. I have a question for New Code?(Y/N) at J3, J5, and J7. It looks like I missed out something. In the event that if my model is opti781 and have the scenario as below whereby the Prod_code3 is used in another model but new to opti781. In this case, how can I tie its status to by model as well. Is it gonna be similar to column I.

compareall.xlsx
ABCDEFGHIJ
1RECORD LIST
2CPU_modelInvoiceProd_code1Prod_code2Prod_code3Enter CPU_modelopti781RESULT (compare to last entry in the list)NEW CODE? (Y/N)
3opti701016212014-11232D178B-0310134003-32L82F3Enter latest Prod_code12014-11232D3MATCHN
4opti701016232014-11232D178B-0310134003-32L82F3
5opti701016192014-11232D178B-0310134003-32L82F3Enter latest Prod_code278B-031014NOT MATCHY
6opti78034552014-11232D178B-0310134003-32L82F3
7opti78034582014-11232D178B-0310134003-32L82F3Enter latest Prod_code34003-32L82F3NOT MATCHY
8opti78034532014-11232D178B-0310134003-32L82F3
9opti78034572014-11232D178B-0310134003-32L82F3
10opti99027002014-11232D178B-0310134003-32L82F3
11opti99027092014-11232D278B-0310134003-32L82F3
12opti99027072014-11232D278B-03101A4003-32L82F3
13opti701016252014-11232D278B-0310134003-32L82F3
14opti701016202014-11232D278B-0310134003-32L82F3
15opti78034582014-11232D278B-0310134003-32L82F3
16opti99027052014-11232D278B-0310134003-32L82F3
17opti701016252014-11232D278B-0310134003-32L82F3
18opti99027052014-11232D278B-0310144003-32L82F5
19opti78034582014-11232D278B-0310134003-32L82F6
20opti78135512014-11232D378B-0310134003-32L82F7
21opti78034532014-11232D378B-0310134003-32L82F6
22opti78135522014-11232D378B-0310134003-32L82F7
Sheet5
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,893
Office Version
  1. 365
Platform
  1. Windows
Is this what you mean?

FaezMH 1.xlsm
J
1
2NEW CODE? (Y/N)
3N
4
5Y
6
7Y
Sheet3
Cell Formulas
RangeFormula
J3J3=IF(COUNTIFS($C$3:$C$22,H3,$A$3:$A$22,H2),"N","Y")
J5J5=IF(COUNTIFS($D$3:$D$22,H5,$A$3:$A$22,H2),"N","Y")
J7J7=IF(COUNTIFS($E$3:$E$22,H7,$A$3:$A$22,H2),"N","Y")
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
I think Peter's got it. The formula I suggested earlier for "New Code" assumed that you wanted to know if a Prod_code was new in the data table, across all models. That assumption was based on the example shown in post #1, where the specified Prod_code2 has a result of "N" because it appears once in the data table for a model that is different than the specified model in H2. If instead you want to know whether the specified Prod_code is new for the specified CPU_model, then Peter's extension should work well.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,162
Members
410,775
Latest member
alal1030
Top