# Subtotal & Regroup onto different tabs

#### kgkev

##### Well-known Member
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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### kgkev

##### Well-known Member
To make it simpler here is a section showing the data from the spread sheet

Last edited:

#### DonkeyOte

##### MrExcel MVP
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
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
Glad it worked. We all have our faults and mine is being a nerd.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,675
Messages
5,838,714
Members
430,566
Latest member
ChanchalSingh

### 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.

### Which adblocker are you using?

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

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