google sheets additional custom column in filter

1
votes

I like to add an additional column in filter formula. This one is working, but not exactly what i need:

=FILTER({detroit!A2:N,"detroit"&row(detroit!A2:A)},detroit!A2:A<>"")

I need only the text "detroit" without the row number, something like this:

=FILTER({detroit!A2:N,"detroit"},detroit!A2:A<>"")

I think i need to use a function for every cell, like row, what gives back nothing?

It also have to be fast to calculate, because of the size of the data.

the_issue

Any ideas to solve this?

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

try this where Z column is empty:

=FILTER({detroit!A2:N, detroit!Z2:Z&"detroit"}, detroit!A2:A<>"")

if you dont have any empty column use:

=FILTER({detroit!A2:N, 
 IFERROR(SEQUENCE(ROWS(detroit!A2:A))/0)&"detroit"}, detroit!A2:A<>"")

also you could do:

=QUERY(detroit!A2:N, 
 "select A,B,C,D,E,F,G,H,I,J,K,L,M,N,'detroit' 
  where A is not null 
  label 'detroit'''", 0)