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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
Okay. so you need column G's value (without blank) in column H at each change of column D (DateEventID).. correct ?



Regards,
DILIPandey
 

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,279
Messages
5,836,366
Members
430,424
Latest member
ALHK022

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
Top