Fill in data from firt NON BLANK cells from Above:

**smannon** · 07-13-2009, 03:31 PM

I have a spreadsheet with over 6,000 records. it is setup as follows:

A1:E1 are the column headings
A1= CustomerID; B1= Customer; C1=Item; D1= Item Description; E1= Price

Rows 1-5 have Customer ID & Customer informaion
Rows 6-15 is the order information for Customer in Row 5 but the column information for column A6:B15 are blank

I want to automatically fill in the values in row A6:B15 with the first non-blank information from above. In this case it would be A5:B5.

This needs to be done by finding the blank cells since there is no pattern to the information and there are too many blanks to go through and manually edit the information and some blanks are only one row and others are multiple rows.

Thanks for the help!
Steve M.

**shg** · 07-13-2009, 03:37 PM

If I understand what you want, try this:

Select columns A:B

Do Edit > Goto > Special, Blanks

Put the cursor in the formula bar, type = then hit the up arrow key. DON"T hit Enter.

Press and hold the Ctrl key, then press Enter.

If that doesn't give the result you want, post a workbook.

**smannon** · 07-13-2009, 03:48 PM

Here is a sample of what I am working with.

**shg** · 07-13-2009, 03:53 PM

OK, two setup steps, because the data in cols A:B is formatted as text, and some cells that appear blank contain null strings.

1. Select cols A:B and format as General.

2. Select col A and do Data > Text to Columns, Finish. Repeat for col B.

3. Proceed as previously suggested.

**smannon** · 07-13-2009, 04:11 PM

I am now able to select all Blanks with the GO TO command, however placing the "=" sign in the formula bar and pressing the UP arrow & then holding SHIFT+ENTER moves the selection to the LAST BLANK Cell.??????

**smannon** · 07-13-2009, 04:53 PM

First of all thanks for all the info...I was able to acheive the results. Here's what I did:

From "SHG" Post:
1. Select cols A:B and format as General.

2. Select col A and do Data > Text to Columns, Finish. Repeat for col B.

3. Put the cursor in the formula bar, type = then hit the up arrow key. DON"T hit Enter.

What I found different was the following:

4. Immediately following step 3 Select Column A and Column B right above one of the blank selections that has data.

5. Press CTRL+ENTER

This Automatically filled in the data from the first NON-BLANK row above all BLANK sections in the entire document.

This is so easy! Thanks for all your help!

**shg** · 07-13-2009, 04:54 PM

Sorry, Ctrl+Enter, my bad

Ctrl and Shift are two of the few keys I can locate without looking, so I sometimes swap the names

After you do that, you probably want to select cols A:B again, copy, and paste as values, eliminating the formulas.

Would you please mark the thread as Solved?

**arunovia** · 07-29-2009, 07:45 AM

I have been trying to do the same, but not getting expected result.

Could you please let me know which cell should be hiighlighed when we enter = in formula bar ?

Also could you please tell me exactly which cells in your example sheet are you selecting for point 4 (4. Immediately following step 3 Select Column A and Column B right above one of the blank selections that has data.) ?