Subtotal & Regroup onto different tabs

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
I have a sheet of data that shows each individual line items from a sale. It also shows a brand (A,B,C or D)

I would like to seperate each of the brand from the one data sheet onto 4 seperate tabs on the same workbook.

I would also like each order only to appear once.

The sheet is set up as follows

A = Order number - This should appear once on each sheet and drive the divide for the subtotals

b = Customer

c = Item Cost (this needs to be totalled as per the change in A)

d = Item Price (this needs to be totalled as per the change in A)

e = Freight Cost (this needs to be totalled as per the change in A)

f = Freight Price (this needs to be totalled as per the change in A)

G = Brand (A,B,C,D) this will drive the divide onto the 4 tabs but does not need to be brought across as data onto each sheet.

Any help appreciated.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
To make it simpler here is a section showing the data from the spread sheet

spreadsheetexamplejg5.jpg
 
Last edited:

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Well you have a few options here -- you could create four separate pivot tables so you can report each brand separately rather than trying to create four new worksheet tables...

if you didn't want to use PT and assuming you could use helper columns on your master sheet...

Assume screenshot (master data) is on Sheet1 but let's assume headers in row 1 and data begins in row 2

Formula in H2 (to be copied down for all rows with data -- my example assume 1000 rows)

="o"&$A2&":c"&B2

Formula in I2 (to be copied down for all rows with data -- my example assume 1000 rows)

=IF(COUNTIF($H$1:$H2,$H2)>1,"",$G2)

Formula in J2 (to be copied down for all rows with data -- my example assume 1000 rows)

=IF($I2="","",$I2&COUNTIF($I$1:$I2,$I2))

Now let's assume you have created a new sheet and called it A (for Brand A)

On sheet A in cell A1 type A
in B1 to G1 type your column headers (Order Number, Customer etc...)

Still on sheet A

Formula in A2 (to be copied down for all rows with data -- my example assume 500 rows)

=MATCH($A$1&ROW(A2)-1,Sheet1!$J$1:$J$1000,0)

Formula in B2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",INDEX(Sheet1!$A$1:$G$1000,$A2,1))

Formula in C2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",INDEX(Sheet1!$A$1:$G$1000,$A2,2))

Formula in D2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!C$2:C$1000))

Formula in E2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!D$2:D$1000))

Formula in F2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!E$2:E$1000))

Formula in G2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!F$2:F$1000))


You know have a sheet for Brand A showing only Brand A details grouped at invoice level.

Copy sheet A and rename B -- change B1 to be B and you should have a sheet for B... repeat for C & D.
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
BRILLIANT!!

Not too complicated - I can see how it all works. I can't believe you got it spot on first try!!!
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Glad it worked. We all have our faults and mine is being a nerd.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

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