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 arraya
, - if not the rowindex for
b
is calculated andINDEX
-result is based on virtual arrayb
.
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))))