Get header info fm another sheet, then count and sum by 2 parameters


Well-known Member
May 27, 2011
Office Version
  1. 365
  1. Windows
Hello again! I haven't been here in months because we were on furlough. I have a very tough (for me) macro to do on a short deadline, so hoping someone can help me.
I have 2 workbooks: 1 is just named by whatever PO is detailed in it. In this example it's called "Copy of 10100552517-working" (I'll just refer to it as PO# if that's okay) and the 2nd is called "Store Nos. by DC-copy" (I'll refer to it as Store Nos.). I'm only concerned with columns AF-whatever the last column of data is on "PO#".
  • The columns indicate stores and will vary in number.
  • The rows list styles and will also vary in number.
  • There is a header row which will always be row 12. AF12 shows the first store on the PO; in this example "1002/NP"

Workbook "Store Nos." lists all the stores, but they're divided up sort of randomly across columns. Each list/column has a Warehouse shown above it; Warehouse 7079, Warehouse 7099 or Warehouse 7077

So - what I need to do is, in workbook PO#, make a copy of Sheet PO and call it "PO by Distribution Center". Then insert 2 rows below row 12. Then extract the first 4 digits from the header row - for example 1002. Paste that number in row 14. Next, find that store number in workbook Store Nos (ignoring the letters on that worksheet since they're not formatted consistently). Find out which Warehouse that store is located under and place that 4 digit number in row 13 of workbook PO#.

From here on, we'll stay in workbook PO# on sheet "PO by Distribution Center".

The best option seems to be to create a table a couple of rows below the last row of data, starting in AE. Column AE should list the stores as they are on the original sheet - for example 1010/LA, 1012/SF, etc. Column AF should be headed "DC", AG = "STORE", AH = "TOTAL # STYLES BY STORE", AI = "TOTAL QTY # UNITS BY STORE" AND AJ = "TOTAL # UNITS BY DC". (Bolding and underlining are just trying to make this easier to read). The DC column simply lists the DC (Warehouse) fetched from the Store Nos workbook; STORE column simply lists the 4 digit store number. Then, sort ALL of the table by DC first and STORE second.
Next column of the table should calculate how many different styles were received at each store; the following column should sum the total # of units received at each store. Finally, the last column needs to sum the total number of units received at that DC.

I'd post some coding, but I don't actually HAVE any viable code. I have a few little bits of very mundane things, but nothing that will help.

I know this is complicated as all get out, but I hope I've explained it at least a LITTLE bit. Please ask me anything about whatever I've messed up. I'll try to upload images of the 2 workbooks; it'll look a lot clearer with visual aids.

If you've made it this far, you have my undying gratitude and I'll be even more grateful if someone can enlighten me!

Thank you!


PO BI-sht 1.JPG
PO BI-sht2.JPG
Store No.JPG

Some videos you may like

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

Watch MrExcel Video

Forum statistics

Latest member

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