Filtering debtors report

[drephy]

Please, I have a data sample bellow. row2 to row22 contain debtors details in while row28 to row30 contains just the debtors list with the terminated period. I want to get a report that will filter the periods and the corresponding amount from 2009/2010 to the terminated period as contained in C28 - C31.

[TABLE="class: grid, width: 539"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]idnumber[/TD]
[TD]name[/TD]
[TD]semester[/TD]
[TD]period[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]First Semester[/TD]
[TD]2009/2010[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]First Semester[/TD]
[TD]2010/2011[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]Second Semester[/TD]
[TD]2010/2011[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]First Semester[/TD]
[TD]2011/2012[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]Second Semester[/TD]
[TD]2011/2012[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]First Semester[/TD]
[TD]2012/2013[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]Second Semester[/TD]
[TD]2012/2013[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]Second Semester[/TD]
[TD]2009/2010[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]First Semester[/TD]
[TD]2010/2011[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]Second Semester[/TD]
[TD]2010/2011[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]First Semester[/TD]
[TD]2011/2012[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]Second Semester[/TD]
[TD]2011/2012[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]First Semester[/TD]
[TD]2012/2013[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]Second Semester[/TD]
[TD]2012/2013[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]Second Semester[/TD]
[TD]2009/2010[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]First Semester[/TD]
[TD]2010/2011[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]Second Semester[/TD]
[TD]2010/2011[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]First Semester[/TD]
[TD]2011/2012[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]Second Semester[/TD]
[TD]2011/2012[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]First Semester[/TD]
[TD]2012/2013[/TD]
[TD="align: right"]17700[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]Second Semester[/TD]
[TD]2012/2013[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]idnumber[/TD]
[TD]name[/TD]
[TD]periodterminated[/TD]
[TD]report[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]01/5938/UE[/TD]
[TD]IDOKO I SOLOMON[/TD]
[TD]2011/2012[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]01/5777/RE[/TD]
[TD]IKERIONWU OSI D[/TD]
[TD]2009/2010[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]01/5873/UE[/TD]
[TD]UNUODE J AUSTINE[/TD]
[TD]2012/2013[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]01/5943/RE[/TD]
[TD]KALU KALU T[/TD]
[TD]2009/2010[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Please, this is how i want the report to look like.

if A28 IS FOUND IN A2:D22 then return the corresponding of column E matching c28 (which is 2011/2012) backward using "()" for the amount due and "," as session separator

Example: in column D28 I should have the following report as below
2011/2012(4000), 2011/2012(17700), 2010/2011(4000), 2010/2011(17700), 2009/2010(4000).

I will so much appreciate if you guys can give me a solution.

 

[Arithos]

Hi,

I have a code here for you, (VBA)

Sub Report2()


For j = 1 To 4
    idnumber = Cells(27 + j, 1).Value
    idnumberrow = Range("A2:A22")
    R = 1
   For Each c In idnumberrow
            R = R + 1
              If c = idnumber Then
                Reporttotal = "," & Cells(R, 4).Value & " (" & Cells(R, 5).Value & ")"
                Cells(27 + j, 4).Value = Cells(27 + j, 4).Value & Reporttotal
             End If
    Next c
Next j
End Sub

D28 should contain 1 more entry:

2009/2010 (4000),2010/2011 (17700),2010/2011 (4000),2011/2012 (17700),2011/2012 (4000),2012/2013 (17700),2012/2013 (4000)

Atleast I hope so, rows 16, to 22. Seven entrys.
And this macro gets the info I posted above.

Does this solve it?

 

[drephy]

Thank you so much. I believe this can help but i don't even know how to use macros. can you just give me a simple formula like IF Function?

 

[Arithos]

Thank you so much. I believe this can help but i don't even know how to use macros. can you just give me a simple formula like IF Function?

yeah, most likely.
I would use a SUMPRODUCT formula combined with and Index-match, this would however need some "helping columns".
You can do a quick search on how to insert a macro tho. and see if you get it to work.

I do not have time to help you further at the moment, but if its not solved with formulas yet I'll have a look at a later time

 

[drephy]

Thank you so much #Arithos

 

[Arithos]

Thank you so much #Arithos

Ok, Dropbox link for a workbook I created for you, should be pretty straight forward. If you have any questions just ask. (Open link, click on Drephy.xlsm, then download it, then you have a working workbook)

https://www.dropbox.com/sh/sobp20lafdqnf5r/AAAujT7hYgYVOIGwxiFMsNr4a?dl=0

You might have to enable macros.

Paste as much info you want in columns A:E, and the crierias you want to use in your search in columns G:J.


Alternative link: http://www.uploadhosting.co/uploads/84.215.67.103/Drephy.xlsm

 

[drephy]

I can't thank you enough Arothos. U just solved my problem. Thank you.

 

[Arithos]

I can't thank you enough Arothos. U just solved my problem. Thank you.

Glad to help, The macro is dynamic, so you can use as much data as you like, as long as its placed in the same rows