lambdagoogle-sheets-formulauniquevlookupcountif

Changing value in one column with sequential information when cell value changes in another column


Cannot find information on web, so asking for help. All solutions I find are on python. I need solution on google sheets.

I have sequential information, which needs to be updated based on values in another column.

sample:

dog 1
dog 1
dog 1
cat 2
cat 2
cat 2
cat 2
cat 2
    3
    3
    4
    4
    4
    5
    5
    6
    7
    7
    7
    7
    7
    7
    8

result I need:

dog 1
dog 1
dog 1
cat 2
cat 2
cat 2
cat 2
cat 2
dog 3
dog 3
cat 4
cat 4
cat 4
dog 5
dog 5
cat 6
dog 7
cat 7
dog 7
dog 7
dog 7
dog 7
cat 8

Vlookup is not a solution, because I have very long list. Please recommend solution without using "ISODD" function. Because there would be cases with text values.


Solution

  • use VLOOKUP:

    =INDEX(IFNA(VLOOKUP(B:B, {TOCOL(UNIQUE(B:B), 1), ARRAY_CONSTRAIN(TOCOL(
     REDUCE(, SEQUENCE(ROUNDUP(COUNTUNIQUE(B:B)/COUNTA(A:A))), 
     LAMBDA(x, y, {A:A; x})), 1), COUNTUNIQUE(B:B), 1)}, 2, )))
    

    enter image description here

    enter image description here