Find a non-blank cell and bring back text a in same row

How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Instead of a formula based approach why not simply copy and paste the data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" <Rod@discussions.microsoft.com> wrote in message
news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> How can I have excel:
> 1) search a specific row, e.g R4:R500 and for every occurance of a
> non-blank
> cell
> 2) bring back what corresponds to that row in col D?
>
> Thanks

Hi,

The source data will change daily. I would like to bring this summary sheet
up and have the sheet do the calculations and present the results in a clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

> Instead of a formula based approach why not simply copy and paste the data
> on column R on to another sheet
> Sort the data
> and paste it back on column D in your original sheet
>
> "Rod" <Rod@discussions.microsoft.com> wrote in message
> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> > How can I have excel:
> > 1) search a specific row, e.g R4:R500 and for every occurance of a
> > non-blank
> > cell
> > 2) bring back what corresponds to that row in col D?
> >
> > Thanks
>
>
>

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400<>"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400<>"",ROW($A$1:$A$397)),ROW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" <Rod@discussions.microsoft.com> wrote in message
news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
> Hi,
>
> The source data will change daily. I would like to bring this summary
> sheet
> up and have the sheet do the calculations and present the results in a
> clean
> manner, e.g. no blanks, etc..
>
> "N Harkawat" wrote:
>
>> Instead of a formula based approach why not simply copy and paste the
>> data
>> on column R on to another sheet
>> Sort the data
>> and paste it back on column D in your original sheet
>>
>> "Rod" <Rod@discussions.microsoft.com> wrote in message
>> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
>> > How can I have excel:
>> > 1) search a specific row, e.g R4:R500 and for every occurance of a
>> > non-blank
>> > cell
>> > 2) bring back what corresponds to that row in col D?
>> >
>> > Thanks
>>
>>
>>

No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files: '[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<>"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<>"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of "NAME".
4) The desired result is for a return of "Tracy" given row D17 is "Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

> Ok then on column D type this formula
> =IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400<>"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400<>"",ROW($A$1:$A$397)),ROW(1:1))))
>
> And array enter it (ctrl+shift+enter instead of just Enter)
>
> And copy it down all the way for 397 rows.
>
> This will give you a list of all non blanks
>
>
> "Rod" <Rod@discussions.microsoft.com> wrote in message
> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
> > Hi,
> >
> > The source data will change daily. I would like to bring this summary
> > sheet
> > up and have the sheet do the calculations and present the results in a
> > clean
> > manner, e.g. no blanks, etc..
> >
> > "N Harkawat" wrote:
> >
> >> Instead of a formula based approach why not simply copy and paste the
> >> data
> >> on column R on to another sheet
> >> Sort the data
> >> and paste it back on column D in your original sheet
> >>
> >> "Rod" <Rod@discussions.microsoft.com> wrote in message
> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> >> > How can I have excel:
> >> > 1) search a specific row, e.g R4:R500 and for every occurance of a
> >> > non-blank
> >> > cell
> >> > 2) bring back what corresponds to that row in col D?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
>

Rod

Explain me exactly what you need
My understanding is in your tracking sheet you got names on COLUMN R but
with blank rows. And in the sheet where you want "clean
presentation of the information" sheet COI on column D you need that name to
appear but without blank rows.
So where do the dates come from.....

This formula is going to do the following:-

Say column R in tracking sheet has names as follows:-

Tracy
....
....
Robert
....
Alex
....
Joe


In your clean presentation sheet his formula will show it as follows :

Tracy
Robert
Alex
Joe

IN short Dropping all blank rows

Explain if this is what you wanted to achieve




"Rod" <Rod@discussions.microsoft.com> wrote in message
news:300B8704-54C7-4BE5-AD78-6C1CBFDEADC0@microsoft.com...
> No Luck. Here are my results:
> 1) I updated your suggestion to reflect the two different files:
> '[Tracking
> Sheet]Other Srcs' and summary sheet, which is suppose to give me the clean
> presentation of the information, call it COI
> 2) The formula looks like this:
> {=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
> Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
> Srcs'!$R$4:$R$400<>"",ROW('[Tracking Sheet.xls]Other
> Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
> Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
> Srcs'!$R$4:$R$400<>"",ROW('[Tracking Sheet.xls]Other
> Srcs'!$D$1:$D$397)),ROW(1:1))))}
> 3) and returned a value of 38430 in COI's A2 (A1 is a col heading of
> "NAME".
> 4) The desired result is for a return of "Tracy" given row D17 is "Tracy"
> and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the first
> occurance of a data which should be returned as a desired match.
>
> Thoughts?
>
> "N Harkawat" wrote:
>
>> Ok then on column D type this formula
>> =IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400<>"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400<>"",ROW($A$1:$A$397)),ROW(1:1))))
>>
>> And array enter it (ctrl+shift+enter instead of just Enter)
>>
>> And copy it down all the way for 397 rows.
>>
>> This will give you a list of all non blanks
>>
>>
>> "Rod" <Rod@discussions.microsoft.com> wrote in message
>> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
>> > Hi,
>> >
>> > The source data will change daily. I would like to bring this summary
>> > sheet
>> > up and have the sheet do the calculations and present the results in a
>> > clean
>> > manner, e.g. no blanks, etc..
>> >
>> > "N Harkawat" wrote:
>> >
>> >> Instead of a formula based approach why not simply copy and paste the
>> >> data
>> >> on column R on to another sheet
>> >> Sort the data
>> >> and paste it back on column D in your original sheet
>> >>
>> >> "Rod" <Rod@discussions.microsoft.com> wrote in message
>> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
>> >> > How can I have excel:
>> >> > 1) search a specific row, e.g R4:R500 and for every occurance of a
>> >> > non-blank
>> >> > cell
>> >> > 2) bring back what corresponds to that row in col D?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>
>>
>>

Have you considered just using Autofilter?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Rod" <Rod@discussions.microsoft.com> wrote in message
news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> How can I have excel:
> 1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
> cell
> 2) bring back what corresponds to that row in col D?
>
> Thanks

Never mind, just read the rest of the text, apologies.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:%23s5nrfAMFHA.3844@TK2MSFTNGP14.phx.gbl...
> Have you considered just using Autofilter?
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "Rod" <Rod@discussions.microsoft.com> wrote in message
> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> > How can I have excel:
> > 1) search a specific row, e.g R4:R500 and for every occurance of a
> non-blank
> > cell
> > 2) bring back what corresponds to that row in col D?
> >
> > Thanks
>
>