Match, Index, Vlookup, Large....Help Please

[hothotmail125]

I have been working on this one for an hour or so. Any help would be
greatly appreciated.

Unfortunately, I am not good with Match and Index, most likely both of
which would need to be used in the example below.

Here is my Data (numbers under state represent units sold in each
state):

Emp ID State
.. CA NY TX FL IA
1045 10 10 0 40 70
1046 20 80 40 70 90
1047 0 0 60 50 30
1048 50 50 10 20 20
1049 60 20 80 10 0

I would like to set up a formula by which a person can type in an
employee ID and retrieve all the sales data for that employee.

I envision it listing out each state and corresponding units sold for
that employee. I would like the output to be sorted by units sold
highest to lowest.

I have used a combination of Index, Match and Large functions to get
what I want for a known employee number but that doesn't work if I have
an unknown employee (prior to user input). I am assuming a lookup
would need to be nested in there somewhere. I am just having trouble
figuring out the order. Then again, Maybe it I am missing the boat
completely.

Also, if anyone has any suggestions on breaking ties in rankings using
the LARGE function, I could use some advice there also.
Thanks in advance

 

[Dave Peterson]

An alternate approach...

Say your headings are in rows 1:2.
Insert a new row 1.

Put this in A1:
=subtotal(3,A4:a65536)

put this in B1:
=subtotal(9,A4:a65536)

=subtotal(3,...) counts the number of entries you can see in that range
=subtotal(9,...) sums the quantities in the cells you can see.

Now select A3 (last row of headers) through the bottom right cell of your data.

Click on Data|filter|autofilter

Now filter column A by one of your employee id's.

Those formulas will reflect the values for just those visible cells.

(I'd add one more column that summed all the states in each row. Just to make
it a little easier to get a grand total.)

 

[Max]

Another option to play around ..

Assume the sample data below is
in Sheet1, A1:F7, headers in rows 1 and 2,
data from row3 down

Emp ID State
. CA NY TX FL IA
1045 10 10 0 40 70
1046 20 80 40 70 90
1047 0 0 60 50 30
1048 50 50 10 20 20
1049 60 20 80 10 0

Select A3:A7 and name the range: Emp

In Sheet2
--------------
Set up a Data Val. list in A1 to select employees

Select A1
Click Data > Validation
Under "Allow", select: List
Put in the "Source:" box: =Emp
Click OK

Put in D1: =A1

Put in:

D2: =INDEX(Sheet1!$B$2:$F$2,ROW(A1))

E2:

=IF(OR(ISNA(MATCH(D$1,Emp,0)),ISNA(MATCH($D2,Sheet1!$B$2:$F$2,0))),"",INDEX(
Sheet1!$B$3:$F$7,MATCH(D$1,Emp,0),MATCH($D2,Sheet1!$B$2:$F$2,0)))

F2: =IF(E2="","",E2-ROW()/10^10)

(col F will act as an arbitrary tie-breaker col)

Select D2:F2, copy down by as many rows
as there are states in the table in Sheet1
(For the sample data, copy down to F6)

Hide away cols D to F

Put in A2:

=INDEX(Sheet2!D,MATCH(LARGE(Sheet2!$F:$F,ROW(A1)),Sheet2!
$F:$F,0))

Copy across to B2,
fill down by as many rows
as there are states in the table in Sheet1
(For the sample data, fill down to B6)

The above will aut0-return
the descending sort of the sales data from Sheet1
for the employee selected in A1

If in A1 is selected: 1045 (from the DV droplist),
you'll get:

1045
IA 70
FL 40
CA 10
NY 10
TX 0

If you select in A1: 1048,
you'll get

1048
CA 50
NY 50
FL 20
IA 20
TX 10

And so on ..

Any ties (e.g.: sales data for CA and NY for 1048 above)
will be returned in the same order
as they appear in the source table in Sheet1

 

[Domenic]

Here's another approach...

Assuming that the first and second row contain your headers, and that
your data starts on the third row...

1) Select H3:H7

2) With those cells highlighted, enter the following array formula that
needs to be confirmed with CONTROL+SHIFT+ENTER:

=IF(L2<>"",TRANSPOSE(B2:F2),"")

3) Select I3:I7

4) With those cells highlighted, enter the following array formula that
needs to be confirmed with CONTROL+SHIFT+ENTER:

=IF(L2<>"",TRANSPOSE(INDEX(B3:F7,MATCH(L2,A3:A7,0),0)),"")

5) Enter the following formula in J3 and copy down:

=IF(I3<>"",RANK(I3,$I$3:$I$7)+COUNTIF($I$3:I3,I3)-1,"")

So far, this will give you your helper columns which can be hidden if
you so desire.

L2: enter the Employee ID of interest

M3, copied across:

=IF($L$2<>"",INDEX($H$3:$H$7,MATCH(COLUMN()-COLUMN($M$3)+1,$J$3:$J$7,0)),
"")

M4, copied across:

=IF($L$2<>"",INDEX($I$3:$I$7,MATCH(COLUMN()-COLUMN($M$4)+1,$J$3:$J$7,0)),
"")

Hope this helps!