Latest Data pull-up from the corresponding date

[Sunil Pinto]

Can you please tell me if anyone can solve this puzzle?

Please refer to the below data

Name	Date	Advance	Bill	Balance
A	1 Feb 2024​	1,000	250	750
B	1 Feb 2024​	2,000	275	1,725
A	10 Feb 2024​	750	350	400
C	10 Feb 2024​	2,500	450	2,050
D	14 Feb 2024​	1,800	550	1,250
C	15 Feb 2024​	2,050	300	1,750
B	1 Mar 2024​	1,725	300	1,425
D	9 Mar 2024​	1,250	400	850
A	1 Apr 2024​	400	100	300
B	1 Apr 2024​	1,425	350	1,075
D	5 Apr 2024​	850	350	500
C	27 Apr 2024​	1,750	350	1,400
A	1 May 2024​	300	300	-
C	15 May 2024​	1,400	500	900
D	19 May 2024​	500	200	300
B	1 Jun 2024​	1,075	600	475
C	19 Jun 2024​	900	250	650
D	23 Jun 2024​	300	300	-
C	25 Jun 2024​	650	150	500
B	10 Jul 2024​	475	475	-
C	27 Oct 2024​	500	500	-

In Cell "H4" I needed advanced balance if I entered the date in "G4"

If H4=29/2/2024, result in G4= 5,125 (Details of workings are below for your reference.

29 Feb 2024​	5,125.00	(A-400, B-1,725,C-1,750,D-1,250)
31 Mar 2024​	4,425.00	(A-400,B-1425,C1750,D-850)
30 Apr 2024​	3,275.00	(A-300,B-1075,C-1400,D-500)
31/05/2024​	2,275.00	(A-0,B-1075,C-900,D-300)
30/06/2024​	975.00	(A-0,B-475,C-500,D-0)
31/07/2024​	500.00	(A-0,B-0,C-500,D-0)
31/08/2024​	500.00	(A-0,B-0,C-500,D-0)
30/09/2024​	500.00	(A-0,B-0,C-500,D-0)
31/10/2024​	-	(A-0,B-0,C-0,D-0)

Thanks a lot in advance whoever the expert fixes this task


Regards,

Sunil PInto

 

[kvsrinivasamurthy]

Are the columns in 1st table are E,D,C,B,A From left to right.

 

[RobP]

Well, its an absolutely brutal formula, but it appears to work. Don't ask me how it works, as I was only taught the theory this morning by @Fluff

I'm unclear (due to the way you wrote your post) if your date went in 'G' or 'H' - so if I've got it inverted, you can switch the H's & G's in the formula...

Good luck
cheers
Rob

Book1
	A	B	C	D	E	F	G	H
1	Name	Date	Advance	Bill	Balance		28/02/2024	5125
2	A	01/02/2024	1,000	250	750
3	B	01/02/2024	2,000	275	1,725
4	A	10/02/2024	750	350	400
5	C	10/02/2024	2,500	450	2,050
6	D	14/02/2024	1,800	550	1,250
7	C	15/02/2024	2,050	300	1,750
8	B	01/03/2024	1,725	300	1,425
9	D	09/03/2024	1,250	400	850
10	A	01/04/2024	400	100	300
11	B	01/04/2024	1,425	350	1,075
12	D	05/04/2024	850	350	500
13	C	27/04/2024	1,750	350	1,400
14	A	01/05/2024	300	300	-
15	C	15/05/2024	1,400	500	900
16	D	19/05/2024	500	200	300
17	B	01/06/2024	1,075	600	475
18	C	19/06/2024	900	250	650
19	D	23/06/2024	300	300	-
20	C	25/06/2024	650	150	500
21	B	10/07/2024	475	475	-
22	C	27/10/2024	500	500	-
23
24
Sheet1

Cell Formulas
Range	Formula
H1	H1	=INDEX($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)),LOOKUP(2,1/($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0))="A"),ROW($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)))-ROW(A1)+1),5)+INDEX($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)),LOOKUP(2,1/($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0))="B"),ROW($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)))-ROW(A1)+1),5)+INDEX($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)),LOOKUP(2,1/($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0))="C"),ROW($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)))-ROW(A1)+1),5)+INDEX($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)),LOOKUP(2,1/($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0))="D"),ROW($A$1:INDEX($E:$E,MATCH(INDEX(B:B,MATCH(G1,B:B,1)),B:B,0)))-ROW(A1)+1),5)

 

[Sunil Pinto]

"Mr. Robp, you're incredible!

Your help has made my day.

Thank you, thank you, thank you!"

 

[RobP]

tour welcome ...
Rob

 

[Sunil Pinto]

Dear All,
In above formula names of "A" Columns are integrated within the formula as "A", "B","C","D". Actually this is made me as obstacle's to use this formula. In my actual data, which contain large number of names and I not able to integrate within the formula.

Can any body help me to further develop the above formula

Thanks & Regards,

Sunil Pinto

 

[Fuji]

vba, if you like
to the sheet module
1) right click on the sheet tab and [view code]
2) paste the following code and hit Alt + F11 to go back to Excel
Code will run when G4 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim a, i&, dic As Object
    If Target.Address(0, 0) <> "G4" Then Exit Sub
    Application.EnableEvents = False
    Target(, 2).ClearContents
    If IsDate(Target) Then
        Set dic = CreateObject("Scripting.Dictionary")
        a = [a1].CurrentRegion.Value
        For i = UBound(a, 1) To 1 Step -1
            If (a(i, 2) <= Target) Then
                If Not dic.exists(a(i, 1)) Then dic(a(i, 1)) = IIf(a(i, 5) = "-", 0, a(i, 5))
            End If
        Next
        Target(, 2) = Application.Sum(dic.items)
    End If
    Application.EnableEvents = True
End Sub

 

[RobP]

Or if you can look for the names one at a time, you can type the relevant name into a cell, say Q1 and change the "A" to =Q1.

then you can remove the other 75% of my formula for B, C and D references.

Rgds
Rob

 

[RobP]

Then of course the other option if its a BIG problem would be to upgrade yourself from Excel 2016 to O365, as this brings many new formulas we could use to solve your problem in an easier way.

Rob