Compare and match multiple cells

F

FaezMH

New Member
Joined
Oct 14, 2019
Messages
39
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
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:
Upvote 0
KRice said:
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)?
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
KRice said:
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.
Click to expand...

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.
 
Upvote 0
@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"
 
Upvote 0
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.
 
Upvote 0
KRice said:
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.
Click to expand...
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
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JeffGrant
  • Solved
Looking for suggestions with efficient comparing of two ranges
Replies
5
Views
432
Tupe77
T
J
  • Solved
Calculate sums based on values
Replies
3
Views
306
NateSC
NateSC
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
438
ChrisMac1
ChrisMac1
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
666
Davenil
D
K
  • Solved
Can't get vlookup or index/match to work in compare
Replies
6
Views
414
KDS14589
K

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top