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

M

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!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ORDER[/TD]
[TD]MODEL[/TD]
[TD][/TD]
[TD]QUALIFYING MODEL 1[/TD]
[TD]QUALIFYING MODEL 2[/TD]
[TD]QUALIFYING MODEL 3[/TD]
[TD]QUALIFYING MODEL 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9001[/TD]
[TD]1A[/TD]
[TD][/TD]
[TD]1A[/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD]4A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9001[/TD]
[TD]2B[/TD]
[TD][/TD]
[TD]1B[/TD]
[TD]2B[/TD]
[TD]3B[/TD]
[TD]4B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9001[/TD]
[TD]3C[/TD]
[TD][/TD]
[TD]1C[/TD]
[TD][/TD]
[TD]3C[/TD]
[TD]4C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9001[/TD]
[TD]4D[/TD]
[TD][/TD]
[TD]1E[/TD]
[TD][/TD]
[TD]3D[/TD]
[TD]4D[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]9002[/TD]
[TD]1A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9003[/TD]
[TD]1E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4F[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9003[/TD]
[TD]2A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9003[/TD]
[TD]3A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]9003[/TD]
[TD]4Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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
Gerald Higgins said:
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 ?
Click to expand...

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")



[TABLE="width: 858"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ORDER[/TD]
[TD]MODEL[/TD]
[TD][/TD]
[TD][/TD]
[TD]QUALIFYING MODEL 1[/TD]
[TD]QUALIFYING MODEL 2[/TD]
[TD]QUALIFYING MODEL 3[/TD]
[TD]QUALIFYING MODEL 4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9001[/TD]
[TD]1A[/TD]
[TD]1[/TD]
[TD]YES[/TD]
[TD]1A[/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD]4A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9001[/TD]
[TD]2B[/TD]
[TD]1[/TD]
[TD]YES[/TD]
[TD]1B[/TD]
[TD]2B[/TD]
[TD]3B[/TD]
[TD]4B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9001[/TD]
[TD]3C[/TD]
[TD]1[/TD]
[TD]YES[/TD]
[TD]1C[/TD]
[TD][/TD]
[TD]3C[/TD]
[TD]4C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9001[/TD]
[TD]4D[/TD]
[TD]1[/TD]
[TD]YES[/TD]
[TD]1E[/TD]
[TD][/TD]
[TD]3D[/TD]
[TD]4D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9002[/TD]
[TD]1A[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4E[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]9003[/TD]
[TD]1E[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4F[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9003[/TD]
[TD]2A[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9003[/TD]
[TD]3A[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9003[/TD]
[TD]4Z[/TD]
[TD]0[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
ORDER​
[/td][td]
MODEL​
[/td][td]
Formula​
[/td][td]
QUALIFYING MODEL 1​
[/td][td]
QUALIFYING MODEL 2​
[/td][td]
QUALIFYING MODEL 3​
[/td][td]
QUALIFYING MODEL 4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
9001​
[/td][td]
1A​
[/td][td]
Yes​
[/td][td]
1A​
[/td][td]
2A​
[/td][td]
3A​
[/td][td]
4A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
9001​
[/td][td]
2B​
[/td][td]
Yes​
[/td][td]
1B​
[/td][td]
2B​
[/td][td]
3B​
[/td][td]
4B​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
9001​
[/td][td]
3C​
[/td][td]
Yes​
[/td][td]
1C​
[/td][td][/td][td]
3C​
[/td][td]
4C​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
9001​
[/td][td]
4D​
[/td][td]
Yes​
[/td][td]
1E​
[/td][td][/td][td]
3D​
[/td][td]
4D​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
9002​
[/td][td]
1A​
[/td][td]
No​
[/td][td][/td][td][/td][td][/td][td]
4E​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
9003​
[/td][td]
1E​
[/td][td]
No​
[/td][td][/td][td][/td][td][/td][td]
4F​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
9003​
[/td][td]
2A​
[/td][td]
No​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
9003​
[/td][td]
3A​
[/td][td]
No​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
9003​
[/td][td]
4Z​
[/td][td]
No​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
You must log in or register to reply here.

Similar threads

K
  • Question Question
Match cells with a twist.
Replies
1
Views
362
SidharthS
S
T
  • Question Question
Returning Values in one array based on data from two separate arrays
Replies
2
Views
320
Fluff
Fluff
K
  • Solved
Return Value With Multiple Criteria
Replies
9
Views
645
Fluff
Fluff
C
  • Solved
Need to Return Unique Players for Same Multiple Number Result (XLOOKUP?)
2
Replies
14
Views
1K
Fluff
Fluff
K
  • Question Question
Making Certain Columns Mandatory & Unique. Restrict, if user fails to maintain values in mandatory columns & unique values before Save and Close
Replies
3
Views
583
StuLux
StuLux

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

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