Unique combination (question reformatted)

[mendi]

I need a way to find the distinct Country & Entity combinations on tab [Data] and have this information fed to tab [Summary by Country & Entity].

Below is a small portion of tab [Data].
A B C D E F G H I
1 Country Total United States United States United States China Denmark Canada China
2 Entity ABC Corp ABC Corp DEF Corp ABC Corp DEF Corp GHI Corp GHI Corp
3 Product A B A A B C A
4 Amount1 $335,000 $50,000 $60,000 $100,000 $35,000 $65,000 $15,000 $10,000
5 Amount2 $420,000 $90,000 $85,000 $20,000 $80,000 $85,000 $40,000 $20,000
6 Amount3 $485,000 $75,000 $45,000 $75,000 $95,000 $70,000 $95,000 $30,000




I have summarized tab [Data] by ‘Country’ as follows:
A B C D E F G H I
1 Country Total United States China Denmark Canada
2 Amount1 $335,000 $210,000 $45,000 $65,000 $15,000
3 Amount2 $420,000 $195,000 $100,000 $85,000 $40,000
4 Amount3 $485,000 $195,000 $125,000 $70,000 $95,000


Row ‘Country’ is populated via the following (beginning in cell C1):
=INDEX(Data!$B$1:$I$1,SMALL(IF(MATCH(Data!$B$1:$I$1,Data!$B$1:$I$1,0)=COLUMN(Data!$B$1:$I$1)-MIN(COLUMN(Data!$B$1:$I$1))+1,COLUMN(Data!$B$1:$I$1)-MIN(COLUMN(Data!$B$1:$I$1))+1),COLUMN(B1)))

The Amount rows are populated via the following (beginning in cell C2):
=SUMIFS(INDIRECT("'Data'!"&MATCH($A2,Data!$A:$A,0)&":"&MATCH($A2,Data!$A:$A,0)),Data!$1:$1,'Summary by Country'!C$1)


I have also summarized tab [Data] by ‘Entity’ as follows:
A B C D E F G H I
1 Entity Total ABC Corp DEF Corp GHI Corp
2 Amount1 $335,000 $145,000 $165,000 $25,000
3 Amount2 $420,000 $255,000 $105,000 $60,000
4 Amount3 $485,000 $215,000 $145,000 $125,000


Now, I need to be able to summarize by unique Country and Entity combinations that are reflected on the data tab. I can’t seem to figure out how to populate my Country (row 1) and Entity (row 2) in this table. I am expecting my output to look as follows:
(I have already adjusted my Amounts to match on Country and Entity, so I’m just looking for how to populate my Country and Entity fields)
A B C D E F G H I
1 Country Total United States United States China China Denmark Canada
2 Entity ABC Corp DEF Corp ABC Corp GHI Corp DEF Corp GHI Corp
3 Amount1 $335,000 $110,000 $100,000 $35,000 $10,000 $65,000 $15,000
4 Amount2 $420,000 $175,000 $20,000 $80,000 $20,000 $85,000 $40,000
5 Amount 3 $485,000 $120,000 $75,000 $95,000 $30,000 $70,000 $95,000


Any suggestions are greatly appreciated.

 

[juankalsa]

There is no way to use Pivot?

 

[mendi]

There is no way to use Pivot?

since the pivot table size can change depending on the customer that the workbook is for (ie, amount of columns on data tab), I wasn't able to auto populate the country & entity summary tab

 

[mendi]

(Tables reflected clearly below - Thanks again for any suggestions)

I need a way to find the distinct Country & Entity combinations on tab [Data] and have this information fed to tab [Summary by Country & Entity].

Below is a small portion of tab [Data].

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Country	Totals	United States	United States	United States	China	Denmark	Canada	China
2	Entity	-	ABC Corp	ABC Corp	DEF Corp	ABC Corp	DEF Corp	GHI Corp	GHI Corp
3	Product	-	A	B	A	A	B	C	A
4	Amount1	$335,000	$50,000	$60,000	$100,000	$35,000	$65,000	$15,000	$10,000
5	Amount2	$420,000	$90,000	$85,000	$20,000	$80,000	$85,000	$40,000	$20,000
6	Amount3	$485,000	$75,000	$45,000	$75,000	$95,000	$70,000	$95,000	$30,000
Data

