vba to copy names to each product type

[tonywatsonhelp]

Hi everyone,
I have a sheet that has product numbers is column A and dates in column J
now i E and F i have make and model however over the years these have become incorrect,
so what i need is a macro that can do this,
first sort by column A and Column J (both ascending)
then for each product number in column A find the latest date in column J copy whats in E and F and paste it into all rows with the same product number.
so simply put, for each product number in column A whatever the data in E and F of the row with the newest date in column J that data should be for all rows with that product number.

hope thats clear, please help if you can i totally stuck
Thanks
Tony

 

[psgoldberg]

Perhaps try using a formula to create a new table with the values you want - usually simpler and safer than a macro.

If the table name is "tblData" with headings of the columns: Product, ColumnE, ColumnF, and it is sorted Descending by Product and Date then the formula =XLOOKUP(tblData[@Product],tblData[Product],tblData[[ColumnE]:[ColumnF]],0,1) will give you the values you want. Place the formula on the same row and to the right of the table.

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product	B	C	D	ColumnE	ColumnF	G	H	I	Date		Correct E	Correct F
2	A123				Good	Better				4/6/2004		Good	Better
3	A123				SDF	GHJ				4/6/2002		Good	Better
4	A123				ERT	FGH				4/6/1990		Good	Better
5	A123				EFG	DFG				4/6/1989		Good	Better
6	A123				ABC	DEF				4/6/1988		Good	Better
7	A444				Another	OK				4/6/2009		Another	OK
8	A444				SDF	GHJ				4/6/2002		Another	OK
9	A444				ERT	FGH				4/6/1990		Another	OK
10	A444				EFG	DFG				4/6/1989		Another	OK
11	A444				ABC	DEF				4/6/1988		Another	OK
Sheet1

Cell Formulas
Range	Formula
L2:M11	L2	=XLOOKUP(tblData[@Product],tblData[Product],tblData[[ColumnE]:[ColumnF]],0,1)
Dynamic array formulas.

 

[tonywatsonhelp]

I need a vba solution but thanks