ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Finding state that contains employees biggest sale (https://www.excelbanter.com/excel-discussion-misc-queries/151536-finding-state-contains-employees-biggest-sale.html)

Richard

Finding state that contains employees biggest sale
 
I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.

--
Richard

Toppers

Finding state that contains employees biggest sale
 
one way:

=INDEX($C$1:$C$4,MATCH(MAX(($A$1:$A$4="Joe")*($B$1 :$B$4)),$B$1:$B$4,0))

Enter with Ctrl+Shift+Enter

Substitute "Joe" with cell containing name

"Richard" wrote:

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.

--
Richard


Toppers

Finding state that contains employees biggest sale
 
..... no it doesn't!!!

"Toppers" wrote:

one way:

=INDEX($C$1:$C$4,MATCH(MAX(($A$1:$A$4="Joe")*($B$1 :$B$4)),$B$1:$B$4,0))

Enter with Ctrl+Shift+Enter

Substitute "Joe" with cell containing name

"Richard" wrote:

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.

--
Richard


Jim Thomlinson

Finding state that contains employees biggest sale
 
How about a pivot table. Place your cursor in the data set and select Data -
Pivot Table (Brings up the wizard. You can follow the wizard or more likely
just select Finish)

Place the names in the left column and then the state next to it. In the
center section add the amounts. Now right click on the State field and select
Field Settings - Advanced. Change the order to desending and Select Show Top
(1).

You may want to get rid of Subtotals and a few other things just to clean it
up a tad...
--
HTH...

Jim Thomlinson


"Richard" wrote:

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.

--
Richard


T. Valko

Finding state that contains employees biggest sale
 
Array entered:

=INDEX(C1:C4,MATCH(1,(A1:A4="Joe")*(B1:B4=MAX(IF(A 1:A4="Joe",B1:B4))),0))

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
.... no it doesn't!!!

"Toppers" wrote:

one way:

=INDEX($C$1:$C$4,MATCH(MAX(($A$1:$A$4="Joe")*($B$1 :$B$4)),$B$1:$B$4,0))

Enter with Ctrl+Shift+Enter

Substitute "Joe" with cell containing name

"Richard" wrote:

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the
largest
sale. For example, for Joe this would be VA since his largest sale of
20.00
was to VA.

--
Richard




Toppers

Finding state that contains employees biggest sale
 
.. what I intended ...

=INDEX($C$2:$C$5,MATCH(MAX(($A$2:$A$5="Joe")*($B$2 :$B$5)),($A$2:$A$5="Joe")*($B$2:$B$5),0))

entered with Ctrl+Shift+Enter

"Toppers" wrote:

.... no it doesn't!!!

"Toppers" wrote:

one way:

=INDEX($C$1:$C$4,MATCH(MAX(($A$1:$A$4="Joe")*($B$1 :$B$4)),$B$1:$B$4,0))

Enter with Ctrl+Shift+Enter

Substitute "Joe" with cell containing name

"Richard" wrote:

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.

--
Richard


Richard

Finding state that contains employees biggest sale
 
Works great. I'm just learning the power of the match, index combinations.
--
Richard


"Toppers" wrote:

. what I intended ...

=INDEX($C$2:$C$5,MATCH(MAX(($A$2:$A$5="Joe")*($B$2 :$B$5)),($A$2:$A$5="Joe")*($B$2:$B$5),0))

entered with Ctrl+Shift+Enter

"Toppers" wrote:

.... no it doesn't!!!

"Toppers" wrote:

one way:

=INDEX($C$1:$C$4,MATCH(MAX(($A$1:$A$4="Joe")*($B$1 :$B$4)),$B$1:$B$4,0))

Enter with Ctrl+Shift+Enter

Substitute "Joe" with cell containing name

"Richard" wrote:

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.

--
Richard


Richard

Finding state that contains employees biggest sale
 
I have pivot close to working. What do I enter as field under Select Show Top?
It seems to only offer one value, Sales - but that doesn't work.
--
Richard


"Jim Thomlinson" wrote:

How about a pivot table. Place your cursor in the data set and select Data -
Pivot Table (Brings up the wizard. You can follow the wizard or more likely
just select Finish)

Place the names in the left column and then the state next to it. In the
center section add the amounts. Now right click on the State field and select
Field Settings - Advanced. Change the order to desending and Select Show Top
(1).

You may want to get rid of Subtotals and a few other things just to clean it
up a tad...
--
HTH...

Jim Thomlinson


"Richard" wrote:

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.

--
Richard



All times are GMT +1. The time now is 04:44 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com