Link 2 tables

[TomasM]

Good morning everyone,

I'm making my internship in a company, and have a lot of work with excel. I'm ''beginner'' so struggling some times.
What I need to do is :

	A​	B​	C​
1​	Company​	Group​	Quantity​
2​	ABC	2	30
3​	DEF	1	20
4​	GHI	2	25

This is the main table, where you enter all the values (company name, group of consumption, and quantity of consumption).

	E​	F​
1​	(Group) 1​	(Group) 2​
2​	20	30
3​		25

This is the second table. If I enter infos in the first table, I need that it appears automatically in the second one.

Sorry for my English, hopefully I was clear. Thank you for your help !

 

[My Aswer Is This]

My question is:
Are we really dealing with a Excel Table?
A Excel Table is a Range on a Excel Sheet.

We may have several tables on the same sheet.

If we are dealing with a Excel Table we need the name of the two Tables.

Are you looking for a Vba solution or a formula solution?

 

[Fluff]

How about

Book1
	A	B	C	D	E	F
1	Company	Group	Quantity		(Group) 1	(Group) 2
2	ABC	2	30		20	30
3	DEF	1	20			25
4	GHI	2	25
HPV

Cell Formulas
Range	Formula
E2:E4	E2	=IFERROR(AGGREGATE(14,6,$C$2:$C$100/($B$2:$B$100=1),ROWS($E$2:$E2)),"")
F2:F4	F2	=IFERROR(AGGREGATE(14,6,$C$2:$C$100/($B$2:$B$100=2),ROWS($F$2:$F2)),"")

 

[Fluff]

Cross posted link 2 tables

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sandy666]

you can try with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Sort = Table.Sort(Source,{{"Group", Order.Ascending}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Sort, {{"Group", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Sort, {{"Group", type text}}, "en-GB")[Group]), "Group", "Quantity"),
    RC = Table.RemoveColumns(Pivot,{"Company"})
in
    RC

Company	Group	Quantity	gaaaap	1	2
ABC	2	30			30
DEF	1	20		20
GHI	2	25			25

or with Prefix

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Sort = Table.Sort(Source,{{"Group", Order.Ascending}}),
    Prefix = Table.TransformColumns(Sort, {{"Group", each "(Group) " & Text.From(_, "en-GB"), type text}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Prefix, {{"Group", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Prefix, {{"Group", type text}}, "en-GB")[Group]), "Group", "Quantity"),
    RC = Table.RemoveColumns(Pivot,{"Company"})
in
    RC

(Group) 1	(Group) 2
	30
20
	25

 

[sandy666]

or even like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Sort = Table.Sort(Source,{{"Group", Order.Ascending}}),
    Prefix = Table.TransformColumns(Sort, {{"Group", each "(Group) " & Text.From(_, "en-GB"), type text}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Prefix, {{"Group", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Prefix, {{"Group", type text}}, "en-GB")[Group]), "Group", "Quantity")
in
    Pivot

Company	Group	Quantity	gaaaap	Company	(Group) 1	(Group) 2
ABC	2	30		ABC		30
DEF	1	20		DEF	20
GHI	2	25		GHI		25