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>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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 ?
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Thank you so much Marcelo! This worked great. I really appreciate your help, you just saved me hours if not days of work.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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