CODEWITHSUNDEEP
google-sheets
andio
andio

Reputation: 1778

google sheet : generate increment number

I use this formula:

=filter(calc!B7:B,calc!P7:P="")

to display the data from Col B of 'calc' sheet where the Col P is null or "". it works fine and it will output single column. If i want to add index number (like incremental ID number) , i normally just manually add any formula that can generate increment number and it works.

But the problem is if the number of the output of that filter() was changed , then i have to readjust the index by extending it or deleting to match the number of the output.

I'm thinking if it's possible to embed the index number (increment number) into the formula itself , for example maybe like this :

=filter({ [my_index_number] , calc!B7:B },calc!P7:P="")

so to generate 2 column, where the left is the custom index_number column. Is it possible ?

Upvotes: 0

Views: 139

Answers (1)

Harun24hr
Harun24hr

Reputation: 36880

Give a try on below formula. Adjust sheet name for other sheet. I have tested the formula to same sheet.

=ArrayFormula(IFERROR(SPLIT(SEQUENCE(COUNTA(FILTER(B7:B,P7:P="")))&"@"&FILTER(B7:B,P7:P=""),"@"),""))

enter image description here

Upvotes: 2

Related Questions