Conditional VLOOKUP

[thebenjames]

What is the easiest way to pull through the data I need into columns H and I for each ULN? Some ULNs have multiple entries for "1 - Total training price" so I would like the latest one if possible. Some also don't necessarily have a "2 - Total assessment price", but if that pulls through as N/A that is absolutely fine.

finances.csv
	A	B	C	D	E	F	G	H	I	J
1	ULN	TNP type	TNP amount	TNP date			ULN	1 - Total training price	2 - Total assessment price	Total
2	1000001562	1 - Total training price	27000	17/09/2019			1000001562
3	1000159123	1 - Total training price	13231	28/04/2020			1000159123
4	1000159123	2 - Total assessment price	1400	28/04/2020			1000208663
5	1000208663	1 - Total training price	11400	08/01/2020			1002135582
6	1002135582	1 - Total training price	10900	01/07/2021			1002442620
7	1002135582	2 - Total assessment price	500	01/07/2021			1002535335
8	1002135582	1 - Total training price	11400	20/05/2021			1036930806
9	1002442620	1 - Total training price	16490	15/10/2020			1039989224
10	1002442620	2 - Total assessment price	1510	15/10/2020			1060682426
11	1002535335	1 - Total training price	11400	08/01/2020			1114082901
12	1036930806	1 - Total training price	22285	14/10/2019			1124359426
13	1036930806	2 - Total assessment price	1715	14/10/2019			1135648777
14	1039989224	1 - Total training price	19710	18/09/2018			1143934996
15	1039989224	2 - Total assessment price	690	18/09/2018			1151349940
16	1060682426	2 - Total assessment price	450	07/05/2019			1176791922
17	1060682426	1 - Total training price	26550	25/09/2018			1188538526
18	1114082901	1 - Total training price	16490	13/05/2021			1191531456
19	1114082901	2 - Total assessment price	1510	13/05/2021			1193462966
Sheet3

 

[mohadin]

May be

Sub test2()
Dim a As Variant
Dim i As Long
a = Sheets("sheet1").Cells(1).CurrentRegion.Value2
With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .Exists(a(i, 1)) Then
                .Add a(i, 1), Array(a(i, 1), a(i, 3), "")
             Else
             w = .Item(a(i, 1))
             w(2) = a(i, 3)
             .Item(a(i, 1)) = w
            End If
        Next
Sheets("sheet1").Cells(2, 7).Resize(.Count, 3) = Application.Transpose(Application.Transpose(.items))
End With
End Sub

 

[jasonb75]

Is this what you're looking for?

Book1
	A	B	C	D	E	F	G	H	I	J
1	ULN	TNP type	TNP amount	TNP date			ULN	1 - Total training price	2 - Total assessment price	Total
2	1000001562	1 - Total training price	27000	17-Sep-19			1000001562	27000	0	27000
3	1000159123	1 - Total training price	13231	28-Apr-20			1000159123	13231	1400	14631
4	1000159123	2 - Total assessment price	1400	28-Apr-20			1000208663	11400	0	11400
5	1000208663	1 - Total training price	11400	08-Jan-20			1002135582	10900	500	11400
6	1002135582	1 - Total training price	10900	01-Jul-21			1002442620	16490	1510	18000
7	1002135582	2 - Total assessment price	500	01-Jul-21			1002535335	11400	0	11400
8	1002135582	1 - Total training price	11400	20-May-21			1036930806	22285	1715	24000
9	1002442620	1 - Total training price	16490	15-Oct-20			1039989224	19710	690	20400
10	1002442620	2 - Total assessment price	1510	15-Oct-20			1060682426	26550	450	27000
11	1002535335	1 - Total training price	11400	08-Jan-20			1114082901	16490	1510	18000
12	1036930806	1 - Total training price	22285	14-Oct-19			1124359426	0	0	0
13	1036930806	2 - Total assessment price	1715	14-Oct-19			1135648777	0	0	0
14	1039989224	1 - Total training price	19710	18-Sep-18			1143934996	0	0	0
15	1039989224	2 - Total assessment price	690	18-Sep-18			1151349940	0	0	0
16	1060682426	2 - Total assessment price	450	07-May-19			1176791922	0	0	0
17	1060682426	1 - Total training price	26550	25-Sep-18			1188538526	0	0	0
18	1114082901	1 - Total training price	16490	13-May-21			1191531456	0	0	0
19	1114082901	2 - Total assessment price	1510	13-May-21			1193462966	0	0	0
Sheet2

Cell Formulas
Range	Formula
H2:I19	H2	=MAXIFS($C:$C,$A:$A,$G2,$B:$B,H$1,$D:$D,MAXIFS($D:$D,$A:$A,$G2,$B:$B,H$1))
J2:J19	J2	=SUM(H2:I2)