Where to Start?

tourless

Board Regular
Joined
Feb 8, 2007
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top