zookeepertx
Well-known Member
- Joined
- May 27, 2011
- Messages
- 589
- Office Version
- 365
- Platform
- 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#".
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!
Jenny
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!
Jenny
