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.

	A	B	C	D	E
1	idnumber	name	semester	period	amount
2	01/5777/RE	IKERIONWU OSI D	First Semester	2009/2010	17700
3	01/5777/RE	IKERIONWU OSI D	First Semester	2010/2011	17700
4	01/5777/RE	IKERIONWU OSI D	Second Semester	2010/2011	4000
5	01/5777/RE	IKERIONWU OSI D	First Semester	2011/2012	17700
6	01/5777/RE	IKERIONWU OSI D	Second Semester	2011/2012	4000
7	01/5777/RE	IKERIONWU OSI D	First Semester	2012/2013	17700
8	01/5777/RE	IKERIONWU OSI D	Second Semester	2012/2013	4000
9	01/5873/UE	UNUODE J AUSTINE	Second Semester	2009/2010	4000
10	01/5873/UE	UNUODE J AUSTINE	First Semester	2010/2011	17700
11	01/5873/UE	UNUODE J AUSTINE	Second Semester	2010/2011	4000
12	01/5873/UE	UNUODE J AUSTINE	First Semester	2011/2012	17700
13	01/5873/UE	UNUODE J AUSTINE	Second Semester	2011/2012	4000
14	01/5873/UE	UNUODE J AUSTINE	First Semester	2012/2013	17700
15	01/5873/UE	UNUODE J AUSTINE	Second Semester	2012/2013	4000
16	01/5938/UE	IDOKO I SOLOMON	Second Semester	2009/2010	4000
17	01/5938/UE	IDOKO I SOLOMON	First Semester	2010/2011	17700
18	01/5938/UE	IDOKO I SOLOMON	Second Semester	2010/2011	4000
19	01/5938/UE	IDOKO I SOLOMON	First Semester	2011/2012	17700
20	01/5938/UE	IDOKO I SOLOMON	Second Semester	2011/2012	4000
21	01/5938/UE	IDOKO I SOLOMON	First Semester	2012/2013	17700
22	01/5938/UE	IDOKO I SOLOMON	Second Semester	2012/2013	4000
23
24
25
26
27	idnumber	name	periodterminated	report
28	01/5938/UE	IDOKO I SOLOMON	2011/2012
29	01/5777/RE	IKERIONWU OSI D	2009/2010
30	01/5873/UE	UNUODE J AUSTINE	2012/2013
31	01/5943/RE	KALU KALU T	2009/2010

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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