Where to Start?

[tourless]

Hi Folks,

Been a while... Anyway I'm looking at a simple list of all active customers and all their invoices. I've got three columns; Name, Invoice Date, and Invoice Amount. I'm looking for a way to say 'if the customer has invoices prior to 2019 then ignore them and move on to the next customer. Basically creating a list of new customers for 2019.

In a previous version I used an additional column which was a contract start date but that isn't always accurate so I had to scrap it. I've thought about concat'ing a helper column with customer name and 0 for dates prior then 2019 or 1 for dates. Or starting with just a list of customers then looping through that and saying with customer name, if customer has invoice date that does not start with 2019 then move on to the next customer.

I need some help wrapping my head around the logic for this and I'm open to all suggestions.

Thanks all!

 

[My Aswer Is This]

I do not understand what move on means.
You said:

'if the customer has invoices prior to 2019 then ignore them and move on to the next customer. Basically creating a list of new customers for 2019.

If date in column 3 is less then 2019 do nothing
If date in column 3 is 2019 or greater do what?

Create a list of new customers where?
On another sheet?
Copy the entire row to sheet named 2019 ??

I need more specific details.
​

 

[tourless]

I do not understand what move on means.
You said:

'if the customer has invoices prior to 2019 then ignore them and move on to the next customer. Basically creating a list of new customers for 2019.
I need more specific details.
​

Let's see... Sheet1 col A is a list of active customers sorted alphabetically. One name per row, no duplicates. Sheet2 col A is name, col B is invoice date as 20YY-MM-DD, col C is invoice amount. Sheet2 col A will contain duplicates. My thought is to take the name from Sheet1 and compare it to the names on sheet2. Where it matches, check col B and if date does not start with 2019, move on to the next customer from sheet1 and start again. Where the name from sheet1 matches sheet2 and does not have any dates that start prior to 2019, THEN copy that name to sheet3.

I'm not sure I'm looking at this the right way but the end result is to only have a list of non-duplicated customers with no invoices prior to 2019.

 

[tourless]

Well just to bring closure to this I decided it was easier to clean up some of the data and mandate a new procedure than it was to try and reinvent the wheel. Now, using the dedicated start date field I'm able to pull only new customers and report accordingly. Sometime you just have to get it out of your head to realize your answer. Thanks.