How to populate a cel with the previous cel information?

[Steven547]

I'm looking to figure out how to populate a cel with the previous cel data, IF the current cel is blank.

Column A Column B Column C
Test A Test A Test A
Test A Test A
Test B Test B Test B
Test B Test B
Test B Test B
Test B Test B
Test C Test C Test C
Test C Test C

So the empty field under TEST A would populate with the previous data (which would be TEST A). And same for the other fields. So in the FIELD A column, there would be no blanks. And I can't have it equal to Field B because that is not always going to be populated and it's different data. Tried to format the above...basically, Column A only has 3 bits of data in it (Test A, Test B, Test C). It was supposed to format like in excel.

Any suggestions? Help?

Thanks!

 

[SkipVought]

Hi,

In a new column, use a forumal like...
[tt]
D2: =if(a1=a2,d1,a2)
[/tt]]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Steven547]

I was able to use the OFFSET function, but the only problem with that, is it is populating the wrong data in the next row. If that next row is populated, it can't add data there:

Data A Data B
Data A Data B
Data A Data C
Data C Data C

So instead of it being Data C with Data C, it's doing Data A with Data C:

Offset form: =OFFSET(B3,-1,1,1)

 

[SkipVought]

Why are you using OFFSET?

did you TRY the technique I posted?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

This should do exactly what you're after:
thread68-1388904

NOTE: It's hard to tell exactly what you want because your sample data doesn't line up when posted. If you surround the sample data with [ignore][tt][/tt][/ignore] tags it will post properly.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Steven547]

I tried it, but it only returned a value of "0". So I'm still experimenting with it to see what's not working...

 

[Steven547]

Sorry anotherhiggins, didn't see your post until after I posted mine. I tried that and it basically populates the entire Column A with the first value only.
If there is a value in Column A, then the next rows should be populated with that preceeding value, until it reaches a DIFFERENT value, in which case, rinse and repeat.

[tt]
Column A Column B Column C
Test A Test A Test A
Test A Test A
Test B Test B Test B
Test B Test B
Test B Test B
Test B Test B
Test C Test C Test C
Test C Test C
[/tt]

The blanks would be populated with the preceeding values (Test A, Test B, Test C) Make sense and I hope my formatting worked!

 

[Steven547]

Shoot, format didn't work. Apologies.

Basically, flush it all to the right so you have 3 data on the 1st line, 2 pieces of data on the 2nd.... (so Column A has data, then the next row is blank, then data, then 3 blanks, then data, then blank)

 

[Steven547]

May have figured it out:

=IF(A2="", OFFSET(A2,-1,1,1), A2)

If I use the (-1) instead of (1), it seems to work because it has to look at the PREVIOUS data, not the NEXT data which would populate it wrong...

 

[SkipVought]

oops sorry
[tt]
D2: =IF(ISBLANK(A2),D1,A2)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Steven547]

Guess that'll work, too!

Thanks!

 

[SkipVought]

Then you could simply COPY column D and Edit > Paste Special -- VALUES over column A.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Steven547:

(back to my solution from the linked thread)

Did you try it?

I ask because I've done this and had others do this and.... well, it works. Since you first filter down to ONLY the blanks, only those visible blanks will take the formula that you're entering.

But if for some strange reason your Excel is not behaving this way, then you can add a step as follows - this will definitely work without exception:

Data > Filter > Auto Filter

For Column A, Filter on Blanks

Select all cells in column A - except for the header record - all the way down to the end of the used rows

[!][Ctrl]+[G], [Alt]+, [Y], [Enter][/!] *

type in = and press the Up Arrow

Press [Ctrl]+[Enter]

Data > Filter > Auto Filter

*That step is a keyboard shortcut for selecting only visible cells.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Steven547]

thanks. I tried SkipVought's method:

=IF(ISBLANK(A2),D1,A2)

And it works pretty good. Thanks for all the advice!

 

[Loomah]

not directly related to the thread but hopefully useful anyway....

*That step is a keyboard shortcut for selecting only visible cells

oooo, way too much effort for a lazy wotsit me!!
you can substitute ctrl+g with f5 but that doesn't really help!
but the simple shortcut for the whole thing is
alt+;



;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​