Filtering Data and then re-filtering that data - help/suggestions please

[miller11963]

I am trying to find a solution to storing and accessing data in an organized manor.

What I am trying todo is be able to record payment records for clients who pay for a weekly or monthly service. I would like to be able to easily see all the payments for that particular client (possible by filtering I believe, correct me if there is a better way). The next step I want is on another sheet to have that client with the date of last payment they made aka search all of the payment records for that client and then return that clients last payment date. Do I need to write a macro or something like that? I really have no idea and would take any suggestions on how to build the workbook as I am starting from scratch. I have already built a vlookup to pull in all the member info off a generated member/client ID.

Thank you in advance.

[FDibbins]

It doesnt sound like you need VBA for this, just a formula and/or maybe a pivot table. but in order to help you better please attach a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[bentleybob]

Maybe try this approach. It assumes you add new records down such that, for any customer, the entry on the highest numbered row is the latest date.

[miller11963]

bentleybob, first of all you nailed the idea of what I'm trying todo. I'm going to ask you to explain the formula to me because I don't fully understand it. I have used both the vlookup and countif functions but not sure how it is producing the result I'm looking for. What does the "" &"-"&COUNTIF "" function added to the Vlookup do and how does it do that?

Is there a way todo this with out having a column with unique customer codes? If not suggestions as to how to make an Auto generated code. Right now I'm using an auto generated client ID that is the first 3 of their last name and last four of their phone number for a XXX#### ID. Is there a way to auto generate a "-###" three digit number code added to the end of their ID, XXX####-### that is sequential based on that last entry for that ID??

Thank's again.

[bentleybob]

Your idea of a unique customer number is similar to the approach I used with the CustomerCode and the COUNTIF. The Customer Code simply referenced the customer name, a separator (I used the "-"), and a sequence number. Then, the lookup simply finds the customer name plus the separator and the total number of records that customer has in the database which, according to my assumption, is also the number for the latest entry for that customer which is what you're looking for.

So just take your auto generated client ID and add a separator (not needed, but makes it easier to visually separate the customer name or ID from the sequential code) and then add the sequence using the COUNTIF as I did in the CustomerCode column.

Attach a file with an example if you need further help as was suggested by herr moderator.

[miller11963]

I have used your advice and started building the sheet I want to use. Unfortunately this leads to additional problems. So lets start with the first question I asked. I believe that solved the problem of differentiating records and allow me to find the most recent of those records. Now here comes the next problem I want to be able to find the next record that is a weekly or monthly payment aka a payment where they would have a next due date. I tried using a if than statement but I realized the way I was doing it was limited to only going back one record not to the next record of the type I wanted. So if I had two "Drop In" or "10 Session" payment lengths in a row it would still return an incorrect payment type.

Also while you are looking at my sheet any suggestions on how to do this as you can see what I'm trying to do. Also for Member ID any suggestions to make it less likely to have any duplicates because obviously for this whole workbook to work the Member ID has to be unique.

Thanks again for any advice or help.





Membership Database & Log copy.xlsx

[bentleybob]

If your question is how to further refine the lookup to essentially filter for a specific payment length (e.g., "drop in" or "10 sessions"), or maybe filter OUT a specific payment length, then you may have to use SUMPRODUCT. I'm a bit confused as to what the criteria are, since "Status for Monthly or Weekly Billing" doesn't exactly match with the payment lengths of 1 week, 2 weeks, other, etc.

So, using Benjamin Miller as an example, what would you want to show up as the "last entry" in this section given his five different payment types, and what is your logic for deciding what to report when there might be more than one "correct" answer?

Not trying to be difficult, just trying to grok what you're looking for.

[miller11963]

Thank you for the question. I want to filter the data pulled up Payment Status sheet. "Status for Weekly and Monthly Billing" should really refer to anything that is re-occurring, so weekly, monthly, and yearly. The payments that wouldn't be included would be Drop In, 10 Sessions, and Other. I want to find a result with a payment date that based on the payment length will spit out the next payment due date on the Payment Status sheet.

So for the two different entries on the Payment Status sheet for Benjamin Miller the Last payment would be all the info for mil7890-5 payment ID, and the result for the "Status for Weekly and Monthly Billing" would be all the info for mil7890-3 payment ID.

Did I answer what I was looking for?

Did I answer your question?

[bentleybob]

Well, I had to add several additional helper columns and the formula in Payment Status!F8 is rather long, but everything works. I do see that there are circular references but I don't think I created them.

Does this do the trick?

Just to be clear, the formulas I added in row 8 of the Payment Status tab appear to work. I didn't change anything in row 10 or rows 14+.