grab most often occurring item & information

[halloyd]

I'm back!

I need a formula to scan a column and return the three most common values in that column along with information associated with them.

Here is the sample from which I will be pulling information:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Qty[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Reason[/TD]
[TD]Claim #[/TD]
[/TR]
[TR]
[TD]HEN020[/TD]
[TD]1[/TD]
[TD]Selenite Flower Small[/TD]
[TD]$89[/TD]
[TD]WIS[/TD]
[TD]1542[/TD]
[/TR]
[TR]
[TD]HEN020[/TD]
[TD]1[/TD]
[TD]Selenite Flower Small[/TD]
[TD]$89[/TD]
[TD]DMG[/TD]
[TD]1642[/TD]
[/TR]
[TR]
[TD]HEN020[/TD]
[TD]1[/TD]
[TD]Selenite Flower Small[/TD]
[TD]$89[/TD]
[TD]DMG[/TD]
[TD]8572[/TD]
[/TR]
[TR]
[TD]HEN020[/TD]
[TD]2[/TD]
[TD]Selenite Flower Small[/TD]
[TD]$89[/TD]
[TD]NBO[/TD]
[TD]3154[/TD]
[/TR]
[TR]
[TD]indJL019[/TD]
[TD]6[/TD]
[TD]Whip Stitch Stool, Brass[/TD]
[TD]$145[/TD]
[TD]DNR[/TD]
[TD]2463[/TD]
[/TR]
[TR]
[TD]indJL019[/TD]
[TD]5[/TD]
[TD]Whip Stitch Stool, Brass[/TD]
[TD]$145[/TD]
[TD]DNR[/TD]
[TD]7541[/TD]
[/TR]
[TR]
[TD]indJL019[/TD]
[TD]1[/TD]
[TD]Whip Stitch Stool, Brass[/TD]
[TD]$145[/TD]
[TD]DMG[/TD]
[TD]5987[/TD]
[/TR]
[TR]
[TD]MM170596[/TD]
[TD]3[/TD]
[TD]Barnyard Stool[/TD]
[TD]$15[/TD]
[TD]NBO[/TD]
[TD]3154[/TD]
[/TR]
[TR]
[TD]MM170596[/TD]
[TD]7[/TD]
[TD]Barnyard Stool[/TD]
[TD]$15[/TD]
[TD]WIS[/TD]
[TD]6524[/TD]
[/TR]
[TR]
[TD]indH129[/TD]
[TD]3[/TD]
[TD]Table Leg Candle Stand[/TD]
[TD]$12[/TD]
[TD]DNR[/TD]
[TD]9764[/TD]
[/TR]
</tbody>[/TABLE]

I need it to:

• tell me the total amount an item is on the list (Amt column)
• recognize which occurs most (HEN020), second most (indJL019), and third most (MM170596)
• return the total quantity of the items (HEN020 x5, indJL019 x12, MM170596 x10)
• each claim # associated (HEN020 would read "1542, 1642, 8572, 3154")
• return the reason for the claim of each LINE item (so for HEN020, it would read "WIS, DMG, DMG, NBO")

I can automate price without help.

Here is what the end result of the report would look like:

[TABLE="class: outer_border, width: 750"]
<tbody>[TR]
[TD]Amt[/TD]
[TD]Item[/TD]
[TD]Description[/TD]
[TD]Claim #'s[/TD]
[TD]Total Qt[/TD]
[TD]Reason[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]HEN020[/TD]
[TD]Selenite Flower Small[/TD]
[TD]1542, 1642, 8572, 3154[/TD]
[TD]5[/TD]
[TD]WIS, DMG, DMG, NBO[/TD]
[TD]$89[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]indJL019[/TD]
[TD]Whip Stitch Stool, Brass[/TD]
[TD]2463, 7541, 5987[/TD]
[TD]12[/TD]
[TD]DNR, DNR, DMG[/TD]
[TD]$145[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]MM170596[/TD]
[TD]Barnyard Stool
[/TD]
[TD]3154, 6524[/TD]
[TD]10[/TD]
[TD]NBO, WIS[/TD]
[TD]$15[/TD]
[/TR]
</tbody>[/TABLE]

Thank you everyone for the help I've already received on this forum, it's helping me to learn and be more efficient at my job!

Thank you,
Heather

 

[Eric W]

Welcome back, Heather!

How about:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Item	Qty	Description	Price	Reason	Claim #	Amt	Item	Description	Claim #'s	Total Qt	Reason	Price
HEN020	Selenite Flower Small	WIS	HEN020	Selenite Flower Small	1542, 1642, 8572, 3154	WIS, DMG, DMG, NBO
HEN020	Selenite Flower Small	DMG	indJL019	Whip Stitch Stool, Brass	2463, 7541, 5987	DNR, DNR, DMG
HEN020	Selenite Flower Small	DMG	MM170596	Barnyard Stool	3154, 6524	NBO, WIS
HEN020	Selenite Flower Small	NBO
indJL019	Whip Stitch Stool, Brass	DNR
5	$145
indJL019	Whip Stitch Stool, Brass	DMG
MM170596	Barnyard Stool	NBO
MM170596	Barnyard Stool	WIS
indH129	Table Leg Candle Stand	DNR

<tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[TD="align: right"]3154[/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: right"][/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: right"]2463[/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: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]Whip Stitch Stool, Brass[/TD]

[TD="align: right"]DNR[/TD]
[TD="align: right"]7541[/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: right"][/TD]

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

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

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

[TD="align: right"]5987[/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: right"][/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: right"]3154[/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: right"][/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: right"]6524[/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: right"][/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: right"]9764[/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: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=COUNTIF($A$2:$A$11,I2)[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]=INDEX($C$2:$C$11,MATCH(I2,$A$2:$A$11))[/TD]
[/TR]
[TR]
[TH]L2[/TH]
[TD="align: left"]=SUMIF($A$2:$A$11,I2,$B$2:$B$11)[/TD]
[/TR]
[TR]
[TH]N2[/TH]
[TD="align: left"]=INDEX($D$2:$D$11,MATCH(I2,$A$2:$A$11))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$11,MODE(IF(COUNTIF($I$1:$I1,$A$2:$A$11)=0,MATCH($A$2:$A$11,$A$2:$A$11,0))))}[/TD]
[/TR]
[TR]
[TH]K2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$F$2:$F$11,""))}[/TD]
[/TR]
[TR]
[TH]M2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$E$2:$E$11,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Columns K and M are the problematic ones. TEXTJOIN is currently only available in Excel 365. If you don't have it, you'll probably need a macro to get this report.

 