I have summarized tab [Data] by ‘Country’ as follows:

Excel 2010
	A	B	C	D	E	F	G
1	Country	Totals	United States	China	Denmark	Canada	#NUM!
2	Amount1	$335,000	$210,000	$45,000	$65,000	$15,000
3	Amount2	$420,000	$195,000	$100,000	$85,000	$40,000
4	Amount3	$485,000	$195,000	$125,000	$70,000	$95,000
Summary by Country

Row ‘Country’ is populated via the following (beginning in cell C1):
=INDEX(Data!$B$1:$I$1,SMALL(IF(MATCH(Data!$B$1:$I$1,Data!$B$1:$I$1,0)=COLUMN(Data!$B$1:$I$1)-MIN(COLUMN(Data!$B$1:$I$1))+1,COLUMN(Data!$B$1:$I$1)-MIN(COLUMN(Data!$B$1:$I$1))+1),COLUMN(B1)))

The Amount rows are populated via the following (beginning in cell C2):
=SUMIFS(INDIRECT("'Data'!"&MATCH($A2,Data!$A:$A,0)&":"&MATCH($A2,Data!$A:$A,0)),Data!$1:$1,'Summary by Country'!C$1)


I have also summarized tab [Data] by ‘Entity’ as follows:

Excel 2010
	A	B	C	D	E
1	Country	Totals	ABC Corp	DEF Corp	GHI Corp
2	Amount1	$335,000	$145,000	$165,000	$25,000
3	Amount2	$420,000	$255,000	$105,000	$60,000
4	Amount3	$485,000	$215,000	$145,000	$125,000
Summary by Entity

Now, I need to be able to summarize by unique Country and Entity combinations that are reflected on the data tab. I can’t seem to figure out how to populate my Country (row 1) and Entity (row 2) in this table. I am expecting my output to look as follows:
(I have already adjusted my Amounts to match on Country and Entity, so I’m just looking for how to populate my Country and Entity fields)

Excel 2010
	A	B	C	D	E	F	G	H
1	Country	Totals	United States	United States	China	China	Denmark	Canada
2	Entity	-	ABC Corp	DEF Corp	ABC Corp	GHI Corp	DEF Corp	GHI Corp
3	Amount1	$335,000	$110,000	$100,000	$35,000	$10,000	$65,000	$15,000
4	Amount2	$420,000	$175,000	$20,000	$80,000	$20,000	$85,000	$40,000
5	Amount3	$485,000	$120,000	$75,000	$95,000	$30,000	$70,000	$95,000
Summary by Country & Entity

Any suggestions are greatly appreciated.

 

[mikerickson]

In C3, you could put
=SUMPRODUCT(Data!$C$4:$I$6*(Data!$C$1:$I$1=C$1)*(Data!$C$2:$I$2=C$2)*(Data!$A$4:$A$6=$A3))
and drag it down and right as needed.



(that evaluates to this explicit form:
=SUMPRODUCT(Data!$C$4:$I$6*(Data!$C$1:$I$1="United States")*(Data!$C$2:$I$2="ABC Corp")*(Data!$A$4:$A$6="Amount1"))

 

[mendi]

In C3, you could put
=SUMPRODUCT(Data!$C$4:$I$6*(Data!$C$1:$I$1=C$1)*(Data!$C$2:$I$2=C$2)*(Data!$A$4:$A$6=$A3))
and drag it down and right as needed.



(that evaluates to this explicit form:
=SUMPRODUCT(Data!$C$4:$I$6*(Data!$C$1:$I$1="United States")*(Data!$C$2:$I$2="ABC Corp")*(Data!$A$4:$A$6="Amount1"))

Thanks for your reply mikerickson, however I'm not having an issue with calculating the 'Amounts'. What I can't seem to figure out is how to populate the Country and Entity on tab [Summary by Country & Entity] - the filed highlighted in yellow.

 

[mendi]

I figured out a work around...thanks juankalsa and mikerickson for your replies