Subtotal & Regroup onto different tabs

[kgkev]

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.

 

[kgkev]

To make it simpler here is a section showing the data from the spread sheet

 

[DonkeyOte]

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]

BRILLIANT!!

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

 

[DonkeyOte]

Glad it worked. We all have our faults and mine is being a nerd.