How to merge data & deleting data from weekly changing top 100 list?

[Verill]

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

	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

 

[bhasi77]

Hi

Hope this helps:

Assuming your Previous Week Data is in Sheet 1 and New Data in Sheet 2 in same format as mentioned by you:

1	Item Number	Location	Customer	On-Hand	$ Value	Monthly Usage	# Months	Previous # of Months	Comment
1	ABC	=VLOOKUP(B2,Sheet1!B:J,2,FALSE)	=VLOOKUP(B2,Sheet1!B:J,3,FALSE)	100000	50000	10000	10	=VLOOKUP(B2,Sheet1!B:J,7,FALSE)	=VLOOKUP(B2,Sheet1!B:J,7,FALSE)

<tbody>
</tbody><colgroup><col span="8"><col><col></colgroup>

Bhasker Pitchai