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!

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

<tbody>
</tbody>

 

[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