Pulling Latest value for each ID via vba

[Smurphster16]

Hi Guys,

Basically I have a fairly large spreadsheet which contains ID's and then values in the adjacent column with dates in the following column.

For each ID I want to take the value corresponding to the latest date, bearing in mind each ID could have a different number of values and therefore dates.

What is the best solution /is there a VBA solution to this?

Thanks in advance,

Phil

 

[Crystalyzer]

It would be really helpful if you could post a sample of the data

 

[James006]

Hi,

You can have either an Array Formula, or a VBA solution ...

It depends on how you need your end result to be displayed ...

 

[joshman108]

If you sort the data on ID then DATE ascending, and assuming your three columns are in A,B and C, and if you also had the unique IDs in columns E started on E1, you could put this formula in F1 and drag it down

=INDEX(B:B,MATCH(E1,A:A,0)+COUNTIFS(A:A,E1)-1)

 

[JoeMo]

Sounds like a formula solution is possible. Is your preference for a VBA solution?

Either way you can improve your chance of getting a solution if you post some sample data so we can see which columns/rows hold the data, at least for the ID,Value and Date data you describe in your post.

 

[Smurphster16]

sorry can't find an option to insert attachment with sample data?- what is the best way?

thanks

 

[James006]

Hi,

Could you describe your worksheet structure ...

 

[Smurphster16]

Hi guys this is a sample data for the problem;

I would like to know a vba and formula solution if possible please as this may help me further down the line.

Thanks so much!

