Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Multiplying excel rows

0

Hello everyone,

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

BMA0023

BMA0403

BMA0339

...

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:

BMA0023PH

BMA0023PA

BMA0403PH

BMA0403PA

BMA0339PH

BMA0339PA

....

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

Answer
Discuss

Discussion

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: 6102) 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: 6102) 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

Answers

0
Selected Answer

Magg

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
Discuss

Discussion

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: 6102) 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: 6102) 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
Great.

Oops! I'll remove reference to question 2 in my Answer.
John_Ru (rep: 6102) 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