Creating a specific list from large data set

Good evening all!

We collect loan payments for 36 months from customers.


Column A lists 1000+ customers.

Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.

Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.

Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.

This repeats for all 36 payments.


New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.


We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.

Can anyone help with this please?!! Thanks a bunch!!

I guess I'm not following your suggestion.

On row 1, Customer 0001 pays $100 on 1/1/2014.

Are you saying that Customer 0001's next payment would be listed on row 1233 as paying $100 on 2/3/2014?

How many rows deep does Excel allow?? After 10 years, we'd be down 120,000 rows ....

OK thanks for verifying. We'll mull that one over. One issue that creates is that we have no visibility on the customer's pay patterns ... currently we can see a given customer's payments going across on a set row....

We see an issue with the long list.

Currently, our "master" spreadsheet pulls data from each individual customer file. We list the customer once on the "master" and it continues to "auto-fill" as payments are recorded in the customer's file.

With the "long list," the customer would need to be entered each time along with their payment date and amount. We'd be duplicating efforts ....

We were thinking there must be a way to search a date range (say 1/1/14 - 1/12/14) and if a payment was made, move to the next customer ... otherwise, add the customer to a list.

OK, we attached a simplified cut up version of our spreadsheet. Maybe this will help to understand our need.

Again, we're looking for a way (without Code) to create a list of Customers each month that have not made a payment in a specific range of dates.

Using the spreadsheet example we attached, let's assume it's DEC 12, 2013 ... we want a list of the Customer Names that have not made a payment between DEC 1 - DEC 12. The answer should be Customer 2 and Customer 3.

Thank you!

I'm so sorry!

Unlocked file is attached.

Thank you for helping!!

Let me know if this works for you!

That is an awesome set up! We played with it for a short period of time and it looks like it will accomplish what we're looking for.

Thank you very much for your time and efforts!!!!

Your solution is simple ... wow ... thank you for your help. We're going to use your solution and the one from Ron above to come up with our new system.

You all are awesome! Can't thank you enough!!!

Xx7,

One question please on your formula for selecting last payment. Is there significance with the 35000 and 45000? Or did you simply choose a large spread on the two ends of the date search? Just curious. We're still learning Excel!!

Thx!

Originally Posted by InterstateRentals

Xx7,

One question please on your formula for selecting last payment. Is there significance with the 35000 and 45000? Or did you simply choose a large spread on the two ends of the date search? Just curious. We're still learning Excel!!

Thx!

Those are dates that correspond to "Oct 28/1995" to "Mar 15/2023"

Since I'm using the Max formula.. it is looking for the LARGEST value in that row. I'm assuming you won't have any monthly payments as large as $35,000 each?? If you do, then you may have to use a different formula, because it could pick up the payment dollar amount as opposed to the date.


For example type 40000 into a cell and convert to date format. This equals July 6/2009. Dates are really numeric values in cells.