[TABLE="width: 368"]
<tbody>[TR]
[TD]id[/TD]
[TD]company name[/TD]
[TD]Value[/TD]
[TD]Value date[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£1,002[/TD]
[TD="align: right"]31/05/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£2,004[/TD]
[TD="align: right"]30/06/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£4,008[/TD]
[TD="align: right"]31/07/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£8,016[/TD]
[TD="align: right"]31/08/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£16,032[/TD]
[TD="align: right"]30/09/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£32,064[/TD]
[TD="align: right"]31/10/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£64,128[/TD]
[TD="align: right"]30/11/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£128,256[/TD]
[TD="align: right"]31/12/2015[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£256,512[/TD]
[TD="align: right"]31/01/2016[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£513,024[/TD]
[TD="align: right"]29/02/2016[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£1,026,048[/TD]
[TD="align: right"]31/03/2016[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£2,052,096[/TD]
[TD="align: right"]30/04/2016[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£4,104,192[/TD]
[TD="align: right"]31/05/2016[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£8,208,384[/TD]
[TD="align: right"]30/06/2016[/TD]
[/TR]
[TR]
[TD]bb23[/TD]
[TD]Auto Fiedelity sales[/TD]
[TD="align: right"]£16,416,768[/TD]
[TD="align: right"]31/07/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£3,001[/TD]
[TD="align: right"]31/03/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£3,601.20[/TD]
[TD="align: right"]30/04/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£4,321.44[/TD]
[TD="align: right"]31/05/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£5,185.73[/TD]
[TD="align: right"]30/06/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£6,222.87[/TD]
[TD="align: right"]31/07/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£7,467.45[/TD]
[TD="align: right"]31/08/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£8,960.94[/TD]
[TD="align: right"]30/09/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£10,753.13[/TD]
[TD="align: right"]31/10/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£12,903.75[/TD]
[TD="align: right"]30/11/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£15,484.50[/TD]
[TD="align: right"]31/12/2016[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£18,581.40[/TD]
[TD="align: right"]31/01/2017[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£22,297.68[/TD]
[TD="align: right"]28/02/2017[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£26,757.22[/TD]
[TD="align: right"]31/03/2017[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£32,108.66[/TD]
[TD="align: right"]30/04/2017[/TD]
[/TR]
[TR]
[TD]bb54[/TD]
[TD]Hertford auto trades[/TD]
[TD="align: right"]£38,530.39[/TD]
[TD="align: right"]31/05/2017[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,005.00[/TD]
[TD="align: right"]31/05/2017[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,105.25[/TD]
[TD="align: right"]£42,916.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,210.51[/TD]
[TD="align: right"]£42,947.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,321.04[/TD]
[TD="align: right"]£42,978.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,437.09[/TD]
[TD="align: right"]£43,008.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,558.94[/TD]
[TD="align: right"]£43,039.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,686.89[/TD]
[TD="align: right"]£43,069.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,821.24[/TD]
[TD="align: right"]£43,100.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£2,962.30[/TD]
[TD="align: right"]£43,131.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£3,110.41[/TD]
[TD="align: right"]£43,159.00[/TD]
[/TR]
[TR]
[TD]bb16[/TD]
[TD]smith automotives[/TD]
[TD="align: right"]£3,265.93[/TD]
[TD="align: right"]£43,190.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

With your data in A1:D42, e.g. id bb23 ...

you could test following array formula

=INDEX(C2:C42,MATCH(MAX(IF(A2:A42="bb23",D2:D42)),D2:D42,0))

Hope this will help

 

[JoeMo]

Copy the formulas in H2:I2 down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	id	company name	Value	Value date	sort		id	Latest Date	Value
2	bb23	Auto Fiedelity sales	2,004	6/30/2015	0.02758		bb16	3/31/2018	3,265.93
3	bb16	smith automotives	2,558.94	10/31/2017	0.02827		bb23	7/31/2016	16,416,768
4	bb23	Auto Fiedelity sales	256,512	1/31/2016	0.06747		bb54	5/31/2017	38,530.39
5	bb16	smith automotives	2,321.04	8/31/2017	0.07741
6	bb23	Auto Fiedelity sales	4,008	7/31/2015	0.13401
7	bb54	Hertford auto trades	7,467.45	8/31/2016	0.15235
8	bb23	Auto Fiedelity sales	16,416,768	7/31/2016	0.18378
9	bb54	Hertford auto trades	5,185.73	6/30/2016	0.21211
10	bb16	smith automotives	2,210.51	7/31/2017	0.23401
11	bb54	Hertford auto trades	15,484.50	12/31/2016	0.24165
12	bb23	Auto Fiedelity sales	1,002	5/31/2015	0.24625
13	bb16	smith automotives	2,962.30	1/31/2018	0.27605
14	bb54	Hertford auto trades	3,601.20	4/30/2016	0.2957
15	bb23	Auto Fiedelity sales	8,016	8/31/2015	0.34779
16	bb54	Hertford auto trades	22,297.68	2/28/2017	0.35748
17	bb23	Auto Fiedelity sales	4,104,192	5/31/2016	0.35788
18	bb16	smith automotives	2,105.25	6/30/2017	0.37374
19	bb23	Auto Fiedelity sales	32,064	10/31/2015	0.40699
20	bb54	Hertford auto trades	4,321.44	5/31/2016	0.46551
21	bb54	Hertford auto trades	26,757.22	3/31/2017	0.48363
22	bb54	Hertford auto trades	32,108.66	4/30/2017	0.48545
23	bb54	Hertford auto trades	18,581.40	1/31/2017	0.48678
24	bb23	Auto Fiedelity sales	128,256	12/31/2015	0.50437
25	bb16	smith automotives	3,110.41	2/28/2018	0.53849
26	bb16	smith automotives	2,005.00	5/31/2017	0.54067
27	bb54	Hertford auto trades	3,001	3/31/2016	0.58074
28	bb54	Hertford auto trades	10,753.13	10/31/2016	0.67167
29	bb54	Hertford auto trades	8,960.94	9/30/2016	0.68949
30	bb16	smith automotives	2,686.89	11/30/2017	0.76434
31	bb23	Auto Fiedelity sales	2,052,096	4/30/2016	0.78184
32	bb54	Hertford auto trades	6,222.87	7/31/2016	0.78331
33	bb23	Auto Fiedelity sales	1,026,048	3/31/2016	0.80445
34	bb23	Auto Fiedelity sales	8,208,384	6/30/2016	0.81514
35	bb54	Hertford auto trades	12,903.75	11/30/2016	0.87511
36	bb16	smith automotives	3,265.93	3/31/2018	0.89053
37	bb16	smith automotives	2,821.24	12/31/2017	0.90305
38	bb54	Hertford auto trades	38,530.39	5/31/2017	0.92425
39	bb23	Auto Fiedelity sales	64,128	11/30/2015	0.93088
40	bb23	Auto Fiedelity sales	513,024	2/29/2016	0.96551
41	bb16	smith automotives	2,437.09	9/30/2017	0.98706
42	bb23	Auto Fiedelity sales	16,032	9/30/2015	0.99884
Sheet9