Compare and match multiple cells

[FaezMH]

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
	A	B	C	D	E	F	G	H	I	J
1	RECORD LIST
2	CPU_model	Invoice	Prod_code1	Prod_code2	Prod_code3		Enter CPU_model	opti780	RESULT (compare to last entry in the list)	NEW CODE? (Y/N)
3	opti7010	1621	2014-11232D1	78B-031013	4003-32L82F3		Enter latest Prod_code1	2014-11232D3	MATCH	N
4	opti7010	1623	2014-11232D1	78B-031013	4003-32L82F3
5	opti7010	1619	2014-11232D1	78B-031013	4003-32L82F3		Enter latest Prod_code2	78B-031014	NOT MATCH	N
6	opti780	3455	2014-11232D1	78B-031013	4003-32L82F3
7	opti780	3458	2014-11232D1	78B-031013	4003-32L82F3		Enter latest Prod_code3	4003-32L82F9	NOT MATCH	Y
8	opti780	3453	2014-11232D1	78B-031013	4003-32L82F3
9	opti780	3457	2014-11232D1	78B-031013	4003-32L82F3
10	opti990	2700	2014-11232D1	78B-031013	4003-32L82F3
11	opti990	2709	2014-11232D2	78B-031013	4003-32L82F3
12	opti990	2707	2014-11232D2	78B-03101A	4003-32L82F3
13	opti7010	1625	2014-11232D2	78B-031013	4003-32L82F3
14	opti7010	1620	2014-11232D2	78B-031013	4003-32L82F3
15	opti780	3458	2014-11232D2	78B-031013	4003-32L82F3
16	opti990	2705	2014-11232D2	78B-031013	4003-32L82F3
17	opti7010	1625	2014-11232D2	78B-031013	4003-32L82F3
18	opti990	2705	2014-11232D2	78B-031014	4003-32L82F5
19	opti780	3458	2014-11232D2	78B-031013	4003-32L82F6
20	opti781	3551	2014-11232D3	78B-031013	4003-32L82F7
21	opti780	3453	2014-11232D3	78B-031013	4003-32L82F6
22	opti781	3552	2014-11232D3	78B-031013	4003-32L82F7
23
24
25
Sheet5

 

[FaezMH]

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!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.