Insert Rows automatically

[Excel Rocks]

Hi Excel Gurus!

Currently Consultants bill their hours every month only if their working hours is greater than 0.
To better visualize the data in graphs, we would like to normalize the data, by capturing (create rows) consultant details even if they haven't worked for a certain month. We would add 'zero' hours if they haven't worked. Can this be automated in EXCEL ?

Any Help is greatly appreciated. Thanks!

Insert excel rows automatically.PNG

[Ochimus]

Enter the names of your consultants into Col A of Ranges sheet, and name range "Consultants"

Following macro then adds the list automatically to next free row of table, assigning Zero hours to them. You then "overwrite" any that have worked.

Option Explicit

Sub AllIn()

Dim Consultants As Range, c As Range
Dim a As Long, f As Long, n As Long

With Sheet2
f = .Cells(.Rows.Count, "A").End(xlUp).Row
Set Consultants = .Range(.Cells(2, 1), .Cells(f, 1))
End With

With Sheet1
a = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
If a = 3 Then a = 2
Consultants.Copy Range("C" & a)
n = .Cells(.Rows.Count, "C").End(xlUp).Row
For Each c In Range("D" & a, "D" & n)
c = 0
Next

End With

End Sub

Hope this helps

Ochimus

[Excel Rocks]

Hi Ochimus, First of all I would like to say Thank You for your response.

I think your solution would be best suited for any new entries.Meaning we could define range for all "Consultants" that have been working for a company and add hours every month by running your macro. Awesome!

But since we are trying to normalize the historical data. It would be harder to define consultant range, as new consultant's might keep adding every month and we do not want them to be populated prior to their first entry. (I'm not sure if that makes sense). So is it possible to dynamically lookup to a range and create unique Consultants ?
Dynamic range: In the image below, when I create records/rows for Feb month(BLACK arrow), I would need to lookup to Prior month's Consultants and create unique record for missing consultant with ZERO hours.

In simple words would it be possible to literally translate 'Current state' table to 'Future state' Table ?
Insert excel rows automatically v2.PNG

[Ochimus]

Excel Rocks,

Thanks for the clarification.

To get a "retrofit", you're going to need the "Consultant" database to know when they started, any months they were not available, and when they left the organisation. Macro can then look at the month in Col B, establish which consultants should have been included in that period, add those that aren't and give them Zero hours, resort the month and move on to the next block?

And do all the Consultants work for "Company 1", or could you have multiple company details in any month?

Ochimus

[Excel Rocks]

Ochimus- Would you be able to Help with this?

Thanks!

[Excel Rocks]

You are Right.

"Consultant" database to know

1. when they started,
2. any months they were not available,
3. and when they left the organisation (THIS MIGHT NOT BE TRUE.There is no question of leaving a company. {Its kinda weird, I took a bad example to represent the scenario. But lets stick to this for now}).

Do all the Consultants work for "Company 1", or could you have multiple company details in any month?

1. We have multiple companies(Company 1, Company 2, so on). A Consultant may work for multiple companies at a time and we keep track of all consultants working for with any companies. I guess to simplify things we could concatenate "Company+Consultant" to get a unique entity working in a month

[ATTACH]Automatically Insert Rows in Excel.xlsx[/ATTACH]

Insert excel rows automatically v3 multiple companies.PNG