[halloyd]

Columns K and M are the problematic ones. TEXTJOIN is currently only available in Excel 365. If you don't have it, you'll probably need a macro to get this report.

Thanks a million for the help I checked and I do have Textjoin, so I'll try to figure out how that one works. Thank you!

 

[halloyd]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=COUNTIF($A$2:$A$11,I2)[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]=INDEX($C$2:$C$11,MATCH(I2,$A$2:$A$11))[/TD]
[/TR]
[TR]
[TH]L2[/TH]
[TD="align: left"]=SUMIF($A$2:$A$11,I2,$B$2:$B$11)[/TD]
[/TR]
[TR]
[TH]N2[/TH]
[TD="align: left"]=INDEX($D$2:$D$11,MATCH(I2,$A$2:$A$11))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$11,MODE(IF(COUNTIF($I$1:$I1,$A$2:$A$11)=0,MATCH($A$2:$A$11,$A$2:$A$11,0))))}[/TD]
[/TR]
[TR]
[TH]K2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$F$2:$F$11,""))}[/TD]
[/TR]
[TR]
[TH]M2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$E$2:$E$11,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

So I'm admittedly mildly confused about these. Will these only work if the information is joined?

The result I posted would be in another worksheet from the information that it's pulling from. For example, the sheet that contains the information I need is Claim Status Sheet.xlsx. The report will be on Claim Report.xlsx.

Here's a screenie of the sheet I'm pulling information from. I hid unnecessary columns.

Now, the report that is pulling information from this sheet is in another workbook entirely. I cannot combine the two.

 

[Eric W]

Those formulas should work fine if you point to another workbook, even if the other workbook is closed. You do have to adapt the ranges to point to that workbook. For example, the I2 formula above would become:

=INDEX('C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11,MODE(IF(COUNTIF($I$1:$I1,'C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11)=0,MATCH('C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11,'C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11,0))))

with CSE. You'll need to change the path to your actual path.

 

[halloyd]

Those formulas should work fine if you point to another workbook, even if the other workbook is closed. You do have to adapt the ranges to point to that workbook. For example, the I2 formula above would become:

=INDEX('C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11,MODE(IF(COUNTIF($I$1:$I1,'C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11)=0,MATCH('C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11,'C:\Folder\[Claim Status Sheet.xlsx]Sheet1'!$A$2:$A$11,0))))

with CSE. You'll need to change the path to your actual path.

Guess who's back... back again... I'm back... plz help me...

So I got all the formulas plugged in with the locations correct to my sheets, but it's returning 0.
Here's the sheet where the formula is:

And the formula:

=INDEX('[2018 Claim Status Sheet.xlsx]Jan'!$I$5:$I$62,MODE(IF(COUNTIF($K$8:$K$8,'[2018 Claim Status Sheet.xlsx]Jan'!$I$3:$I$300)=0,MATCH('[2018 Claim Status Sheet.xlsx]Jan'!$I$3:$I$300,'[2018 Claim Status Sheet.xlsx]Jan'!$I$3:$I$300,0))))

I think it has something to do with referencing itself? The K8:8 is equivalent to your I1:I1. I have no idea

Here's the sheet it's pulling from:

Thank you again.