Fill in blank cells + Create 'Record Numbers'

[GFP1963]

Probably not the best title to get the point across but now that you are here...:

Have a spreadsheet with rows of company names and multiple contacts per company.
The company name data is only in the row of the first contact for the company.
The subsequent company name rows are blank until the next set of company contacts begins.

Need to accomplish 2 things:

1. Fill in the blank company name cells with the correct company name (the contacts are grouped by company)
2. Add a numeric 'record number' for each contact per company (1 - ##), repeating at 1 for the first contact/record per company.

A sample set of data is available for review here:

1. Sheet 1 illustrates the 'starting point'.
2. Sheet 2 illustrates where we need to end up.

(it is a Google Sheet but am working in Excel - for Mac - version 14.6.8 :/ )

 

[Fluff]

Not sure if this works on a Mac, but try

Sub fillBlanks()

   Dim Rng As Range
   
   For Each Rng In Columns(2).SpecialCells(xlBlanks).Areas
      With Rng.Offset(-1, -1).Resize(1)
         .Value = 1
         .AutoFill Rng.Offset(-1, -1).Resize(Rng.Count + 1), xlFillSeries
      End With
      Rng.Offset(-1).Resize(Rng.Count + 1).FillDown
   Next Rng
      
End Sub

 

[GFP1963]

Thank you for the reply. I was hoping there would be a formula or formulas to work with as I am an idiot when it comes to VBA. If possible could you set up a sheet with that macro? I can message you my email address or any other delivery method you prefer.

 

[Fluff]

Sample uploaded here
https://1drv.ms/x/s!AtYrlcV2NWPFgQjHV9jDmWeZFnZj

 

[GFP1963]

That is fabulous! Thank you so much!
I really should start learning this VBA in my 'spare time'...

 

[Fluff]

Glad to help & thanks for the feedback.
VBA is a very helpful tool, but being on a Mac, I don't think you can use it to its fullest effect.

 

[GFP1963]

Sorry to bother again but now I have sheets where the company name IS on every row and I just need to add the 'record numbers'. Can do..?

 

[Fluff]

Put this formula in A2 & copy down
=COUNTIF(B$2:B2,B2)

 

[GFP1963]

Excellent! Thank you again.

 

[Fluff]

Your welcome