Combine first column of n sheets to one colume

user_7682615 0
Viewed 32

So in one spreadsheet file we have n sheets.

Sheet1
Names
a
b
Sheet2
Names
b
c
d

and so on.. assume n sheets with n values in the first column

Then there's one super sheet. Super sheet

Supersheet Supersheet Supersheet Supersheet
Sheet1 Sheet2 Sheet3 ...
Names Count
a 1
b 2
c 1
d 1

Assume A1:1 is a list of all of the names of the sheets we want to fetch names from. We want the names pasted into A3:A.

My first thought was to do =unique(filter({INDIRECT(A1&"!A3:A");INDIRECT(B1&"!A3:A");INDIRECT(C1&"!A3:A")},{INDIRECT(A1&"!A3:A");INDIRECT(B1&"!A3:A");INDIRECT(C1&"!A3:A")}<>"")) and expand, but that doesn't take care of N sheets.

So what I thought I could do was =ARRAYFORMULA(INDIRECT(A2:C2&"!A2:A3")) but that only takes care of sheet1. INDIRECT doesn't work with arrayformula

So what can I do?

google-sheets
1 Answers

you can either use some script or put your sheets in array {} and pre-program it:

=QUERY({INDIRECT(Sheet1!A1&"!A3:A"); 
        INDIRECT(Sheet2!A1&"!A3:A")}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''")

enter image description here

since this is one column future sheets can be added this way:

=QUERY({INDIRECT(Sheet1!A1&"!A3:A"); 
        INDIRECT(Sheet2!A1&"!A3:A");
IFERROR(INDIRECT(Sheet3!A1&"!A3:A"));
IFERROR(INDIRECT(Sheet4!A1&"!A3:A"))}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''")

0

IMPORTRANGE would be:

=QUERY({IMPORTRANGE("id", Sheet1!A1&"!A3:A"); 
        IMPORTRANGE("id", Sheet2!A1&"!A3:A");
IFERROR(IMPORTRANGE("id", Sheet3!A1&"!A3:A"));
IFERROR(IMPORTRANGE("id", Sheet4!A1&"!A3:A"))}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''")

enter image description here

ofc, every importrange formula needs to be authorized separately

user_56326290
Related