Macro to duplicate values/rows based on cell value

Freddyfroo · Jun 6th 2017

Good morning,

I hope someone can help me - I have scoured the net for a macro that results in the below - I have come across many different threads with this sort of stuff but not quite what I want.

Input table

[TABLE="width: 500"]

[tr]

[td][/td]

[td]

A

[/td]

[td]

B

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

Manpower Required

[/td]

[td]

QTY Required

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

Supervisor

[/td]

[td]

2

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

Cleaner

[/td]

[td]

5

[/td]

[/tr]

[/TABLE]

Output table - (Desired outcome)

[TABLE="width: 500"]

[tr]

[td]

Manpower

[/td]

[td]

NO

[/td]

[td]

Name

[/td]

[/tr]

[tr]

[td]

Supervisor

[/td]

[td]

1

[/td]

[td][/td]

[/tr]

[tr]

[td]

Supervisor

[/td]

[td]

2

[/td]

[td][/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

1

[/td]

[td][/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

2

[/td]

[td][/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

3

[/td]

[td][/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

4

[/td]

[td][/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

5

[/td]

[td][/td]

[/tr]

[/TABLE]

Basically in the output table the rows are duplicated and the number increases incrementally based on the qty - I then simply have to type in the name of the particular person.

The input table could contain anything from 1 line of manpower entries to up to 20 entries.

Hope I am making sense and I hope someone can help me?

I am currently using Excel 2010.

Thanks

BaraaKhalil · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

Hello Freddy
Have a look at this sample file

Freddyfroo · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

Hi BaraaKhalil - thanks a lot for your reply, I have opened the document and the tables are correct however there is no macro to run (unless I am missing something?).
Again, appreciate your time.
Thanks

BaraaKhalil · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

In the attachment the solution is by array formulas .. Have a look at the formulas
If you need macro,then try this code

Code

Sub Test()
    Dim i   As Long
    Dim j   As Long
    Dim x   As Long
    Dim r   As Long




    r = 2




    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 2) <> "" And IsNumeric(Cells(i, 2)) Then
            x = Cells(i, 2).Value
            For j = 1 To x
                Cells(r, 9).Value = Cells(i, 1).Value
                Cells(r, 10).Value = j
                r = r + 1
            Next j
        End If
    Next i
End Sub

Display More

Regards

Freddyfroo · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

Apologies BaraaKhalil I never noticed the formulas I just set off looking for a macro. Anyway, a big thank you both solutions work a treat.
Really appreciate your expertise.
Thank you.

BaraaKhalil · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

Never mind Freddy
You're welcome. Glad I can offer some help
Regards

Freddyfroo · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

BaraaKhalil, I hope you can help me further as I seem to have hit a slight problem.

Is there a way either the formula or macro can be a bit more dynamic - for example I have attached the document with an additional column (Name) on the output table in order to allocate a person.

The problem is though if I was to add an additional supervisor (make the supervisor 3 in the input table) the output table adds an additional supervisor pushing the 2 cleaners down but leaving the names static. So on the attached currently if you make the supervisor required 3 it leaves Jim in there but as supervisor 3 when he needs to stay as Cleaner 1. Does this make sense? can you help further?

Thanks a lot, and sorry if I am making your head hurt.forum.ozgrid.com/index.php?attachment/72681/

****Edited with file attached****

BaraaKhalil · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

I am afraid I can't get the problem well .. Can you attach the desired output?

Freddyfroo · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

I will try my best to clarify below......

Currently
Input Table

[TABLE="width: 500"]

[tr]

[td]

Manpower

[/td]

[td]

Qty Required

[/td]

[/tr]

[tr]

[td]

Supervisors

[/td]

[td]

2

[/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

2

[/td]

[/tr]

[/TABLE]

Output Table (Desired Outcome - which I already have using the original formula/macro)

[TABLE="width: 500"]

[tr]

[td]

Manpower

[/td]

[td]

No

[/td]

[td]

Name

[/td]

[/tr]

[tr]

[td]

Supervisor

[/td]

[td]

1

[/td]

[td]

Bill

[/td]

[/tr]

[tr]

[td]

Supervisor

[/td]

[td]

2

[/td]

[td]

Ben

[/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

1

[/td]

[td]

Jim

[/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

2

[/td]

[td]

Bob

[/td]

[/tr]

[/TABLE]

Scenario......
Say I now want to add another Supervisor in the input table to make it 3 supervisors....

Input table
[TABLE="width: 500"]

[tr]

[td]

Manpower

[/td]

[td]

Qty Required

[/td]

[/tr]

[tr]

[td]

Supervisors

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

2

[/td]

[/tr]

[/TABLE]

Desired Output
[TABLE="width: 500"]

[tr]

[td]

Manpower

[/td]

[td]

No

[/td]

[td]

Name

[/td]

[/tr]

[tr]

[td]

Supervisor

[/td]

[td]

1

[/td]

[td]

Bill

[/td]

[/tr]

[tr]

[td]

Supervisor

[/td]

[td]

2

[/td]

[td]

Ben

[/td]

[/tr]

[tr]

[td]

Supervisor

[/td]

[td]

3

[/td]

[td][/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

1

[/td]

[td]

Jim

[/td]

[/tr]

[tr]

[td]

Cleaner

[/td]

[td]

2

[/td]

[td]

Bob

[/td]

[/tr]

[/TABLE]

So now I have added an extra supervisor I want the output table to insert 'Supervisor 3' in the right place but move the cleaner rows down so that the names I have allocated move down aswell. In the current version if I add an extra supervisor Jim becomes supervisor 3 and Bob is cleaner 1 with cleaner 2 empty.

I hope this make sense?

Thanks a lot

BaraaKhalil · Jun 6th 2017

Re: Macro to duplicate values/rows based on cell value

Hello try this code (It is not for me of course)

Code

Sub Test()
    Dim r   As Long
    Dim m   As Long
    Dim s   As String
    Dim n   As Long
    Dim c   As Range
    Dim v   As Long
    Dim i   As Long




    Application.ScreenUpdating = False
        m = Range("E" & Rows.Count).End(xlUp).Row
        
        If m > 1 Then
            For r = m To 2 Step -1
                s = Range("E" & r).Value
                If s <> Range("E" & r - 1).Value Then
                    Set c = Range("A:A").Find(What:=s, LookAt:=xlWhole, SearchDirection:=xlPrevious)
                    If c Is Nothing Then
                        Do
                            Range("E" & r).Resize(1, 3).Delete Shift:=xlShiftUp
                        Loop Until Range("E" & r).Value <> s
                    End If
                End If
            Next r
        End If
        
        m = Range("A" & Rows.Count).End(xlUp).Row
        
        If m > 1 Then
            For r = 2 To m
                s = Range("A" & r).Value
                n = Range("B" & r).Value
                Set c = Range("E:E").Find(What:=s, LookAt:=xlWhole, SearchDirection:=xlPrevious)
                If c Is Nothing Then
                    Set c = Range("E:E").Find(What:="*", LookAt:=xlWhole, SearchDirection:=xlPrevious)
                    For i = 1 To n
                        c.Offset(1, 0).Resize(1, 3).Insert Shift:=xlShiftDown
                        c.Offset(1, 0).Value = s
                        c.Offset(1, 1).Value = n - i + 1
                    Next i
                Else
                    v = c.Offset(0, 1).Value
                    If v > n Then
                        c.Offset(n - v + 1, 0).Resize(v - n, 3).Delete Shift:=xlShiftUp
                    ElseIf v < n Then
                        For i = 1 To n - v
                            c.Offset(1, 0).Resize(1, 3).Insert Shift:=xlShiftDown
                            c.Offset(1, 0).Value = s
                            c.Offset(1, 1).Value = n - i + 1
                        Next i
                    End If
                End If
            Next r
        End If
    Application.ScreenUpdating = True
End Sub

Display More

Freddyfroo · Jun 7th 2017

Re: Macro to duplicate values/rows based on cell value

BaraaKhalil thats cracked it - exactly what I was after - thank you very much for the time taken to help on this.

BaraaKhalil · Jun 7th 2017

Re: Macro to duplicate values/rows based on cell value

You're welcome. Glad it worked for you
Best Regards

Freddyfroo · Jun 7th 2017

Re: Macro to duplicate values/rows based on cell value

BaraaKhalil,

I have had to insert 3 rows above the table headers so the input table starts at A4 (as opposed to A1) and the output table starts at E4 (as opposed to E1). Subsequently the macro no longer works.

What part of the code needs tweaking to mitigate these extra rows?

Thanks again.

BaraaKhalil · Jun 7th 2017

Re: Macro to duplicate values/rows based on cell value

Try change in this line

Code

If m > 1 Then        For r = 2 To m

Replace 2 with 5 as 5 is the new start row for data

Freddyfroo · Jun 8th 2017

Re: Macro to duplicate values/rows based on cell value

Thanks once again - tweaked this and now works as I want.

Macro to duplicate values/rows based on cell value

Participate now!

Share