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

[miken54]

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.

	A	B
1	INVOICE #	MODEL#
2	A100	W1234
3	A100	DE123
4	B100	W1234
5	B100	DG123
6	C100	W5000
7	C100	DE500
8	D100	W4000
9	D100	DEG400
10	E100	W4000
11	E100	DG400
12	F100	W5000
13	F100	DE500
14

<tbody>
</tbody>

F100

<tbody>
</tbody>

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
1	INVOICE #	MODEL#		A	B	C	D	E	F
2	A100	W1234		W1234	W1234	W5000	W4000	W4000	W5000
3	A100	DE123		DE123	DG123	DE500	DEG400	DG400	DE500
4	A500	W1234
5	B100	W1234
6	B100	DG123
7	C100	W5000
8	C100	DE500
9	D100	W4000
10	D100	DEG400
11	E100	W4000
12	E100	DG400
13	F100	W5000
14	F100	DE500
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))),"")

 

[miken54]

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

	A	B	C	D	E	F	G	H	I
1	INVOICE #	MODEL#		A	B	C	D	E	F
2	A100	W1234		W1234	W1234	W5000	W4000	W4000	W5000
3	A100	DE123		DE123	DG123	DE500	DEG400	DG400	DE500
4	A500	W1234
5	B100	W1234
6	B100	DG123
7	C100	W5000
8	C100	DE500
9	D100	W4000
10	D100	DEG400
11	E100	W4000
12	E100	DG400
13	F100	W5000
14	F100	DE500

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))),"")

 

[Aladin Akyurek]

Part 2...

Book1
	A	B	C	D	E	F	G
1	INVOICE #	MODEL#		WASHER MODEL	POSSIBLE DRYER 1	POSSIBLE DRYER 2
2	A100	W1234		W1234	DE123	DG123
3	A100	DE123	POSSIBLE	W5000	DE500	DG500
4	A500	W1234
5	B100	W1234
6	B100	DG123	POSSIBLE
7	C100	W5000
8	C100	DE500	POSSIBLE
9	D100	W4000
10	D100	DEG400	NOT POSSIBLE
11	E100	W4000
12	E100	DG400	NOT POSSIBLE
13	F100	W5000
14	F100	DE500	POSSIBLE
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"))

 

[miken54]

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

INVOICE		MODEL		WASHER		DRYER OPTION 1		DRYER OPTION 2
86024571		EFLS527UIW		EFLS527UIW		EFMG527UIW		EFME527UIW
86024571		EFMG527UIW		WM3670HVA		DLGX3571V		DLEX3571V
86059371		WM3670HVA		WAT28400UC		WTG86400UC		WTE86400UC
86059371		DLEX3571V		WCVH4800KWW		DCVH480EKWW		EFMG627UIW
86093981		WCVH4800KWW		EFLS627UTT		EFME627UTT		EFMG627UTT
86093981		DCVH480EKWW
86127801		EFMG617SIW
86127821		EFMG617SIW
86075832		EFLS617SIW
86075832		EFME617SIW
86075832		EPWD157SIW
86075832		EPWD157SIW

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

You should have also posted the desired results...

Book1
	A	B	C	D	E	F
1	INVOICE	MODEL		WASHER	DRYER OPTION 1	DRYER OPTION 2
2	86024571	EFLS527UIW		EFLS527UIW	EFMG527UIW	EFME527UIW
3	86024571	EFMG527UIW	POSSIBLE	WM3670HVA	DLGX3571V	DLEX3571V
4	86059371	WM3670HVA		WAT28400UC	WTG86400UC	WTE86400UC
5	86059371	DLEX3571V	POSSIBLE	WCVH4800KWW	DCVH480EKWW	EFMG627UIW
6	86093981	WCVH4800KWW		EFLS627UTT	EFME627UTT	EFMG627UTT
7	86093981	DCVH480EKWW	POSSIBLE
8	86127801	EFMG617SIW	NOT POSSIBLE
9	86127821	EFMG617SIW	NOT POSSIBLE
10	86075832	EFLS617SIW	NOT POSSIBLE
11	86075832	EFME617SIW	NOT POSSIBLE
12	86075832	EPWD157SIW	NOT POSSIBLE
13	86075832	EPWD157SIW	NOT 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"))

 

[miken54]

I'm sorry. But thank you so much!!!

You should have also posted the desired results...

	A	B	C	D	E	F
1	INVOICE	MODEL		WASHER	DRYER OPTION 1	DRYER OPTION 2
2	86024571	EFLS527UIW		EFLS527UIW	EFMG527UIW	EFME527UIW
3	86024571	EFMG527UIW	POSSIBLE	WM3670HVA	DLGX3571V	DLEX3571V
4	86059371	WM3670HVA		WAT28400UC	WTG86400UC	WTE86400UC
5	86059371	DLEX3571V	POSSIBLE	WCVH4800KWW	DCVH480EKWW	EFMG627UIW
6	86093981	WCVH4800KWW		EFLS627UTT	EFME627UTT	EFMG627UTT
7	86093981	DCVH480EKWW	POSSIBLE
8	86127801	EFMG617SIW	NOT POSSIBLE
9	86127821	EFMG617SIW	NOT POSSIBLE
10	86075832	EFLS617SIW	NOT POSSIBLE
11	86075832	EFME617SIW	NOT POSSIBLE
12	86075832	EPWD157SIW	NOT POSSIBLE
13	86075832	EPWD157SIW	NOT POSSIBLE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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"))

 

[Aladin Akyurek]

I'm sorry. But thank you so much!!!

You are welcome.