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