Filling in random blank cells

[Hayley]

I'm not sure how to properly explain this, but I have these reports that show account numbers and all the components that are part of those accounts. Each component is on a separate line, but the account number is only on the line of the first component. So, for example, Column A, Row 1 will show the account number 522347. Then Column B, Row 1 will show Base Fare. Column A, Row 2 will be blank, so it's assumed to be the same account number. Column B, Row 2 will show Taxes. Column A, Row 3 will be blank (still same account number). Column B, Row 3 will show Service Fees. etc.

Then Column A, Row 4 will show account number 522440. And then it goes through the same way.

I want to fill in the blanks with the account numbers so that I can sort by components but still see what account they belong to. If I sort by component now, anything that's not on the first row of the account will show a blank account number.

Is there an easy way to do that? Not kidding, I just copied and pasted 1500 rows. My wrists are killing me.

 

[Xiq]

Hi Hayley,

You can try the following steps:

1. Place the following formula in a helper column on the second row (so maybe C2):
   

   =IF(LEN(A2)=0,A1,A2)

2. Copy down the formula
3. Select the cells in column C
4. Copy (ctrl + c)
5. Special past (ctrl + alt + V)
6. Choose "Value" (V)
7. Press OK
8. ...
9. Profit!

 

[Hayley]

That's good for the 1st blank row, but for the ones after that it returns 0. So if row 1 has the acct number, and rows 2, 3 and 4 are blank, that formula will return the account number in row 2 but "0" in row 3 and row 4. Is there a way to tell it to look for the last cell that has a value in it?

 

[Xiq]

Alright, replace the formula in my last comment with this one, but now start in C1:

=IF(LEN(A1)=0,INDEX($C$1:C1,ROWS($A$1:A1)-1),A1)

 

[Hayley]

Ooh, yes that worked! Thank you! Is it worth it to explain what it all means?

 

[shrivallabha]

1. Select Column A. Lets say your data is A1:A100
2. Press F5 | Click Special | Select Blanks | Click OK. It will select all blank cells in the selected range.
3. Then type in the following sequence = followed by UP arrow. This will select first non-blank cell reference.
e.g. if A2 is your first non-blank then formula bar will show =A2.
4. Press CTRL + ENTER i.e. hit ENTER key while keeping CTRL key pressed.
5. Select column A and then do Copy and Paste Special values.

 

[Xiq]

Ooh, yes that worked! Thank you! Is it worth it to explain what it all means?

Let me first give you a less complex formula:

=IF(LEN(A1)=0,INDEX($C:$C,ROW()-1),A1)

I use the following:

• IF-function
• LEN-fucntion
• ROW-fucntion
• INDEX-fucntion

Basically, this happens:

• In the IF-fucntion I ask -- is A1 blank? --
• If not, use the INDEX-function
• Else just reference to A1

The INDEX-function wants to reference something:

• So in the INDEX-function I tell it to reference the whole helper column (C:C)
• I want to reference to the nth object in that column, I do that by asking what row we are on, then minus one.

 

[Hayley]

1. Select Column A. Lets say your data is A1:A100
2. Press F5 | Click Special | Select Blanks | Click OK. It will select all blank cells in the selected range.
3. Then type in the following sequence = followed by UP arrow. This will select first non-blank cell reference.
e.g. if A2 is your first non-blank then formula bar will show =A2.
4. Press CTRL + ENTER i.e. hit ENTER key while keeping CTRL key pressed.
5. Select column A and then do Copy and Paste Special values.

Ooooh this worked too!!!! Thank you!!!