Create List of Tickets

[carling73]

Hey, so i'm trying to find a way of keeping a "live" list of what tickets are held by a customer at any one time so -

There are 100 tickets numbered 1-100

Bob holds tickets 1,5,10, 15, 20, 25 and has recorded these tickets in A2, B2, C2, D2, E2, F2

Bob is looking to trade tickets with Freda

Bob records the tickets he's offering in A3, B3, B3, (Ticket numbers 1,5,10)
Bob records the tickets Freda is offering in A4, B4 (Ticket numbers 16, 23)

How does Bob write a formula to amend the record in A2:F2 to show 15, 16, 20, 23, 25, Blank instead of 1, 5, 10, 15, 20, 25

Bob will go on to make multiple trades and wants to keep the list upto date in line 2

Hops that makes some kind of sense, Thanks all

 

[Marc L]

Hi, according to Excel basics a VBA demonstration for starters :​

Sub Demo1()
    Dim A$, C%, V
        A = "A3:" & Cells(3, Columns.Count).End(xlToLeft).Address
        C = Cells(4, Columns.Count).End(xlToLeft).Column
        Application.ScreenUpdating = False
    With Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Columns
         Range(Join(Evaluate("IF({1},ADDRESS(2,MATCH(" & A & "," & .Address & ",0)))"), ",")).Clear
         [A4].Resize(, C).Cut .Cells(.Count)(1, 2)
        .Resize(, .Count + C).Sort .Cells(1), 1, Header:=2, Orientation:=2
    End With
        Range(A).Clear
        Application.ScreenUpdating = True
End Sub

 

[Marc L]

​

I forgot to remove at the end of the Dim codeline , V …​

 

[carling73]

Marc L, thats amazing thanks mate, really appreciated THANK YOU!