Page 1 of 1
ID base on number
Posted: Thu Sep 14, 2023 8:16 am
by sna
Hi there ,
i need your help how to show ID base on number of values in column a.for example if column a listed 1 to 12 it has to show ID from the above of column a and paste it column c
thanks
Re: ID base on number
Posted: Thu Sep 14, 2023 8:21 am
by sna
here is attached
Re: ID base on number
Posted: Thu Sep 14, 2023 8:59 am
by norkaz
...
C2
=IF(ISTEXT(A2:A166),"",LOOKUP(ROW(A2:A166),ROW(A2:A166)/ISTEXT(A2:A166),A2:A166))
Bigcat9
RCA Bangkok Thailand
Re: ID base on number
Posted: Fri Sep 15, 2023 1:20 am
by sna
Can it be done with excel 365 dynamic array?
Re: ID base on number
Posted: Fri Sep 15, 2023 5:13 am
by norkaz
sna wrote: Fri Sep 15, 2023 1:20 am
Can it be done with excel 365 dynamic array?
Absolutely yes!
Bigcat9
RCA Bangkok Thailand
Re: ID base on number
Posted: Sat Sep 16, 2023 8:42 am
by sna
Possible solution with Lookup (char(255),A$10:A10)?
Re: ID base on number
Posted: Sat Sep 16, 2023 9:53 am
by norkaz
sna wrote: Sat Sep 16, 2023 8:42 am
Possible solution with Lookup (char(255),A$10:A10)?
...
It's possible to use the lookup like this.
C2
=IF(ISTEXT(A2),"",LOOKUP(CHAR(255),$A$2:A2))
Copy down
==============
For the Spill Array formula (Excel 365)
C2
=LET(y,A2:A166,IF(ISTEXT(y),"",SCAN(A1,y,LAMBDA(x,y,LOOKUP(CHAR(255),IF(ISNUMBER(y),x,y))))))
Actually, with the SCAN formula like that, the LOOKUP can be skipped in the formula.
C2
=LET(y,A2:A166,IF(ISTEXT(y),"",SCAN(A1,y,LAMBDA(x,y,IF(ISNUMBER(y),x,y)))))
Bigcat9 / Bangkok Thailand
...
Re: ID base on number
Posted: Sun Sep 17, 2023 7:33 am
by sna
thank you so much
