What function to find the most common values based off certain criteria?

M

miken54

New Member
Joined
Sep 18, 2017
Messages
20
Good Afternoon All,

This might seem easy to some, I just cant figure it out and I am not sure what keywords to enter if this was answered already.

I am the manager of an appliance retail store and I have a lot of new employees who are making simple errors. I was hoping there was a function I could enter into excel that would catch mistakes before the customer takes delivery. If we sell a washing machine, we generally speaking have two options of dryers we can sell that would match (gas or electric). So for example, if model # W1234 was sold, our dryer options would be DE123 or DG123.

Unfortunately, as of now I dont have a list of every corresponding dryer that matches each washer, so I was hoping that I could view all recent invoices that were sold, and find the most common dryers sold with each washer so that I could in essence create a list of the dryer options for each washer.

I have hundreds of lines of unique invoice numbers in column A and in Column B, it will show all of the model#'s that were sold on that invoice (please see below)

So the idea of the function I am looking for was to look up "A", find the washer model, and then find the dryer sold.

Then ultimately, in a different cell I want to show how many times each dryer was sold along with a washer so I can make a list of the most common dryers for each washer (something like this directly below in red)
D E F
WASHER DRYER OPTION 1 DRYER OPTION 2
and then create a different function to say something like "if in column A, washer model W1234 was sold AND dryer model DE123 OR DG123 was sold, then there is no error. However if washer W1234 was old and dryer DE500 was sold on that invoice, then create an error code.

If anyone has any suggestions on what function I could use, it would save me so many headaches haha. Thank you all, I really hope I explained this clearly.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]INVOICE #[/TD]
[TD]MODEL#[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A100[/TD]
[TD]W1234[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A100[/TD]
[TD]DE123[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B100[/TD]
[TD]W1234[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B100[/TD]
[TD]DG123[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C100[/TD]
[TD]W5000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]C100[/TD]
[TD]DE500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]D100[/TD]
[TD]W4000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]D100[/TD]
[TD]DEG400[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]E100[/TD]
[TD]W4000[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]E100[/TD]
[TD]DG400[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]F100[/TD]
[TD]W5000[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]F100[/TD]
[TD]DE500[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="width: 500"]
<tbody>[TR]
[TD]F100[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes

Book1
ABCDEFGHI
1INVOICE #MODEL#ABCDEF
2A100W1234W1234W1234W5000W4000W4000W5000
3A100DE123DE123DG123DE500DEG400DG400DE500
4A500W1234
5B100W1234
6B100DG123
7C100W5000
8C100DE500
9D100W4000
10D100DEG400
11E100W4000
12E100DG400
13F100W5000
14F100DE500
Sheet1


In D2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$2:$B$14,SMALL(IF(FREQUENCY(IF(LEFT($A$2:$A$14)=D$1,MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1),ROWS(D$2:D2))),"")
 
Upvote 0
Hey,first off thanks for taking the time our if your day to look help me. Would you be so kind to help me with part 2? I thought I could figure it out but am having issues.

So basically I am looking to enter something into another cell which would tell me whether or not one of the possible dryers were sold or not. At this point I have my list that looks like this...

A B C
WASHER MODEL POSSIBLE DRYER 1 POSSIBLE DRYER 2
W1234 DE123 DG123
W5000 DE500 DG500

So if W1234 is the washer on invoice invoice A100 and the corresponding dryer is DE123 or DG123, then everything is okay.
But if W1234 is the wahsher on invoice B1000, and the dryer showing up on that invoice is DEG400, an error message would pop up

Aladin Akyurek said:
ABCDEFGHI
INVOICE #MODEL#ABCDEF
A100W1234W1234W1234W5000W4000W4000W5000
A100DE123DE123DG123DE500DEG400DG400DE500
A500W1234
B100W1234
B100DG123
C100W5000
C100DE500
D100W4000
D100DEG400
E100W4000
E100DG400
F100W5000
F100DE500

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

In D2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$2:$B$14,SMALL(IF(FREQUENCY(IF(LEFT($A$2:$A$14)=D$1,MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1),ROWS(D$2:D2))),"")
Click to expand...
 
Upvote 0
Part 2...


Book1
ABCDEFG
1INVOICE #MODEL#WASHER MODELPOSSIBLE DRYER 1POSSIBLE DRYER 2
2A100W1234W1234DE123DG123
3A100DE123POSSIBLEW5000DE500DG500
4A500W1234
5B100W1234
6B100DG123POSSIBLE
7C100W5000
8C100DE500POSSIBLE
9D100W4000
10D100DEG400NOT POSSIBLE
11E100W4000
12E100DG400NOT POSSIBLE
13F100W5000
14F100DE500POSSIBLE
Sheet1 (2)


In C2 control+shift+enter, not just enter, and copy down:

=IF(LEFT($B2)="W","",IF(ISNUMBER(MATCH($B2,INDEX($E$2:$F$3,MATCH(LOOKUP(9.99999999999999E+307,SEARCH("W",IF($A$2:$A$14=$A2,$B$2:$B$14)),$B$2:$B$14),$D$2:$D$3,0),0),0)),"POSSIBLE","NOT POSSIBLE"))
 
Upvote 0
I feel like a total jerk for asking more because I already learned something incredible thanks to you today, but when you wrote "W", is that because the washers I listed all started with a "W"? If so I apologize for not being clear, this was just an example, my real report is much longer and had dozens of washer/dryer machines with different models. Some start with E, W, G, etc.

A B C D E F
[TABLE="width: 495"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]INVOICE[/TD]
[TD][/TD]
[TD]MODEL[/TD]
[TD][/TD]
[TD]WASHER[/TD]
[TD][/TD]
[TD]DRYER OPTION 1 [/TD]
[TD][/TD]
[TD]DRYER OPTION 2[/TD]
[/TR]
[TR]
[TD="align: right"]86024571[/TD]
[TD][/TD]
[TD]EFLS527UIW[/TD]
[TD][/TD]
[TD]EFLS527UIW[/TD]
[TD][/TD]
[TD]EFMG527UIW[/TD]
[TD][/TD]
[TD]EFME527UIW[/TD]
[/TR]
[TR]
[TD="align: right"]86024571[/TD]
[TD][/TD]
[TD]EFMG527UIW[/TD]
[TD][/TD]
[TD]WM3670HVA[/TD]
[TD][/TD]
[TD]DLGX3571V[/TD]
[TD][/TD]
[TD]DLEX3571V[/TD]
[/TR]
[TR]
[TD="align: right"]86059371[/TD]
[TD][/TD]
[TD]WM3670HVA[/TD]
[TD][/TD]
[TD]WAT28400UC[/TD]
[TD][/TD]
[TD]WTG86400UC[/TD]
[TD][/TD]
[TD]WTE86400UC[/TD]
[/TR]
[TR]
[TD="align: right"]86059371[/TD]
[TD][/TD]
[TD]DLEX3571V[/TD]
[TD][/TD]
[TD]WCVH4800KWW[/TD]
[TD][/TD]
[TD]DCVH480EKWW[/TD]
[TD][/TD]
[TD]EFMG627UIW[/TD]
[/TR]
[TR]
[TD="align: right"]86093981[/TD]
[TD][/TD]
[TD]WCVH4800KWW[/TD]
[TD][/TD]
[TD]EFLS627UTT[/TD]
[TD][/TD]
[TD]EFME627UTT[/TD]
[TD][/TD]
[TD]EFMG627UTT[/TD]
[/TR]
[TR]
[TD="align: right"]86093981[/TD]
[TD][/TD]
[TD]DCVH480EKWW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]86127801[/TD]
[TD][/TD]
[TD]EFMG617SIW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]86127821[/TD]
[TD][/TD]
[TD]EFMG617SIW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]86075832[/TD]
[TD][/TD]
[TD]EFLS617SIW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]86075832[/TD]
[TD][/TD]
[TD]EFME617SIW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]86075832[/TD]
[TD][/TD]
[TD]EPWD157SIW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]86075832[/TD]
[TD][/TD]
[TD]EPWD157SIW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You should have also posted the desired results...


