How to create unique Row ID for duplicate row data?

tinabrown

I have a sheet, in whicha specific column (Invoice), can contain duplicates. I want to use a second column (Invoice ID) to automatically create a unique ID. This will help with additional logic I need in the sheet, as well as using in downstream reports.

In this example, there are 5 instances where Invoice = 1234. I am hoping to use a formula in the Invoice ID column to incrementally create a unique id count. I manually added data into the Invoice ID column to show my anticipated result.

Thanks for any suggestions!

Tina

Christian Graf

Create a helper column. Edit column properties>(Column Type>Auto-Number/System) and (System-generated column> Auto-Number)

This creates an incrementing number for each entry. You could use that as a unique ID.

If you are manipulating that sheet and sometimes delete rows and want to maintain the order each entry was entered into your system, you can create a second helper column and use the formula

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

where row ID is the auto numbered row.

tinabrown

Hi Christian, I might not have explained this well enough. I want the Invoice ID to be in direct relation to the Invoice, not a unique row id.

Basically, I want to see that for invoice 1234, if it is the is the 1st, 2nd, 3rd (or more times) that it has been logged to the sheet.

Likewise, for invoice 5678, it can also have a 1st, 2nd, 3rd... Invoice ID.

Each time a unique new Invoice number is added to the sheet, it should do a similar logic in the Invoice ID column.

Hopefully that helps explain further.

Kelly Moore

Hey @tinabrown

Here's one approach:

You could join your Invoice# and Invoice ID together in one a helper column. This would create a unique ID

In your [Invoice ID] column use a COUNTIFS formula to keep track of the counter numbers, per Invoice number. This assumes you are keeping all completed invoices on this same sheet. You will need instead to use the system generated autonumber [Row ID] if rows are being moved off the sheet.

Assuming all invoices remain on the sheet, in your [Invoice ID] use this formula

=COUNTIFS(Invoice:Invoice, Invoice@row)

Then, in the new helper column, use this formula. I added the hyphen to be able to easily parse the Invoice number back out, if required.

=Invoice@row+"-"+[Invoice ID]@row

The above will keep the counter linked to all the same Invoice numbers.

Will this work for you?

Kelly

Paul H

@tinabrown

Add an system auto created date column, or use if you have

Place this formula in your Invoice ID Column

=COUNTIFS(Invoice:Invoice, Invoice@row, Created:Created, <Created@row)

If you already had a created column this should work, if you didn't and just added a created date it will only work moving forward