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 -

CategoryQty SoldAmount.
Assistive Furniture2167342043.83

<tbody>
</tbody>

&

for Sub-Category


CatergorySub-CategoryQty SoldAmount
Assistive FurnitureBed & Accessories40061618.07

<tbody>
</tbody>





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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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 :)

CategorySub-CategorySum of Qty SoldSum of Amount
AccessoriesBedroom Accessories
2700​
332990.76​
CathetersCatheter Accessories
3594​
3558.06​
Gloves
12400​
60129.24​
Intermittent Catheters
3894​
3844.51​
Diagnostic ProductsBlood Collection Set
1636​
4891.64​
Incontinence SuppliesUnderpads
6237​
41790.67​
Underwear
3867​
61823.76​
Respiratory CareCompressors / Nebulizers
2291​
3413.59​
Oxygen Masks and Cannulas
3441​
3406.59​
Skin Care ProductsPerineal/Skin Cleansers
1482​
2805.3​
UrologicalUrological Irrigation Products
2133​
4242.68​
Wound Care ProductsAdhesive Tapes
2021​
990.29​
Gauze & Sponges
16957​
18598.73​
Medicated Wound Dressings
3059​
10636.41​
Grand Total
65712
553122.23

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?

CategorySub-CategorySum of Qty SoldSum of Amount
AccessoriesBedroom Accessories
2700​
332990.76​
Accessories Total
2700
332990.76
CathetersCatheter Accessories
3594​
3558.06​
Gloves
12400​
60129.24​
Intermittent Catheters
3894​
3844.51​
Catheters Total
19888
67531.81
Diagnostic ProductsBlood Collection Set
1636​
4891.64​
Diagnostic Products Total
1636
4891.64
Incontinence SuppliesUnderpads
6237​
41790.67​
Underwear
3867​
61823.76​
Incontinence Supplies Total
10104
103614.43
Respiratory CareCompressors / Nebulizers
2291​
3413.59​
Oxygen Masks and Cannulas
3441​
3406.59​
Respiratory Care Total
5732
6820.18
Skin Care ProductsPerineal/Skin Cleansers
1482​
2805.3​
Skin Care Products Total
1482
2805.3
UrologicalUrological Irrigation Products
2133​
4242.68​
Urological Total
2133
4242.68
Wound Care ProductsAdhesive Tapes
2021​
990.29​
Gauze & Sponges
16957​
18598.73​
Medicated Wound Dressings
3059​
10636.41​
Wound Care Products Total
22037
30225.43
Grand Total
65712
553122.23
 
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,212,927
Messages
6,110,727
Members
448,294
Latest member
jmjmjmjmjmjm

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