Query another table in Excel-CodePudding

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.

CodePudding user response：

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))))