Need help grouping Categories and Sub-Categories

desibouy

Board Regular
Joined
Nov 20, 2014
Messages
98
Office Version
  1. 365
Platform
  1. Windows
Hello,

I need to run a report for work to show me revenue and qty sold in each categories, and sub-categories.
Unfortunately my ERP system doesn't break the category down so I have had to do it manually in Excel, this is how it's shown on our system - "Catheters > Gloves > Powder Free Gloves",I split this using Text to Column (I'm ignoring the Grandchild Category).

I tried using the filter option, yes it gives me the data I need but I have no idea to group it. Reason being I have 5407 Rows, 26 Categories and 166 Sub-Categories.
I can't without manually having to create a new report and copy paste information i.e.

for Category -

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Qty Sold[/TD]
[TD]Amount.[/TD]
[/TR]
[TR]
[TD]Assistive Furniture[/TD]
[TD]2167[/TD]
[TD]342043.83[/TD]
[/TR]
</tbody>[/TABLE]

&

for Sub-Category


[TABLE="width: 500"]
<tbody>[TR]
[TD]Catergory[/TD]
[TD]Sub-Category[/TD]
[TD]Qty Sold[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Assistive Furniture[/TD]
[TD]Bed & Accessories[/TD]
[TD]400[/TD]
[TD]61618.07[/TD]
[/TR]
</tbody>[/TABLE]





Is there a way to do quickly or automatically? It would take me several hours to do this especially with 166 Sub-Categories.

Thank
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
without seeing original data (representative example) it's hard to say something more but maybe try PivotTable
 
Last edited:
Upvote 0
without seeing original data (representative example) it's hard to say something more but maybe try PivotTable

Hello,

Sorry I just remembered that I didn't post a snippet so was going to but then you posted too lol. Thanks

Here's a small sample of what my file looks like -

CategorySub-CategoryQty SoldAmount
Wound Care Products Gauze & Sponges60048943.56
Incontinence Supplies Underpads474528422.55
Wound Care Products Gauze & Sponges42446293.76
Catheters Gloves392116332.58
Incontinence Supplies Underwear386761823.76
Catheters Catheter Accessories35943558.06
Respiratory Care Oxygen Masks and Cannulas34413406.59
Catheters Gloves325413421.63
Catheters Gloves293317188.55
Accessories Bedroom Accessories2700332990.76
Wound Care Products Gauze & Sponges24431271.07
Wound Care Products Gauze & Sponges23881170.12
Catheters Gloves229213186.48
Respiratory Care Compressors / Nebulizers22913413.59
Catheters Intermittent Catheters22842250.61
Urological Urological Irrigation Products21334242.68
Wound Care Products Adhesive Tapes2021990.29
Wound Care Products Gauze & Sponges1878920.22
Diagnostic Products Blood Collection Set16364891.64
Catheters Intermittent Catheters16101593.9
Wound Care Products Medicated Wound Dressings15351519.65
Wound Care Products Medicated Wound Dressings15249116.76
Incontinence Supplies Underpads149213368.12
Skin Care Products Perineal/Skin Cleansers14822805.3

<tbody>
</tbody>


p.s - never used pivots
 
Last edited:
Upvote 0
p.s - never used pivots
maybe time to try :)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Category[/td][td=bgcolor:#DDEBF7]Sub-Category[/td][td=bgcolor:#DDEBF7]Sum of Qty Sold[/td][td=bgcolor:#DDEBF7]Sum of Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Accessories[/td][td]Bedroom Accessories[/td][td]
2700​
[/td][td]
332990.76​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Catheters[/td][td]Catheter Accessories[/td][td]
3594​
[/td][td]
3558.06​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Gloves[/td][td]
12400​
[/td][td]
60129.24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Intermittent Catheters[/td][td]
3894​
[/td][td]
3844.51​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Diagnostic Products[/td][td]Blood Collection Set[/td][td]
1636​
[/td][td]
4891.64​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Incontinence Supplies[/td][td]Underpads[/td][td]
6237​
[/td][td]
41790.67​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Underwear[/td][td]
3867​
[/td][td]
61823.76​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Respiratory Care[/td][td]Compressors / Nebulizers[/td][td]
2291​
[/td][td]
3413.59​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Oxygen Masks and Cannulas[/td][td]
3441​
[/td][td]
3406.59​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Skin Care Products[/td][td]Perineal/Skin Cleansers[/td][td]
1482​
[/td][td]
2805.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Urological[/td][td]Urological Irrigation Products[/td][td]
2133​
[/td][td]
4242.68​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Wound Care Products[/td][td]Adhesive Tapes[/td][td]
2021​
[/td][td]
990.29​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Gauze & Sponges[/td][td]
16957​
[/td][td]
18598.73​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Medicated Wound Dressings[/td][td]
3059​
[/td][td]
10636.41​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
65712
[/td][td=bgcolor:#DDEBF7]
553122.23
[/td][/tr]
[/table]


is that what you want ?
 
Upvote 0
is that what you want ?

Yes sir exactly how I want it. Looks Gorgeous however just one thing -
in your example the the totals are for Sub-Categories, I also need to show how much each category made too. (Qty and Amount). I was thinking hiding those Sub-categories or something, so I'm just left with Categories and the Amounts.
 
Upvote 0
you mean like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Category[/td][td=bgcolor:#DDEBF7]Sub-Category[/td][td=bgcolor:#DDEBF7]Sum of Qty Sold[/td][td=bgcolor:#DDEBF7]Sum of Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Accessories[/td][td]Bedroom Accessories[/td][td]
2700​
[/td][td]
332990.76​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Accessories Total[/td][td][/td][td]
2700
[/td][td]
332990.76
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Catheters[/td][td]Catheter Accessories[/td][td]
3594​
[/td][td]
3558.06​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Gloves[/td][td]
12400​
[/td][td]
60129.24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Intermittent Catheters[/td][td]
3894​
[/td][td]
3844.51​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Catheters Total[/td][td][/td][td]
19888
[/td][td]
67531.81
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Diagnostic Products[/td][td]Blood Collection Set[/td][td]
1636​
[/td][td]
4891.64​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Diagnostic Products Total[/td][td][/td][td]
1636
[/td][td]
4891.64
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Incontinence Supplies[/td][td]Underpads[/td][td]
6237​
[/td][td]
41790.67​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Underwear[/td][td]
3867​
[/td][td]
61823.76​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Incontinence Supplies Total[/td][td][/td][td]
10104
[/td][td]
103614.43
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Respiratory Care[/td][td]Compressors / Nebulizers[/td][td]
2291​
[/td][td]
3413.59​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Oxygen Masks and Cannulas[/td][td]
3441​
[/td][td]
3406.59​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Respiratory Care Total[/td][td][/td][td]
5732
[/td][td]
6820.18
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Skin Care Products[/td][td]Perineal/Skin Cleansers[/td][td]
1482​
[/td][td]
2805.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Skin Care Products Total[/td][td][/td][td]
1482
[/td][td]
2805.3
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Urological[/td][td]Urological Irrigation Products[/td][td]
2133​
[/td][td]
4242.68​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Urological Total[/td][td][/td][td]
2133
[/td][td]
4242.68
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Wound Care Products[/td][td]Adhesive Tapes[/td][td]
2021​
[/td][td]
990.29​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Gauze & Sponges[/td][td]
16957​
[/td][td]
18598.73​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Medicated Wound Dressings[/td][td]
3059​
[/td][td]
10636.41​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Wound Care Products Total[/td][td][/td][td]
22037
[/td][td]
30225.43
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
65712
[/td][td=bgcolor:#DDEBF7]
553122.23
[/td][/tr]
[/table]
 
Upvote 0
OMG!! LOVE YOU, that was easy. First time using it and it was a doddle. Can't believe how easy that was honestly. You've opened a whole new world for me haha.

Just one question. My totals are showing at the top, while yours were at the bottom.
p19u5f
No biggie but it reads better being at bottom. I couldn't see anything in the options.

http://prntscr.com/p19u5f


p19u5f
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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