I've been brought on last minute to sort out tent allocation for a festival of about 1500 people. Their ticket sales are a spreadsheet on Google Drive. I have never done this kind of thing before (I'm an English teacher), but I'm good with software, generally, and have experience with basic programming functions.

Tents will be allocated based on 3-day passes (sheet 1), and 2-day passes (sheet 2). The unique identifier is ticket number, with other details in separate fields.

Sheet 3 (my job) will show the individual tents, and who is inside.

I'll allocate a tent number in a new column within the sales sheets (sheets 1 and 2 - based on customer request). I want to parse these columns to sheet 3, such that sheet 3 displays the tent number, and details of attendees inside that tent (using unique identifier of ticket number).

I'm guessing that each tent will be an array?

As an extra challenge, I want to bound the tents to limits (4-man, 6-, 10-man tent), so that if the number of occurrences of tent x exceeds its bounds, it returns an error.

  • Thanks pnuts. I guess tent limits would be held as a defined limit for each tent (as a data array)? It's an optional limit. I can overcome it by listing each tent as "tent 43 - 6 man", then if it has more than 6 entries I would manually go back and look at sales sheets. I was just thinking array limit so people dont enter excess tent allocation in main sheets. What I need to pull from sheet 1 and 2 is the occurrences of a tent number, then the attendee details of said occurrence is copied to tent sheet. Sorry, I'm new to this, but trying to describe situation in as much detail as possible.
    – Malius
    Nov 5, 2014 at 17:55
  • btw, my knowledge of the term "data array" comes from programming briefly in c, where u can set bounds on an array. I dunno how arrays work in google spreadsheets. Like I said I'm new to all this. Thanks so much.
    – Malius
    Nov 5, 2014 at 17:56
  • 1
    thx. forgot to remove details from sheet 2. all contact details should be gone now
    – Malius
    Nov 5, 2014 at 18:15
  • 1
    Thanks so much. ALthough it shows the tent number in descending order, it at least shows the allocated tents at top of sheet. Once all tents have been assigned and there are no empty cells, I can remove the desc. You've helped me alot. Thanks again.
    – Malius
    Nov 11, 2014 at 5:04
  • 1
    thanks pnuts, unfortunately I need it spelled out to me :P I'll create a separate thread so it doesn't extend further, along with some sample data Thanks again.
    – Malius
    Nov 20, 2014 at 7:47

1 Answer 1


Some finishing touches were applied that have been the subject of another question where there seems to be recognition that a a satisfactory overall conclusion has been reached. This contribution to that from this post was essentially a formula:

=query('3 Day - Friday Bus'!A5:I10,"Select I, A,B,C,D,E,F,G,H order BY I desc")  

to select data from a range in another sheet, slightly rearrange it and order it (descending to avoid blanks floating to the top).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.