SOLVED

IF... THEN... AND! Need help with the formula!

New Contributor

On my payment spreadsheet, I have a column for invoice numbers and a column for the vendor numbers.

If the vendor does not provide an invoice, then the invoice number is always the date of the check run.

 

IF the vendor number repeats (more than one payment for this vendor), and the invoice number is the date, THEN a -1 needs to be added to the end of the date so that it does not appear to be a duplicate invoice number.

 

For each time the vendor number repeats, the -# at the end of the date increases, i.e., 11042021-1, 11042021-2, etc.  Currently, I am manually entering the dash numbers.

Anyone know of a function to automatically add the -1, -2, etc at the end of the date?

Thank you,

Babs

3 Replies
best response confirmed by Babs_Adams (New Contributor)
Solution

@Babs_Adams 

In A4:

=TEXT(C4,"mmddyyyy")&TEXT(COUNTIFS($E$3:$E3,$E4,$C$3:$C3,$C4),"-0;;")

Fill down.

You are AMAZING! I can't tell you how much I appreciate you taking the time and responding and sharing your expertise. Thank you! If I need you again, can I reach out to you?

@Babs_Adams 

Sure, feel free to do so!