find value in column F, then display value in column A of that row

[rc]

I'm trying to populate a new worksheet by flagging cells in a previous
worksheet. Specifically, populate the first column of worksheet named
BestBall using this logic:

If Entry!F4:F154 = "bb1", then grab value of column A and display in first
column of BestBall.

So, let's say we find "bb1" at Entry!F30 and F59. The values contained in
Entry!A30 and A59 need to display in BestBallA4.


Thank you,

rc

 

[Biff]

Hi!

So, let's say we find "bb1" at Entry!F30 and F59. The values contained in
Entry!A30 and A59 need to display in BestBallA4.

Easily done if you put the results in separate cells. If you want all of the
results to appear in a single cell, good luck!

Biff

 

[rc]

Single cell is preferable, but I can make do with separate cells. I know it
is probably very easy to do, but did you forget to divulge the formula here?

Thanks for your response.

rc

 

[Biff]

Hi!

If you want the results going down the column: A4, A5, A6 etc:

Enter this formula in BestBall!A4 as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(Entry!F$4:F$154,"bb1"),INDEX(Entry!A$4:A$154,SMALL(IF(Entry!F$4:F$154="bb1",ROW(Entry!A$4:A$154)-ROW(Entry!A$4)+1),ROWS($1:1))),"")

Copy down until you get blanks.

If you want the results going across the row: A4, B4, C4 etc:

Enter this formula in BestBall!A4 as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(COLUMNS($A:A)<=COUNTIF(Entry!$F4:$F154,"bb1"),INDEX(Entry!$A4:$A154,SMALL(IF(Entry!$F4:$F154="bb1",ROW(Entry!$A$4:$A$154)-ROW(Entry!$A$4)+1),COLUMNS($A:A))),"")

Copy across until you get blanks.

Biff

 

[rc]

This is getting close, but still not exactly what I need to do. Maybe this
will help me clarify:

In Entry!A4:A153 are names of golfers playing in a tournament. This workbook
does many scoring calculations for each player individually on several
worksheets, however I'd like to add a new worksheet called BestBall, where
I'm attempting to pair golfers into teams.

So, Entry!F4:F153 will contain two bb1 flags for the first pairing, two bb2
flags for the second pairing, and so on.

By telling BestBallA4:A153 to find the flags in Entry!F4:F153, I'm hoping to
display those pairings.

I hope this helps to convey my goal.

Thanks,

rc

 

[Biff]

Ok, got it!

Enter this array formula in BestBall!A4:

=INDEX(Entry!$A$4:$A$153,SMALL(IF(Entry!$F$4:$F$153="bb"&ROWS($1:1),ROW(A$4:A$153)-ROW(A$4)+1),COLUMNS($A:A)))

Copy across to B4 then down for 75 rows.

You'll get 75 2-man pairings in ascending order.

Biff

 

[rc]

Biff, this worked perfectly! For the most part, I understand what the formula
is doing, but I'll need to look at it more closely to REALLY absorb it.

Thank you!

rc

 

[Biff]

You're welcome. Thanks for the feedback!

Biff