Fill Series - Column with Multiple Values

Maureen5433

Board Regular
Joined
May 13, 2002
Messages
109
I am feeling sheepish - like this should be simple but nothing is working. I have a column:

LABEL COUNT
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 16
15 empty cells

I need to fill the empty cell with the "series" (2 of 2, 2 of 16, 3 of 16). This is just an example of data - there are all different series - 1 of 12, 1 of 5, etc.

Anyway - was hoping to highlight the whole column and be able to auto fill instead of having to extend each series. I have tried straightforward fill series which doesnt work of course. I have tried go to special - blanks but cant find right combo to make it work.

Will it work or do I have to either do manually or with a macro?

Thank you for any input or ideas. I greatly appreciate. I apologize for not posting snippet of spreadsheet - I cannot seem to remember how. Hopefully this is simple enough that my sad excuse will do. In interim I will refresh my memory.

Regards,
Maureen
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this, it's a bit tricky

highlight the column and press CTRL + G
click special
select blanks
click ok
Press =
put in the formula
=REPLACE(A1,1,FIND(" ",A1)-1,LEFT(A1,FIND(" ",A1)-1)+1)
DO NOT PRESS ENTER

the tricky part is to adjust all the A1 references to whatever the cell reference that is 1 cell above the first blank cell..
So if the first blank cell in the column is say A5, then change all the A1's to A4

Then press CTRL + ENTER

Hope that helps.
 

Maureen5433

Board Regular
Joined
May 13, 2002
Messages
109
You are my hero - I felt like it was going to have to be GoTo Special/Blanks but I was just not getting close afer that. Thank you so much - you have just improved the next 3 days of my life by leaps and bound.

Warm Regards,
Maureen
 

Forum statistics

Threads
1,082,478
Messages
5,365,783
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top