Populate Data With Criteria

[kumara_faith]

Hi,

I have the following :

Book2
	C	D	E	F	G	H	I
2	Table 1				Table 2
3	Name	Branch	Sales		Name	Branch	Sales
4	Staff1	New York	100		Staff1	New York	100
5	Staff2	Texas	100		Staff2	Texas	255
6	Staff2	Virginia	80		Staff3	Washinton	100
7	Staff2	Iowa	75		Staff4	Hawaii	100
8	Staff3	Washinton	100		Staff5	Texas	450
9	Staff4	Hawaii	100		Staff6	Iowa	100
10	Staff5	New York	100		Staff7	Washinton	100
11	Staff5	Texas	200		Staff8	Hawaii	100
12	Staff5	Virginia	150		Staff9	Hawaii	100
13	Staff6	Iowa	100
14	Staff7	Washinton	100
15	Staff8	Hawaii	100
16	Staff9	Hawaii	100
Sheet1

In Table 1, I have the raw data. In Table 2, I am trying to summarize the sales by staff. If the branch is repeated for the same staff, the sales should be summed to the branch which has the highest sales. Example is Staff 2 which has sales in Iowa, Texas and Virginia. Since the highest sales was in Texas, all the sales will be summed in Texas. Example of the correct result is in Table 2. Is there a formula that could be used to achieve this ? Appreciate all the help.

 

[Phuoc]

Try this

Book1
	C	D	E	F	G	H	I
2	Table 1				Table 2
3	Name	Branch	Sales		Name	Branch	Sales
4	Staff1	New York	100		Staff1	New York	100
5	Staff2	Texas	100		Staff2	Texas	255
6	Staff2	Virginia	80		Staff3	Washinton	100
7	Staff2	Iowa	75		Staff4	Hawaii	100
8	Staff3	Washinton	100		Staff5	Texas	450
9	Staff4	Hawaii	100		Staff6	Iowa	100
10	Staff5	New York	100		Staff7	Washinton	100
11	Staff5	Texas	200		Staff8	Hawaii	100
12	Staff5	Virginia	150		Staff9	Hawaii	100
13	Staff6	Iowa	100
14	Staff7	Washinton	100
15	Staff8	Hawaii	100
16	Staff9	Hawaii	100
Sheet1

Cell Formulas
Range	Formula
H4:H12	H4	=INDEX($D$4:$D$16,MATCH(AGGREGATE(14,6,$E$4:$E$16/($C$4:$C$16=G4),1),$E$4:$E$16/($C$4:$C$16=G4),0))
I4:I12	I4	=SUMIF($C$4:$C$16,G4,$E$4:$E$16)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[awoohaw]

Here is a dfferent take on it.

Mr Excel Questions4.xlsm
	A	B	C	D	E	F	G	H
1	Table 1
2	Name	Branch	Sales
3	Staff1	New York	100		Staff1	New York	100
4	Staff2	Texas	100		Staff2	Texas	255
5	Staff2	Virginia	80		Staff3	Washinton	100
6	Staff2	Iowa	75		Staff4	Hawaii	100
7	Staff3	Washinton	100		Staff5	Texas	450
8	Staff4	Hawaii	100		Staff6	Iowa	100
9	Staff5	New York	100		Staff7	Washinton	100
10	Staff5	Texas	200		Staff8	Hawaii	100
11	Staff5	Virginia	150		Staff9	Hawaii	100
12	Staff6	Iowa	100
13	Staff7	Washinton	100
14	Staff8	Hawaii	100
15	Staff9	Hawaii	100
Sheet2

Cell Formulas
Range	Formula
E3:E11	E3	=UNIQUE(A3:A15)
F3:F11	F3	=XLOOKUP(MAX(FILTER($C$2:$C$15,$A$2:$A$15=E3)),FILTER($C$2:$C$15,$A$2:$A$15=E3),FILTER($B$2:$B$15,$A$2:$A$15=E3))
G3:G11	G3	=SUM(FILTER(C2:C15,A2:A15=E3))
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Table 1
2	Name	Branch	Sales		Name	Branch	Sales
3	Staff1	New York	100		Staff1	New York	100
4	Staff2	Texas	100		Staff2	Texas	255
5	Staff2	Virginia	80		Staff3	Washinton	100
6	Staff2	Iowa	75		Staff4	Hawaii	100
7	Staff3	Washinton	100		Staff5	Texas	450
8	Staff4	Hawaii	100		Staff6	Iowa	100
9	Staff5	New York	100		Staff7	Washinton	100
10	Staff5	Texas	200		Staff8	Hawaii	100
11	Staff5	Virginia	150		Staff9	Hawaii	100
12	Staff6	Iowa	100
13	Staff7	Washinton	100
14	Staff8	Hawaii	100
15	Staff9	Hawaii	100
16
Data

Cell Formulas
Range	Formula
E3:E11	E3	=UNIQUE(A3:A15)
F3:F11	F3	=BYROW(E3#,LAMBDA(br,INDEX(SORT(FILTER(B3:C15,A3:A15=br),2,-1),1,1)))
G3:G11	G3	=SUMIFS(C:C,A:A,E3#)
Dynamic array formulas.

 

[kumara_faith]

Hi Fluff,

Thank you for the solution and I appreciate it. Have a great day ahead.

Thank you to awoohaw and Phuoc for your solution.

 

[Fluff]

Glad we could help & thanks for the feedback.