Get last number until specific row in excel-CodePudding

I have an excel spreadsheet that looks like this column:

id 
----
1
a
b
c 
2
d
e
f
3
g
h
i
1
c
d
e
2
a
d
f

Due to the fact that the numbers aren't really IDs, but group-IDs, the desired output structure is:

id | group_id
----
a  | 1
b  | 1
c  | 1
d  | 2
e  | 2
f  | 2
g  | 3
h  | 3
i  | 3
c  | 1
d  | 1
e  | 1
a  | 2
d  | 2
f  | 2

It occurred to me that I could manipulate the formula to obtain the last non-empty value in some manner: =LOOKUP(2,1/(B:B<>""),B:B)

I couldn't figure out how to change the internal condition to find the last digit/number value. Note: the original order is essential.

Does anyone have a suggestion?

CodePudding user response：

You could produce the matching numbers for each letter using a spill formula with xlookup on the row numbers like this if you have Excel 365:

=LET(range,A1:A20,
filteredNumbers,FILTER(range,ISNUMBER(range)),
filteredNumberRowNumbers,FILTER(ROW(range),ISNUMBER(range)),
filteredLetterRowNumbers,FILTER(ROW(range),ISTEXT(range)),
XLOOKUP(filteredLetterRowNumbers,filteredNumberRowNumbers,filteredNumbers,,-1))

to get the letters themselves it's just

=FILTER(A1:A20,ISTEXT(A1:A20))

CodePudding user response：

Try to apply SCAN():

Formula in C2:

=FILTER(CHOOSE({1,2},A2:A21,SCAN(0,A2:A21,LAMBDA(a,b,IF(ISNUMBER(b),b,a)))),ISTEXT(A2:A21))

Or, with access to VSTACK() and HSTACK(), to include headers:

=VSTACK({"ID","GROUP_ID"},FILTER(HSTACK(A2:A21,SCAN(0,A2:A21,LAMBDA(a,b,IF(ISNUMBER(b),b,a)))),ISTEXT(A2:A21)))

CodePudding user response：

All the above answers are great. Another option that works for me is:

=LOOKUP(2,1/(ISNUMBER($A$1:A2)),$A$1:A2)

I insert that formula in B2 and use flash fill to reuse each row. Then, I filtered out the rows with letters in the A column.