Lookup vertically to next cell with data
 

I've got a huge array of data (approx 13,000 rows X 15 columns). I'm looking
for a formula to replace the blank cells as I don't want to copy-paste
13,000 rows. I need the formula to look up the column to find the ID number.

The first column of data contains patient ID numbers. However, there
are blank cells beneath each unique patient ID number until another
unique ID is used. Cells that are adjacent to the blank
ID cells contain data. Is there a formula I can use in Column A to fill in
the blank cells (or lookup and copy the ID number)? Is there VBA code that I
can use?

Here's my data set with the blank cells. My desired result is below.
(col A) (col B)
ID Result
101 Happy
blank Sad
blank Happy
102 Sad
blank Sad
blank Happy
blank Happy
blank Happy

Here's my desired result which replaces the blank cells with the appropriate
ID number.
(col A) (col B)
ID Result
101 Happy
101 Sad
101 Happy
102 Sad
102 Sad
102 Happy
102 Happy
102 Happy

Thanks in advance for your help.

-Scott

Lookup vertically to next cell with data
 

Click to select a single cell in your table. Then Data Filter
Autofilter. In the drop-down in A1, select (blanks); it'll be at the bottom
of the list. Click in the first visible cell in column A and note the
address. Suppose it's cell A4. In that cell, enter the formula =A3 (the key
is that the row number has to be one less than the active row). Copy that
formula down into all the visible rows (they're the ones that are blank and
need the formula). When your done turn the filter off (Data Filter
Autofilter). If you want to lock down the valus you filled in, highlight
column A and Edit Copy. Then Edit Paste Special, select Values and click
OK.

"Scott" wrote:

I've got a huge array of data (approx 13,000 rows X 15 columns). I'm looking
for a formula to replace the blank cells as I don't want to copy-paste
13,000 rows. I need the formula to look up the column to find the ID number.

The first column of data contains patient ID numbers. However, there
are blank cells beneath each unique patient ID number until another
unique ID is used. Cells that are adjacent to the blank
ID cells contain data. Is there a formula I can use in Column A to fill in
the blank cells (or lookup and copy the ID number)? Is there VBA code that I
can use?

Here's my data set with the blank cells. My desired result is below.
(col A) (col B)
ID Result
101 Happy
blank Sad
blank Happy
102 Sad
blank Sad
blank Happy
blank Happy
blank Happy

Here's my desired result which replaces the blank cells with the appropriate
ID number.
(col A) (col B)
ID Result
101 Happy
101 Sad
101 Happy
102 Sad
102 Sad
102 Happy
102 Happy
102 Happy

Thanks in advance for your help.

-Scott