1

I am cataloging items in a spreadsheet using Google Sheets. Every item needs a unique ID, which is a sequential code formatted like ABxxxxxx, where AB000000 < ABxxxxxx < AB999999, starting from AB000001, AB000002, AB000003, etc., and counting up to AB999999.

Some items in my sheet already have unique IDs, but many do not. I am trying to find a formula that generates an ID, and checks if the ID is already in a specific column.

If the new ID is not already in the column, then that ID becomes the new, unique ID for the item; otherwise, if the ID is already in the column, generate a new sequential ID, and check again to see if that one is already in the column.

I believe that I can see if the ID is already in the column by using the '''IF''' and '''COUNTIF''' formulas, and I can check to see if a value is unique using the '''UNIQUE''' formula.

However, I cannot seem to find a formula that will generate a new, unique ID for every item in a column which doesn't already have an ID. Attached is a screenshot which hopefully can illustrate what I want my formula to do. Is this even possible in Sheets?

enter image description here

Thank you very much for your time in reading this. I searched through several pages of Google results, and could not find a formula like the one I need. Any help is much appreciated. Thank you.

Improve this question

1 Answer 1

Reset to default
3

You can create the sequential IDs with this formula:

=ArrayFormula("AB"&TEXT(SEQUENCE(ROWS(A2:A)),"000000"))

enter image description here

Then, you can set a filtered range, excluding those values already existing in your range:

=LET(ids,ArrayFormula("AB"&TEXT(SEQUENCE(ROWS(A2:A)),"000000")),FILTER(ids,BYROW(ids,LAMBDA(each,ISNA(XMATCH(each,A2:A))))))

enter image description here

And, finally you can use BYROW to state that if A column has a value, use that value and if it hasn't it scans with INDEX through that filtered range (ROW(each)-COUNTA(A2:each)-1) is used to calculate how much does it need to progress)

=LET(ids,ArrayFormula("AB"&TEXT(SEQUENCE(ROWS(A2:A)),"000000")),filt,FILTER(ids,BYROW(ids,LAMBDA(each,ISNA(XMATCH(each,A2:A))))),
 BYROW(A2:A,LAMBDA(each,IF(each<>"",each,INDEX(filt,ROW(each)-COUNTA(A2:each)-1)))))

enter image description here

Use only the last column, it was just explaining my thinking! Possibly there are other ways to do it with SORT and MATCH

Improve this answer
3
  • 1
    Great solution. Suggestion to simplify 'FILTER(ids, BYROW(ids, LAMBDA(each, ISNA(XMATCH(each, A2:A)))))' down to 'FILTER(ids, ISNA(MATCH(ids, A2:A, 0)))'
    – Blind Spots ♦
    Apr 11 at 17:49
  • 1
    Thank you very much, Martín, that solved my problem!
    – Ava O.
    Apr 11 at 22:04
  • I'm glad it was useful!! (and yes, @BlindSpots surely that works! I was doing something else and kept BYROW)
    – Martín
    Apr 12 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.