Filtering debtors report

drephy

New Member
Joined
Jul 8, 2014
Messages
12
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.

ABCDE
1idnumbernamesemesterperiodamount
201/5777/REIKERIONWU OSI DFirst Semester2009/201017700
301/5777/REIKERIONWU OSI DFirst Semester2010/201117700
401/5777/REIKERIONWU OSI DSecond Semester2010/20114000
501/5777/REIKERIONWU OSI DFirst Semester2011/201217700
601/5777/REIKERIONWU OSI DSecond Semester2011/20124000
701/5777/REIKERIONWU OSI DFirst Semester2012/201317700
801/5777/REIKERIONWU OSI DSecond Semester2012/20134000
901/5873/UEUNUODE J AUSTINESecond Semester2009/20104000
1001/5873/UEUNUODE J AUSTINEFirst Semester2010/201117700
1101/5873/UEUNUODE J AUSTINESecond Semester2010/20114000
1201/5873/UEUNUODE J AUSTINEFirst Semester2011/201217700
1301/5873/UEUNUODE J AUSTINESecond Semester2011/20124000
1401/5873/UEUNUODE J AUSTINEFirst Semester2012/201317700
1501/5873/UEUNUODE J AUSTINESecond Semester2012/20134000
1601/5938/UEIDOKO I SOLOMONSecond Semester2009/20104000
1701/5938/UEIDOKO I SOLOMONFirst Semester2010/201117700
1801/5938/UEIDOKO I SOLOMONSecond Semester2010/20114000
1901/5938/UEIDOKO I SOLOMONFirst Semester2011/201217700
2001/5938/UEIDOKO I SOLOMONSecond Semester2011/20124000
2101/5938/UEIDOKO I SOLOMONFirst Semester2012/201317700
2201/5938/UEIDOKO I SOLOMONSecond Semester2012/20134000
23
24
25
26
27idnumbernameperiodterminatedreport
2801/5938/UEIDOKO I SOLOMON2011/2012
2901/5777/REIKERIONWU OSI D2009/2010
3001/5873/UEUNUODE J AUSTINE2012/2013
3101/5943/REKALU KALU T2009/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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
Hi,

I have a code here for you, (VBA)


Code:
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?
 
Upvote 0

drephy

New Member
Joined
Jul 8, 2014
Messages
12
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?
 
Upvote 0

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
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
 
Upvote 0

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,191,231
Messages
5,985,412
Members
439,962
Latest member
max_york

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top