link two tables in excel

[mattijsstu]

Hi all,

I already looke online for an anwser to my problem and did not find any. So i am not even sure any anwser/solution would exist.

I have two named tables:

[input]

Name parent	age	Name child 1	Name child 2	Name child 3
Eric	45​	Ella	Joe
Jan	38​	Ann
John	32​	Jonas	Matt	Kate

[output]

Parent	Name child
Eric	Ella
Eric	Joe
Jan	Ann
John	Jonas
John	Matt
John	Kate

The excel is used as a template, so the table is empty at the start.
I want the table named 'output' to be automatically filled in according to the table named 'input'. Above i did this now manualy to show the idea. When i would delete the first row from the 'input' table, it should also update the 'output' table and delete the corresponding items (first 2 rows here).

It only creates a new row in the 'output' table for each child name filled in at the 'input' table.

Perhaps someone on this forum has an idea how to solve this problem, currently i'm at a loss.

Thanks!

greetings

Matt

 

[DHP]

Pls check this formula if this is what you are looking for...

 

[alansidman]

unpivot your data using Power Query also called Get and Transform Data and found on the Data Tab of the ribbon.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name parent", "age"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Child"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

Book4
	A	B	C	D	E	F	G	H	I
1	Name parent	age	Name child 1	Name child 2	Name child 3		Name parent	age	Child
2	Eric	45	Ella	Joe			Eric	45	Ella
3	Jan	38	Ann				Eric	45	Joe
4	John	32	Jonas	Matt	Kate		Jan	38	Ann
5							John	32	Jonas
6							John	32	Matt
7							John	32	Kate
Sheet1

 

[mattijsstu]

Hi DHP,

All tables are empty upon start, so there is nothing in A10 for instance. And i think you took the data from the 'output' table and moved it to the 'input', i have the oposite problem. user fills in data in the 'input' table, this then causes the 'output' table to be filled in automatically.

 

[mattijsstu]

unpivot your data using Power Query also called Get and Transform Data and found on the Data Tab of the ribbon.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name parent", "age"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Child"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

Book4
	A	B	C	D	E	F	G	H	I
1	Name parent	age	Name child 1	Name child 2	Name child 3		Name parent	age	Child
2	Eric	45	Ella	Joe			Eric	45	Ella
3	Jan	38	Ann				Eric	45	Joe
4	John	32	Jonas	Matt	Kate		Jan	38	Ann
5							John	32	Jonas
6							John	32	Matt
7							John	32	Kate
Sheet1

Thanks for the reply, i have never used power query. I will have to look into it a little bit .
Does this make a table from scratch? Since i already have this 'output' table in another sheet but just empty (there are also some more columns in the 'output' table than the 'input' table and the other way arround, won't be a problem i assume?)

Thanks!

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[mattijsstu]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Thanks for the links, this is indeed a very powerfull tool for as far as i can see.
Right now, it works but it creats a new table on a new sheet. If i already have a table in my excel file called 'output' and it has the correct headers etc. Can i use this to fill this existing table, not finding a quick way. Right now i could append, but it's going to add new collumns, not fill in the already existing columns in the table.

thx