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,084,735
Messages
5,379,503
Members
401,608
Latest member
nanana2020

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top