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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

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