google sheets additional custom column in filter

Question

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.

Any ideas to solve this?

score 3 · Accepted Answer · answered Jan 18 '21 at 07:53

3

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)

answered Jan 18 '21 at 07:53

player0

1

A really huuuuuge thanks :) All solutions are handy, depending on the sheet structure. Special thanks for the Query solution. You are awesome. – dessu Jan 18 '21 at 11:52

1 Answers1