Tuesday, 22 June 2010

Can you help with an Excel problem?

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.


  1. Please see email for A solution

  2. For 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:

    Sub 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

    End Sub

  3. Thanks Hazel - about to have a look at it...