CODEWITHSUNDEEP
google-sheetsspreadsheet
Moishe Mackson
Moishe Mackson

Reputation: 5

Add a prefix column to an array

So I'm creating an export worksheet on my spreadsheet as I would like a consistent way to VLOOKUP using an IMPORTRANGE from this sheet. I have 3 datasets and I would like to add a prefix column to the export worksheet so I'm able VLOOKUP which dataset the data comes from. My initial though was to use this formula ={"Dataset 1", 'Dataset 1'!$A$2:$C6; "Dataset 2", 'Dataset 2'!$A$2:$C11; "Dataset 3", 'Dataset 3'!$A$2:$C16} however I'm unable to use this because the strings I am adding aren't rows of data, I understand why I can't use this formula however I'm unsure how to accomplish what I'm trying to do here.

Here is an example spreadsheet with my desired outcome. Thanks for any help!

Upvotes: 0

Views: 464

Answers (1)

Aresvik
Aresvik

Reputation: 4630

Try:

=arrayformula(query(
{
iferror(row('Dataset 1'!A:A)/0,"Dataset 1"),'Dataset 1'!A:C;
iferror(row('Dataset 2'!A2:A)/0,"Dataset 2"),'Dataset 2'!A2:C;
iferror(row('Dataset 3'!A2:A)/0,"Dataset 3"),'Dataset 3'!A2:C
},
"where Col2 is not null label Col1 'Dataset:'",1))

enter image description here

Alternatively, use FILTER:

=ARRAYFORMULA({
    "Dataset:", "Name:", "ID:", "Apples:";
    IFERROR(FILTER('Dataset 1'!A2:A, 'Dataset 1'!A2:A <> "") / 0, "Dataset 1"), FILTER('Dataset 1'!A2:C, 'Dataset 1'!A2:A <> "");
    IFERROR(FILTER('Dataset 2'!A2:A, 'Dataset 2'!A2:A <> "") / 0, "Dataset 2"), FILTER('Dataset 2'!A2:C, 'Dataset 2'!A2:A <> "");
    IFERROR(FILTER('Dataset 3'!A2:A, 'Dataset 3'!A2:A <> "") / 0, "Dataset 3"), FILTER('Dataset 3'!A2:C, 'Dataset 3'!A2:A <> "")
})

If you want the blank rows to appear, use:

=ARRAYFORMULA({
    "Dataset:", "Name:", "ID:", "Apples:";
    IFERROR(row('Dataset 1'!A2:A) / 0, "Dataset 1"), 'Dataset 1'!A2:C;
    IFERROR(row('Dataset 2'!A2:A) / 0, "Dataset 2"), 'Dataset 2'!A2:C;
    IFERROR(row('Dataset 3'!A2:A) / 0, "Dataset 3"), 'Dataset 3'!A2:C
})

Upvotes: 1

Related Questions