Need help grouping Categories and Sub-Categories

desibouy

Board Regular
Joined
Nov 20, 2014
Messages
63
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
without seeing original data (representative example) it's hard to say something more but maybe try PivotTable
 
Last edited:

desibouy

Board Regular
Joined
Nov 20, 2014
Messages
63
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:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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 ?
 

desibouy

Board Regular
Joined
Nov 20, 2014
Messages
63
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.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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
 

desibouy

Board Regular
Joined
Nov 20, 2014
Messages
63
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:

Watch MrExcel Video

Forum statistics

Threads
1,130,438
Messages
5,642,117
Members
417,257
Latest member
Sarahbw

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
Top