grab most often occurring item & information

halloyd

New Member
Joined
Sep 14, 2018
Messages
17
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.

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


ItemQtyDescriptionPriceReasonClaim #
HEN0201Selenite Flower Small$89WIS1542
HEN0201Selenite Flower Small$89DMG1642
HEN0201Selenite Flower Small$89DMG8572
HEN0202Selenite Flower Small$89NBO3154
indJL0196Whip Stitch Stool, Brass$145DNR2463
indJL0195Whip Stitch Stool, Brass$145DNR7541
indJL0191Whip Stitch Stool, Brass$145DMG5987
MM1705963Barnyard Stool$15NBO3154
MM1705967Barnyard Stool$15WIS6524
indH1293Table Leg Candle Stand$12DNR9764

<tbody>
</tbody>

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:

AmtItemDescriptionClaim #'sTotal QtReasonPrice
4HEN020Selenite Flower Small1542, 1642, 8572, 31545WIS, DMG, DMG, NBO$89
3indJL019Whip Stitch Stool, Brass2463, 7541, 598712DNR, DNR, DMG$145
2MM170596Barnyard Stool
3154, 652410NBO, WIS$15

<tbody>
</tbody>

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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Welcome back, Heather!

How about:

ABCDEFGHIJKLMNO
1ItemQtyDescriptionPriceReasonClaim #AmtItemDescriptionClaim #'sTotal QtReasonPrice
2HEN0201Selenite Flower Small$89WIS15424HEN020Selenite Flower Small1542, 1642, 8572, 31545WIS, DMG, DMG, NBO$89
3HEN0201Selenite Flower Small$89DMG16423indJL019Whip Stitch Stool, Brass2463, 7541, 598712DNR, DNR, DMG$145
4HEN0201Selenite Flower Small$89DMG85722MM170596Barnyard Stool3154, 652410NBO, WIS$15
5HEN0202Selenite Flower Small$89NBO3154
6indJL0196Whip Stitch Stool, Brass$145DNR2463
7indJL0195Whip Stitch Stool, Brass$145DNR7541
8indJL0191Whip Stitch Stool, Brass$145DMG5987
9MM1705963Barnyard Stool$15NBO3154
10MM1705967Barnyard
Stool
$15WIS6524
11indH1293Table Leg Candle Stand$12DNR9764

<tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
H2=COUNTIF($A$2:$A$11,I2)
J2=INDEX($C$2:$C$11,MATCH(I2,$A$2:$A$11))
L2=SUMIF($A$2:$A$11,I2,$B$2:$B$11)
N2=INDEX($D$2:$D$11,MATCH(I2,$A$2:$A$11))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
I2{=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))))}
K2{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$F$2:$F$11,""))}
M2{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$E$2:$E$11,""))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
Worksheet Formulas
CellFormula
H2=COUNTIF($A$2:$A$11,I2)
J2=INDEX($C$2:$C$11,MATCH(I2,$A$2:$A$11))
L2=SUMIF($A$2:$A$11,I2,$B$2:$B$11)
N2=INDEX($D$2:$D$11,MATCH(I2,$A$2:$A$11))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
I2{=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))))}
K2{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$F$2:$F$11,""))}
M2{=TEXTJOIN(", ",TRUE,IF($A$2:$A$11=I2,$E$2:$E$11,""))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
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.
vbtECjf.png


Now, the report that is pulling information from this sheet is in another workbook entirely. I cannot combine the two.
 
Upvote 0
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.
 
Upvote 0
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:
8BcAbaR.png


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:
tDq4wgU.png


Thank you again.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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