I somehow got roped into trying to help the people who organise the degree ceremonies with a data problem. They have a spreadsheet of guests and a spreadsheet of seat numbers. Each guest has requested a certain number of seats - between 0 and 4. The guest name needs repeating the same number of times as the number of guests. The guests then need to be allocated to seats. At least this is the way they've framed the problem - there might be a much better way of doing it. I've had a few responses from Twitter (thanks Alan, Frances, Jake and Stuart) and I've
Googled it, but still no joy. I have discovered the repeat (=REPT) function which does repeat text by a specified number - but then joins it into a continuous string. If you have better Excel knowledge than me (not that difficult) and are willing to look at it I'd really appreciate it.
Here's the file (I've changed the names to protect identify <cue A-team music>. There are >1,500 names on the actual sheet.
Please see email for A solution
ReplyDeleteFor the benefit of anyone else looking at this, here is a macro that creates a row per ticket and repeats the surname on each row:
ReplyDeleteSub InsertSurnames()
Dim LastRow As Long
Dim r As Long
Dim surname As String
Dim tickets As Integer
Dim surnameCol As Integer
Dim ticketCol As Integer
Dim targetCol As Integer
surnameCol = 1
ticketCol = 3
targetCol = 4
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
r = 1
Do While r <= LastRow
surname = Cells(r, surnameCol).Value
tickets = Cells(r, ticketCol).Value
If (Not (Len(surname) = 0)) Then
Cells(r, targetCol).Value = surname
For x = 1 To tickets - 1
Cells(r + x, 1).EntireRow.Insert
Cells(r + x, targetCol).Value = surname
Next x
LastRow = LastRow + tickets - 1
End If
r = r + 1
Loop
End Sub
Thanks Hazel - about to have a look at it...
ReplyDelete