Search Column Data and Return Multiple Values across Row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sam - 30 Sep 2006 17:39 GMT

Hi All,

I have two columns of data. Column "A" houses Numeric Labels and Column "B"
houses Numeric Values.

Data:
Numeric Labels A20:A105
Numeric Values B20:B105

I would like to return across a Single Row all Numeric Lables that have a
corresponding Numeric Value in Column "B" that is >=2 (greater than or equal
to 2).

Thanks,
Sam

Reply to this Message

Domenic - 30 Sep 2006 18:36 GMT

Assuming that the results are to be returned in Row 20, starting at D20,
try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER...

D20, copied across:

=IF(COLUMNS($D20:D20)<=COUNTIF($B$20:$B$105,">=2"),INDEX($A$20:$A$105,SMA
LL(IF($B$20:$B$105>=2,ROW($B$20:$B$105)-ROW($B$20)+1),COLUMNS($D20:D20)))
,"")

Hope this helps!

> Hi All,
>
[quoted text clipped - 11 lines]
> Thanks,
> Sam

Reply to this Message

Sam - 30 Sep 2006 19:03 GMT

Hi Domenic,

Thank you very much. Formula works Great!

Cheers,
Sam

>Assuming that the results are to be returned in Row 20, starting at D20,
>try the following formula which needs to be confirmed with
>CONTROL+SHIFT+ENTER...

>D20, copied across:

>=IF(COLUMNS($D20:D20)<=COUNTIF($B$20:$B$105,">=2"),INDEX($A$20:$A$105,SMA
>LL(IF($B$20:$B$105>=2,ROW($B$20:$B$105)-ROW($B$20)+1),COLUMNS($D20:D20)))
>,"")

>Hope this helps!

Reply to this Message

JMB - 30 Sep 2006 19:50 GMT

Just an FYI at this point - you also could have used the autofilter and
filtered on column B (custom) for values >= 2. Then select the values in
column A and copy, select your destination, then click Edit/Paste Special -
Transpose

> Hi All,
>
[quoted text clipped - 11 lines]
> Thanks,
> Sam

Reply to this Message