Need match of two different values

R

richmcgill

Board Regular
Joined
Feb 4, 2019
Messages
71
Office Version
  1. 2016
Platform
  1. Windows
I have a list of customers each with a unique ID number
Each customer can show up multiple times on a very large report. The customer ID stays consistent throughout and does not change.
Each Customer can have a status of Ordered or Ordering or None
Using the customer number I need to know which customer number has both "Ordered" & "Ordering" in its status field. If both are not present I do not need a result. I do not need to know the customers with a None status.

The Result should be a Yes or No. Yes both are present or No both are not present.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Customer 10962 has both Ordered & Ordering so the output would be a Yes.

Customer NumberStatusNeeded Results If Match
16236154OrderedNo
16236154OrderedNo
16236154No
16236154No
16236154No
16236154No
16236154No
32374OrderingNo
32374OrderingNo
32374OrderingNo
32374OrderingNo
37918OrderingNo
37918OrderingNo
10962OrderingYes
10962OrderingYes
10962OrderingYes
10962OrderingYes
10962OrderingYes
10962OrderingYes
10962OrderedYes
10962OrderedYes
90601358OrderingNo
90601358OrderingNo
90601358No
90601358OrderingNo
90601358No

<tbody>
</tbody>
 
Upvote 0
Hi,

Maybe you could use this..


Book1
ABCD
1Customer IDStatusMatchesCriteria
216236154OrderedNoOrdered
316236154OrderedNoOrdering
416236154No
516236154No
632374OrderingNo
732374OrderingNo
837918OrderingNo
937918OrderingNo
1010962OrderingYes
1110962OrderingYes
1210962OrderingYes
1310962OrderedYes
1410962OrderedYes
1590601358OrderingNo
1690601358No
Sheet1
Cell Formulas
RangeFormula
C2=IF(AND(COUNTIFS($A$2:$A$16,A2,$B$2:$B$16,$D$2)>0,COUNTIFS($A$2:$A$16,A2,$B$2:$B$16,$D$3)>0),"Yes","No")
 
Upvote 0
Solution
I have the doubt, if the customer is blank, should count or not?
As in the following example customer 16236154

Try:

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:122px;" /><col style="width:62px;" /><col style="width:62px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Customer Number</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Status</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Result</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">16236154</td><td >Ordered</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">16236154</td><td >Ordered</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">16236154</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">16236154</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">16236154</td><td >Ordering</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">16236154</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">16236154</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">32374</td><td >Ordering</td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">32374</td><td >Ordering</td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">32374</td><td >Ordering</td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">32374</td><td >Ordering</td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">37918</td><td >Ordering</td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">37918</td><td >Ordering</td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">10962</td><td >Ordering</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">10962</td><td >Ordering</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">10962</td><td >Ordering</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">10962</td><td >Ordering</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">10962</td><td >Ordering</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">10962</td><td >Ordering</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">10962</td><td >Ordered</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">10962</td><td >Ordered</td><td >Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">90601358</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">90601358</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">90601358</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">90601358</td><td > </td><td >No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">90601358</td><td > </td><td >No</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(AND(B2<>"",COUNTIFS($A$2:$A$27,A2,$B$2:$B$27,"<>"&"",$B$2:$B$27,"<>"&B2)>0),"Yes","No")</td></tr></table></td></tr></table> <br /><br />
 
Upvote 0
Both work solutions very well. THANK YOU! THANK YOU! THANK YOU! I really do appreciate you help!
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
I need a bit more help. I need to take the results a bit farther.

In another column (D) where column C shows a No which of the statuses (ordered or ordering) in column B is it? The Yes's would move over to Column D as a Yes.
 
Upvote 0
Do you mean something like this?

ABCDE
1Customer NumberStatusResultStatus Found
216236154OrderedYesYes
316236154OrderedYesYes
416236154YesYes
516236154YesYes
616236154OrderingYesYes
716236154YesYes
816236154YesYes
932374OrderingNoOrdering
1032374OrderingNoOrdering
1132374OrderingNoOrdering
1232374OrderingNoOrdering
1337918OrderedNoOrdered
1437918OrderedNoOrdered
1510962OrderingYesYes
1610962OrderingYesYes
1710962OrderingYesYes
1810962OrderingYesYes
1910962OrderingYesYes
2010962OrderingYesYes
2110962OrderedYesYes
2210962OrderedYesYes
2390601358No
2490601358No
2590601358No
2690601358No
2790601358No
28No
29No

<tbody>
</tbody>
Sheet3​

Worksheet Formulas
CellFormula
C2=IF(PRODUCT(COUNTIFS($A:$A,A2,$B:$B,{"Ordered","Ordering"})),"Yes","No")
D2=CHOOSE(SUM(SIGN(COUNTIFS(A:A,A2,B:B,{"Ordered","Ordering"}))*{1,2})+1,"","Ordered","Ordering","Yes")

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Duration (Time Difference) between 2 date fields in different rows
Replies
3
Views
430
Leo Skywalker
L
P
  • Question
Multiple Results with delimiter
Replies
5
Views
121
Peter_SSs
Peter_SSs
I
  • Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
466
ItsNursinTime
I
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
555
ahaye
A
T
  • Question
vba for index match and return between workbooks
Replies
1
Views
332
GlennUK
G

Forum statistics

Threads
1,214,912
Messages
6,122,204
Members
449,072
Latest member
DW Draft

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