Multiplying excel rows


Hello everyone,

I have a long list of randomized samples (about 2000) that look like this:





Now I want to multiply each row because there are subsamples of each sample, but not change the order, so that it looks like this:








The attachements PH and PA and everything else are easy to add, I just have problems "multiplying" the rows, so inserting a copy (or two depending on the sample) of each row beneath it.

Is there a way/formula to do that? I did not find anything without changing the order.

I attached an example file, in sheet two you have the complete list of samples, in sheet one you can find a short example of how it should look like. So for every sample that is "Bombus lapidarius" I want to have 3 rows and for "Episyrphus balteatus" 2 rows.

Best wishes and thank you



Hi Magg and welcome to the Forum

I don't have much time today but  question 1 needs a VBA solution and I guess you haven't used VBA, right? If a  new row were inserted per sample, would you want the new row to start differently e.g. - BMA0023 or BMA0023 (sub) and would you want to repeat any columns? Please go back to your original question and Edit it to clarify (don't use Answer!). If possible please use the Attach Files button to add an Excel file (with your actual detailed data removed if necessary). 
John_Ru (rep: 1267) Jun 8, '21 at 7:41 am
On question 2, you'll find the answer in Don's tutorial here: Increment a Value Every X Number of Rows in Excel. Once you've got your repeated list, you can copy then use Paste Values to "set" the values.
John_Ru (rep: 1267) Jun 8, '21 at 8:36 am
Thx for the reply John, I will edit the question ASAP, I did not use VBA yet.
magg3rs (rep: 2) Jun 8, '21 at 9:09 am
Add to Discussion


Selected Answer


I've assumed this is a one-off event but have a provided solution using VBA (the built-in programming language in Excel). That isn't done normally but bees are important so you're getting extra help to work with them!

Your file has some links (but I don't know what they do sorry). To use the attached file, you will need to enable macros (if requested). On your sheet2 (now called "Pollinator IDs" but you can rename it) you'll see a yellow button. Behind that is the code below- which has been commented (dark red on my screen) to explain what's going on. Click the Yes button in the message box which appears and it will replace what's in sheet 1  (now called "Sub-samples" but you can rename it) with the slide details you want, in the order they appear on the Pollinator IDs sheet.

Note that if there's a typo in the species, only a single row will result. You can create an error (in the first few rows say) click the button and see what happens then just correct the deliberate errors and re-run.

Hope this works for you...

Private Sub CommandButton1_Click()

Dim LstCell As Long, Rw As Long, OutRw As Long
Dim BCount As Long, ECount As Long
Dim Spec As String, PollID As String

'initialize slide counters...
BCount = 0
ECount = 0

' Check if user wants to proceed...
r = MsgBox("Sure you want to clear " & Sheet1.Name & " sheet and replace details?", vbYesNo)
If r = vbNo Then Exit Sub

LstCell = Range("B" & Rows.Count).End(xlUp).Row 'find last row for IDs

Sheet1.Range("A2:D" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).Value = "" 'clear output range

For Rw = 2 To LstCell 'loop through all ID rows
    Spec = Range("B" & Rw).Value 'get species
    PollID = Range("B" & Rw).Offset(0, -1).Value 'get ID number
    OutRw = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1 'find next free row in output sheet

    Select Case Spec ' do things dependent on species...

        Case "Bombus lapidarius" 'if this then...
            BCount = BCount + 1 'increment slide number for species
            Sheet1.Range("A" & OutRw).Value = PollID & "PH" ' add three sample values
            Sheet1.Range("A" & OutRw + 1).Value = PollID & "PT"
            Sheet1.Range("A" & OutRw + 2).Value = PollID & "PA"
            Sheet1.Range("B" & OutRw).Resize(3, 1).Value = PollID ' add ID
            Sheet1.Range("C" & OutRw).Resize(3, 1).Value = Spec ' add species
            Sheet1.Range("D" & OutRw).Resize(3, 1).Value = "B" & BCount 'add slide number

        Case "Episyrphus balteatus" 'if this then as above but...
            ECount = ECount + 1
            Sheet1.Range("A" & OutRw).Value = PollID & "PH" 'just two row
            Sheet1.Range("A" & OutRw + 1).Value = PollID & "PA"
            Sheet1.Range("B" & OutRw).Resize(2, 1).Value = PollID
            Sheet1.Range("C" & OutRw).Resize(2, 1).Value = Spec
            Sheet1.Range("D" & OutRw).Resize(2, 1).Value = "E" & ECount

        Case Else 'if there's a typo, identify what and where
            Sheet1.Range("A" & OutRw).Value = "Species " & Spec & " not recognised for " & PollID & " at row " & Rw
    End Select

Next Rw

MsgBox "All sub sample details added to " & Sheet1.Name & " sheet" 'tell users it's done

End Sub


P.s. I think your example slide numbering for Bombus lapidarius was wrong (you incremented it every 2 sub-samples, not 3) but I've corrected that in the macro.
John_Ru (rep: 1267) Jun 8, '21 at 2:59 pm
Thanks for selecting my Answer Magg! Hope it worked well for you. (I just corrected a typo in it)
John_Ru (rep: 1267) Jun 9, '21 at 5:13 am
Hey John,

Thanks a lot it worked perfectly just as I wanted, I thought I had to do everything manually. I removed question 2 because there are actually solutions in the internet, I just didnt know how to look for that. And sorry for the links, I dont know what they do either??
magg3rs (rep: 2) Jun 9, '21 at 5:35 am

Oops! I'll remove reference to question 2 in my Answer.
John_Ru (rep: 1267) Jun 9, '21 at 5:37 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login