excel - auto fill down between data sets 4

[stoke]

i have a set of data running from columnn A to AA, and several thousand rows long.
column E gives a descriptor name for all the rows below it, until a new descriptor is given, but does not give the descriptor for all the rows until the a new descriptor is given.

My first 'set' of data runs between E2 and E67 (although this is not fixed) -
so E2 has descriptor 'Invoice set 1' and E68 has descriptor 'Invoice set 2' and so on.
Column E is blank from E3 to E67.

It would be enormously useful if i could have a formula that would look for a descriptor and then auto fill down to where the next descriptor is.

Thank you!

 

[ETID]

in a column of your choice....

Note: F1 in the following will change to your col,...row1

=IF(ROW()=2,E2,IF(E2="",F1,E2))

fill down

 

[stoke]

Splendid stuff - thank you very much!

 

[KenWright]

The easiest way to do this is to select E1:E10000 (Assuming your range is that, and that you have data in odd cells at different intervals eg E1, E35, E134, etc, then do Edit / Go To / Special / Blanks. This will make your active cell E2. Now simply type = and then use the mouse to click onto cell E1, and then hit CTRL+ENTER. This will enter all your missing values.

Now select all of Col E and do Edit / Cop / then Edit / paste Special / Values.

Regards
Ken...............

 

[stoke]

once again i am very grateful - and impressed!

 

[KenWright]

My Pleasure

Regards
Ken..................

 

[xlbo]

Ken - very nice. It's always worth a star for different (and novel) ways of solving a problem

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

Cheers Geoff.

There are other uses you can put this too as well, eg if you had a set of data in the following format, with blanks in between your sets of data, and you wanted to sum each of the groups:-

3
4
3

2
3
4
5

4
5

5
4

4
5
6
2
3

You can use the same method of selecting the blanks but simply hit Autosum instead. I know if you had identifiers in another column you could use Data / Subtotals, but that's not always the case.

You can also hit CTRL+B before you deselect in order to bolden all the totals.

I just love that Go To / Special feature though. Great way of finding conditional formats, objects you cant see (eg Buttons that have shrunk because you deleted rows), Data Validation cells. Helps in auditing a sheet as well, especially with being able to tell it to highlight just constants, or just formulas - Turn them all red and see if any are not what you would expect.

Regards
Ken.................