I hope to word this correctly or at least so it makes sense. I receive a list of open invoices every day in a Excel file and then merge these together. Basically I insert a date into column A so I know the count of days that the invoices appear in the report. Sometime, very often in fact, the same invoice number can come back into the queue. After merging all of the together, I have this monster list of invoices. I then use a filter so that I have just the invoices in the queue that I need. So column a is the date of the invoice and column B is the invoice #.
I then need to break these into consecutive invoice date ranges, and then have a formula in column C to copy the invoice number and if it's consecutive to the count of the range. For example: I have invoice "1235" that appears 5 different times. When dated with 1/5/17 to 1/6/17 the formula need to add "1235-1", and then the next consecutive or non-sequential date range, the formula would need to add "1235-2". In my example, the next date is 1/11/17 only. Then the invoice appears again on two more days on 1/21/17 thru 1/22/17, and "1235-3" appears in column C.
Let me know if that makes any sense...
I then need to break these into consecutive invoice date ranges, and then have a formula in column C to copy the invoice number and if it's consecutive to the count of the range. For example: I have invoice "1235" that appears 5 different times. When dated with 1/5/17 to 1/6/17 the formula need to add "1235-1", and then the next consecutive or non-sequential date range, the formula would need to add "1235-2". In my example, the next date is 1/11/17 only. Then the invoice appears again on two more days on 1/21/17 thru 1/22/17, and "1235-3" appears in column C.
Let me know if that makes any sense...
