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
 

FaezMH

New Member
Joined
Oct 14, 2019
Messages
16
Office Version
  1. 365
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.
Thank you, gentlemen! KRice and Pete, you guys are awesome!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,919
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,257
Members
410,905
Latest member
Extjel
Top