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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rc - 20 Mar 2006 23:32 GMT

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

Reply to this Message

Biff - 21 Mar 2006 04:27 GMT

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

> I'm trying to populate a new worksheet by flagging cells in a previous
> worksheet. Specifically, populate the first column of worksheet named
[quoted text clipped - 9 lines]
>
> rc

Reply to this Message

rc - 21 Mar 2006 05:19 GMT

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

> Hi!
>
[quoted text clipped - 19 lines]
> >
> > rc

Reply to this Message

Biff - 21 Mar 2006 07:08 GMT

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

> Single cell is preferable, but I can make do with separate cells. I know
> it
[quoted text clipped - 32 lines]
>> >
>> > rc

Reply to this Message

rc - 22 Mar 2006 00:06 GMT

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

Reply to this Message

Biff - 22 Mar 2006 00:57 GMT

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

> This is getting close, but still not exactly what I need to do. Maybe this
> will help me clarify:
[quoted text clipped - 18 lines]
>
> rc

Reply to this Message

rc - 22 Mar 2006 02:51 GMT

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

Reply to this Message

Biff - 22 Mar 2006 03:18 GMT

You're welcome. Thanks for the feedback!

Biff

> Biff, this worked perfectly! For the most part, I understand what the
> formula
[quoted text clipped - 3 lines]
>
> rc

Reply to this Message