Need help grouping Categories and Sub-Categories

D

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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
without seeing original data (representative example) it's hard to say something more but maybe try PivotTable
 
Last edited:
Upvote 0
sandy666 said:
without seeing original data (representative example) it's hard to say something more but maybe try PivotTable
Click to expand...

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
desibouy said:
p.s - never used pivots
Click to expand...
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
sandy666 said:
is that what you want ?
Click to expand...

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
You must log in or register to reply here.

Similar threads

T
  • Question Question
Automate sparkline creation (loop help!)
Replies
2
Views
3K
thebawp
T
E
  • Question Question
Help with pasting cells in Outlook mail and then pasting grouped image charts after, in same email body
Replies
0
Views
508
ejronin
E
O
  • Question Question
Help needed with nested IF formula to total up
2
Replies
12
Views
987
chrismacldn
C
S
  • Question Question
Need a helper celll or a better formula which won't crash Excel.
Replies
0
Views
674
snd
S
B
  • Question Question
Can Excel transfer data From sheet1 to Sheet2, Sheet3, Sheet4, etc.?
2
Replies
11
Views
1K
bm2006
B

Forum statistics

Threads
1,224,816
Messages
6,181,138
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