If a unique value (order number) contains certain values (models) AND IF those models are found in other cells, then return a "yes"?

miken54

New Member
Joined
Sep 18, 2017
Messages
20
Hi All,

If this was previously answered, please direct me to that link so I dont have to have you repeat yourselves haha. I honestly dont know what keywords to be searching for. Otherwise, if you can help it would be really appreciated.

My company write a bunch of orders (A) which have one or more models (B) sold on each order. Some vendors offer rebates if we sell certain models on the same order.

In this case, if my order has at least one of the qualifying models in EACH of columns D, E, F, and G, my company would qualify for a rebate.

So for example, order number 9001 (A2-A6) would qualify for a rebate because in B2-B6, those models are found in each of D, E, F, and G.
Order 9002 would not qualify because only one model (B7) is found in D-G
Order 9003 also would not qualify because only 3/4 of the models are found in D-G. B11 is not found in "G"

Is there a IF statement or something like that which I can enter into C2 and copy down that will tell me if each order qualify's?
I dont know if this makes a difference, but the models are not two digit numbers. They contain letters and numbers and can be up to digits.

Thanks all! And Happy Holidays!
ABCDEFGHI
1ORDERMODELQUALIFYING MODEL 1QUALIFYING MODEL 2QUALIFYING MODEL 3QUALIFYING MODEL 4
290011A1A2A3A4A
390012B1B2B3B4B
490013C1C3C4C
690014D1E3D4D
790021A4E
890031E4F
990032A
1090033A
1190034Z
12
13
14

<tbody>
</tbody>
 

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.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Is it POSSIBLE that an order could contain, for example, 2 units of model 1A, and if the answer is YES, does that mean you only need another two qualifying units of other models ?
 

miken54

New Member
Joined
Sep 18, 2017
Messages
20
Is it POSSIBLE that an order could contain, for example, 2 units of model 1A, and if the answer is YES, does that mean you only need another two qualifying units of other models ?

Thanks for the quick response!
Yes its very possible that there can be two of the same models but no, you would still need 3 others. Basically in order to get the rebate the models must be found in all 4 columns in D-G
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
OK, and so IF an order contained 1 each of 1A, 1B, 1C, and 1E, and nothing else, then that would NOT qualify for a rebate.
Correct ?
 

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873

ADVERTISEMENT

Hi,

If it's OK with you to add helper column , try this:-

Ctrl+Shift+Enter NOT just Enter

C2 =SUM(--(A2&B2=A2&$E$2:$H$10))

Just Enter

D2 =IF(COUNTIF($A$2:$A$10,A2)=4,IF(SUMPRODUCT(--($A$2:$A$10=A2),$C$2:$C$10)=4,"YES","NO"),"NO")



ABCDEFGH
1ORDERMODELQUALIFYING MODEL 1QUALIFYING MODEL 2QUALIFYING MODEL 3QUALIFYING MODEL 4
290011A1YES1A2A3A4A
390012B1YES1B2B3B4B
490013C1YES1C3C4C
590014D1YES1E3D4D
690021A1NO4E
790031E1NO4F
890032A1NO
990033A1NO
1090034Z0NO

<tbody>
</tbody>
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Maybe...


A
B
C
D
E
F
G
1
ORDER​
MODEL​
Formula​
QUALIFYING MODEL 1​
QUALIFYING MODEL 2​
QUALIFYING MODEL 3​
QUALIFYING MODEL 4​
2
9001​
1A​
Yes​
1A​
2A​
3A​
4A​
3
9001​
2B​
Yes​
1B​
2B​
3B​
4B​
4
9001​
3C​
Yes​
1C​
3C​
4C​
5
9001​
4D​
Yes​
1E​
3D​
4D​
6
9002​
1A​
No​
4E​
7
9003​
1E​
No​
4F​
8
9003​
2A​
No​
9
9003​
3A​
No​
10
9003​
4Z​
No​

Array formula in C2 copied down
=IF(SUM(IF(MMULT(TRANSPOSE(ROW(D$2:G$10)^0),--ISNUMBER(MATCH(D$2:G$10,IF(A$2:A$10=A2,B$2:B$10),0)))>0,1))=4,"Yes","No")
Ctrl+Shift+Enter

M.
 

miken54

New Member
Joined
Sep 18, 2017
Messages
20
Thank you so much Marcelo! This worked great. I really appreciate your help, you just saved me hours if not days of work.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

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
Top