Column : transfer from sheet1 to sheet2

[Jack]

Hi ,


I have an Admin worksheet with 2 columns.
Column A has basball's teams and Column B has
Comments. Some comments are [NULL]. I want to transfer
to another Worksheet comments of only one team without
[NULL]. I want something to reuse the code (no copy and
paste). Is it possible??

I thought about

=????(--(Admin!$A$1:$A$444="NYYankees"),--(Admin!
$B1:$B$444<>[NULL]))

Thanks!

Jack

 

[Bernie Deitrick]

Jack,

In cell A1 of another sheet, use Ctrl-Shift-Enter to enter the array formula

=LARGE((Admin!$A$1:$A$444=$D$1)*(Admin!$B$1:$B$444<>"")*ROW(Admin!$A$1:$A$44
4),ROW())
In cell B1, enter the formula
=IF(A1<>0,INDEX(Admin!$A$1:$A$444,A1,1),"")
In Cell C1, enter the formula
=IF(A1<>0,INDEX(Admin!$B$1:$B$444,A1,1),"")
and in cell D1, enter the team name of interest.

Then copy A1:C1 down as many rows as you expect the maximum number of team
comments will be.

HTH,
Bernie
MS Excel MVP