Want to say thank you immediately for anyone offering assistance to my dilemma since I'm not sure where to begin. It's been 18 years since I've done any type of coding, so I'm basically a newbie with limited understanding and not even sure this is possible to pull off.
I'm using Excel 2013. What I have is a weekly top 100 list that gets updated weekly and is now being done manually (very slow process). I'm looking for a way to automate carrying over Columns B, C, & I to the new data while also taking the Column G (# Months) and putting that into Column H (Previous # of Months) onto the new data and removing the last week's data no longer needed. As this list is ever changing, there are new parts added and items from the previous week not needed any more. For new items, Columns B & C would obvious need to be manually entered due to no data to pull from.
The data I obtain is cut and pasted from another source into the spreadsheet and I just add it in to the previous week for a total of 200 rows of part data. So I can just as easily make a separate spreadsheet for the current week and previous week if there is an opportunity for a simpler solution.
Here is an example of what I am starting out with. Information in bold is my new data, standard text is previous weeks.
Excel 2012
<tbody>
</tbody>
This is how I would like it to end up looking.
Excel 2012
<tbody>
</tbody>
Thank you,
Verill
I'm using Excel 2013. What I have is a weekly top 100 list that gets updated weekly and is now being done manually (very slow process). I'm looking for a way to automate carrying over Columns B, C, & I to the new data while also taking the Column G (# Months) and putting that into Column H (Previous # of Months) onto the new data and removing the last week's data no longer needed. As this list is ever changing, there are new parts added and items from the previous week not needed any more. For new items, Columns B & C would obvious need to be manually entered due to no data to pull from.
The data I obtain is cut and pasted from another source into the spreadsheet and I just add it in to the previous week for a total of 200 rows of part data. So I can just as easily make a separate spreadsheet for the current week and previous week if there is an opportunity for a simpler solution.
Here is an example of what I am starting out with. Information in bold is my new data, standard text is previous weeks.
Excel 2012
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | Item Number | Location | Customer | On-Hand | $ Value | Monthly Usage | # Months | Previous # of Months | Comment |
2 | ABC | 301 | ACT | 110000 | 50000 | 10000 | 11 | 13 | Note 1 |
3 | ABC | 100000 | 50000 | 10000 | 10 | ||||
4 | DEF | 302 | GEN | 35000 | 30000 | 5000 | 7 | 5 | Note 2 |
5 | DEF | 20000 | 30000 | 5000 | 4 | ||||
6 | GAA | 305 | PRT | 50000 | 28500 | 5000 | 10 | 5 | |
7 | GHI | 30000 | 25000 | 7500 | 4 | ||||
8 | JKL | 4000 | 10000 | 2500 | 1.6 | ||||
9 | MNO | 305 | PSG | 3000 | 7500 | 1500 | 2 | 3 | |
10 | MNO | 3000 | 7500 | 1500 | 2 | ||||
11 | PQR | 306 | HAD | 9500 | 5000 | 1000 | 9.5 | 9.5 | Note 3 |
12 | PQR | 8500 | 5000 | 1000 | 8.5 | ||||
13 | STU | 301 | BLP | 3750 | 2500 | 750 | 5 | 5 | |
14 | XYZ | 4000 | 2500 | 1000 | 4 |
<tbody>
</tbody>
Sheet1
This is how I would like it to end up looking.
Excel 2012
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | Item Number | Location | Customer | On-Hand | $ Value | Monthly Usage | # Months | Previous # of Months | Comment |
2 | ABC | 301 | ACT | 100000 | 50000 | 10000 | 10 | 11 | Note 1 |
3 | DEF | 302 | GEN | 20000 | 30000 | 5000 | 4 | 7 | Note 2 |
4 | GHI | 30000 | 25000 | 7500 | 4 | ||||
5 | JKL | 4000 | 10000 | 2500 | 1.6 | ||||
6 | MNO | 305 | PSG | 3000 | 7500 | 1500 | 2 | 2 | |
7 | PQR | 306 | HAD | 8500 | 5000 | 1000 | 8.5 | 9.5 | Note 3 |
8 | XYZ | 4000 | 2500 | 1000 | 4 |
<tbody>
</tbody>
Sheet1
Thank you,
Verill