CODEWITHSUNDEEP
excelvba
Cauder
Cauder

Reputation: 2577

Query another table in Excel

I want to pull all the values from a particular column in another table. My goal is to take a handful of different tables and put particular columns from each of them into a single, collated table.

For example, let's say I have tables about different kinds of objects

FRUITS

name         flavor
banana       savory
orange       sweet
peach        sweet

PETS

name         lifespan
dog          long
fish         short
cat          long

Imagine that I now want to make a third table with the name column from fruits and pets.

COLLATED

name      source 
banana    fruits
orange    fruits
peach     fruits
dog       pets
fish      pets
cat       pets

I tried to install the powerpivot add-in to do this, but I wasn't sure how to do it with a Mac. I'd prefer to use any "table connection" features that Excel offers in case that is possible.

Upvotes: 3

Views: 447

Answers (3)

Cristian Buse
Cristian Buse

Reputation: 4558

A combination of ideas from both @Ike and @JosWoolley great answers would be this:

=LET(
n,{"Fruits","Pets","Cars"},
w,(tblFruits[Name],tblPets[Name],tblCars[Name]),
y,COUNTA(w),
s,SEQUENCE(AREAS(w)*y,,0),
q,1+QUOTIENT(s,y),
z,CHOOSE({1,2},IFERROR(INDEX(w,1+MOD(s,y),,q),""),INDEX(n,q)),
FILTER(z,INDEX(z,0,1)<>""))

For a new table, the table name would be added to the n variable and the column/range to the w variable without the need to edit the rest of the formula.

Edit #1

Adding more columns can get tricky using this approach but it can be done. For example having an extra 'Price' column in all tables would require something like this:

=LET(
n,{"Fruits","Pets","Cars"},
w,(tblFruits[Name],tblPets[Name],tblCars[Name]),
p,(tblFruits[Price],tblPets[Price],tblCars[Price]),
y,COUNTA(w),
s,SEQUENCE(AREAS(w)*y,,0),
q,1+QUOTIENT(s,y),
z,CHOOSE({1,2,3},IFERROR(INDEX(w,1+MOD(s,y),,q),""),INDEX(n,q),IFERROR(INDEX(p,1+MOD(s,y),,q),"")),
FILTER(z,INDEX(z,0,1)<>""))

where you have an extra p variable and the CHOOSE is updated to reflect the new values. Of course, you could change the order of the columns in the CHOOSE by either changing the order of the 3 parts or by simply changing the numbers in the {1,2,3} array (e.g. {1,3,2}).

Upvotes: 3

Jos Woolley
Jos Woolley

Reputation: 9052

=LET(w,(tblFruits[name],tblPets[name],tblCars[name]),x,AREAS(w),y,COUNTA(w),z,IFERROR(INDEX(w,1+MOD(SEQUENCE(x*y,,0),y),,1+INT(SEQUENCE(x*y,,0)/y)),""),FILTER(z,z<>""))

Amend the table column names as required, adding in as many as required.

This should work for reasonably small ranges, though x*y could certainly be improved as a lower bound.

Agreed with Ike that a recursive lambda would probably be of help here.

Upvotes: 3

Ike
Ike

Reputation: 13014

I added two tables to a sheet: tblFruits and tblPets.

Then you can put the following formula in any cell on the same sheet or another sheet.

=LET(
     a,CHOOSE({1,2},tblFruits[name],"Fruits"),
     b,CHOOSE({1,2},tblPets[name],"Pets"),
     rowIndex,SEQUENCE(ROWS(a) + ROWS(b)),
     colIndex,SEQUENCE(1,COLUMNS(a)),
     IF(rowIndex<=ROWS(a),
       INDEX(a,rowindex,colIndex),
       INDEX(b,rowindex-ROWS(a),colIndex)
      )
)

The first four rows of the formula are used to retrieve variables that are then used in the final IF-function:

a and b will return "virtual" arrays of each name column plus the "new" column giving the type.

rowIndex returns a single array {1,2,...(number of rows of both tables)}

colIndex returns an array that is build of the number of columns - in this case 2 (name and type)

These variables are used in the IF-formula: Think of it as a For i = 1 to Ubound(rowIndex)-loop.

If the first value from the rowIndex-Array is smaller than the number of rows of tblFruits,

  • then INDEX-result is based on virtual array a,
  • if not the rowindex for b is calculated and INDEX-result is based on virtual array b.

The result is a spill-down array - you can use a filter on it. Just add a header row and add filter.

But you won't be able to create a table based on it. Therefore you will have to use VBA to create the combined data.

This would be the formula with a third table:

=LET(
a,CHOOSE({1,2},tblFruits[Name],"Fruits"),
b,CHOOSE({1,2},tblPets[name],"Pets"),
c,CHOOSE({1,2},tblRooms[name],"Rooms"),
rowIndex,SEQUENCE(ROWS(a)+ROWS(b)+ROWS(c)),
colIndex,SEQUENCE(1,COLUMNS(a)),
IF(rowIndex<=ROWS(a),
         INDEX(a,rowIndex,colIndex),
         IF(rowIndex<=ROWS(a) + ROWS(b),
         INDEX(b,rowIndex-ROWS(a),colIndex),
INDEX(c,rowIndex-(ROWS(a)+ROWS(b)),colIndex))))

Upvotes: 2

Related Questions