Book1
ABCDEF
1INVOICEMODELWASHERDRYER OPTION 1DRYER OPTION 2
286024571EFLS527UIWEFLS527UIWEFMG527UIWEFME527UIW
386024571EFMG527UIWPOSSIBLEWM3670HVADLGX3571VDLEX3571V
486059371WM3670HVAWAT28400UCWTG86400UCWTE86400UC
586059371DLEX3571VPOSSIBLEWCVH4800KWWDCVH480EKWWEFMG627UIW
686093981WCVH4800KWWEFLS627UTTEFME627UTTEFMG627UTT
786093981DCVH480EKWWPOSSIBLE
886127801EFMG617SIWNOT POSSIBLE
986127821EFMG617SIWNOT POSSIBLE
1086075832EFLS617SIWNOT POSSIBLE
1186075832EFME617SIWNOT POSSIBLE
1286075832EPWD157SIWNOT POSSIBLE
1386075832EPWD157SIWNOT POSSIBLE
Sheet1 (3)


In C2 control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH($B2,$D$2:$D$6,0)),"",IF(ISNUMBER(MATCH($B2,INDEX($E$2:$F$6,MATCH(VLOOKUP(A2,$A$1:$B1,2,0),$D$2:$D$6,0),0),0)),"POSSIBLE","NOT POSSIBLE"))
 
Upvote 0
I'm sorry. But thank you so much!!!

Aladin Akyurek said:
You should have also posted the desired results...

ABCDEF
INVOICEMODELWASHERDRYER OPTION 1DRYER OPTION 2
EFLS527UIWEFLS527UIWEFMG527UIWEFME527UIW
EFMG527UIWPOSSIBLEWM3670HVADLGX3571VDLEX3571V
WM3670HVAWAT28400UCWTG86400UCWTE86400UC
DLEX3571VPOSSIBLEWCVH4800KWWDCVH480EKWWEFMG627UIW
WCVH4800KWWEFLS627UTTEFME627UTTEFMG627UTT
DCVH480EKWWPOSSIBLE
EFMG617SIWNOT POSSIBLE
EFMG617SIWNOT POSSIBLE
EFLS617SIWNOT POSSIBLE
EFME617SIWNOT POSSIBLE
EPWD157SIWNOT POSSIBLE
EPWD157SIWNOT POSSIBLE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]86024571[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]86024571[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]86059371[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]86059371[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]86093981[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]86093981[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]86127801[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]86127821[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]86075832[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]86075832[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]86075832[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]86075832[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1 (3)​

In C2 control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH($B2,$D$2:$D$6,0)),"",IF(ISNUMBER(MATCH($B2,INDEX($E$2:$F$6,MATCH(VLOOKUP(A2,$A$1:$B1,2,0),$D$2:$D$6,0),0),0)),"POSSIBLE","NOT POSSIBLE"))
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Most Common Values Matching certain criteria
Replies
1
Views
356
DRSteele
DRSteele
B
  • Question Question
FInd most common variation of product combination of with order type and complete order details
Replies
5
Views
3K
bbell2219
B

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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