Another INDEX question

Roghaltz

New Member
Joined
Nov 26, 2015
Messages
26
Hi All,
Here is a problem I am wrestling with. We have many sales contests per year (each uniquely identified by the DateEventID column).

Prizes are always awarded to the top 2 salespeople (see the Rank column). At management's discretion prizes can also awarded to one or more salespeople who exhibit extraordinary effort (see the "Y" in the Effort column for winners). There can be between 0 and 2 Effort winners. There are always between 6 and 15 salespeople per contest.

The Prelim column captures the salesperson if they finished in the top 2 OR received an Effort award.

The goal is the OUTPUT column. Namely, at the beginning of each new event capture the salespeople who either finished Top 2 or received an Effort award in the top two to four rows of each event. They can be in any order.

I have been fooling around with INDEX to remove the blanks to go from Prelim to OUTPUT, but can't make it work. There are many events so I'd rather not created OUTPUT by hand.

Excel 2010
ABCD
EFGH
1DateSalespersonEventTypeDateEventIDRankEffortPrelimOUTPUT
2Oct-15a1aOct-15-a1a1a1
3Oct-15a2aOct-15-a5Ya2a2
4Oct-15a3aOct-15-a3a4
5Oct-15a4aOct-15-a2a4
6Oct-15a5aOct-15-a4
7Oct-15a6aOct-15-a6
8Nov-15a1bNov-15-b2a1a1
9Nov-15a2bNov-15-b4a3
10
Nov-15a3bNov-15-b1a3a5
11
Nov-15a4bNov-15-b3a6
12
Nov-15a5bNov-15-b5Ya5
13
Nov-15a6bNov-15-b6Ya6
14
Nov-15a7bNov-15-b8
15
Nov-15a8bNov-15-b7
16
Nov-15a1aNov-15-a8a3
17Nov-15a2aNov-15-a4a7
18Nov-15a3aNov-15-a2a3
19Nov-15a4aNov-15-a3
20Nov-15a5aNov-15-a5
21Nov-15a6aNov-15-a6
22Nov-15a7aNov-15-a1a7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1



Many thanks in advance.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Okay. so you need column G's value (without blank) in column H at each change of column D (DateEventID).. correct ?



Regards,
DILIPandey
 
Upvote 0
Based on my post#2, here is a solution:-

Enter below formula in H2 with key combination ctrl shift enter instead of just enter.

=IFERROR(INDEX($G$1:$G$22,SMALL(IF(($D$2:$D$22=$D2)*($G$2:$G$22<>""),ROW($G$2:$G$22),""),COUNTIF($D$2:$D2,$D2))),"")

and drag down:-



Regards,
DILIPandey
 
Upvote 0

Forum statistics

Threads
1,211,528
Messages
6,102,369
Members
447,794
Latest member
rajmungur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top