Need Formula help

New Contributor

Hi everyone! 

 

Looking for a bit of help with a work situation. It's a hard thing to explain so hopefully you understand what I'm trying to do. I have a stadium that's over 3000 seats. I need to list every seat 1 by 1 in an excel sheet. So what I already have is an excel sheet with each section, row and the seat number that the row starts on and the seat number the row ends on. Is there a formula or a way to spilt everything out. For example Excel Help.JPG this is what my first row looks like. How can I take E2 (6) and F2(20) and number the next couple of rows 6-20 without inserting 20 rows and using the fill function. If I need to go into more detail or explain it again I will try my best to. 

7 Replies

@rp247 

You've given us, I suspect, only a part of the actual big picture. Your end goal surely is not to simply have an Excel spreadsheet that somehow lists every seat one-by-one. THAT description is one part of a process to accomplish something else, surely. 

 

So why don't we begin with describing that "something else":

  • What is the long-term purpose or need you are wanting Excel to help you meet?
  • What will be the information you need to organize in order to meet that need?
    • The array of seats you've asked about is one (and only one) component there. What else?
    • Clients/customers who purchase seats, season tickets (?)
    • multiple seats needing to be assigned contiguously (?)
    • etc
@mathetes
The long term purpose is to create a seat manifest that will go to another company with every seat one by one in a stadium. That's it there's nothing else. We aren't assigning seats or anything right now. This is step 1. I have 378 rows that look just like the one above and I just need to be able for each row to show just 1 seat in it. So the first row would be A 3 6 20 15 and the next row would be A 2 7 20 15

@rp247 

 

I have 378 rows that look just like the one above and I just need to be able for each row to show just 1 seat in it. So the first row would be A 3 6 20 15 and the next row would be A 2 7 20 15

 

And the third row would be?  

A 1 8 20 15           (?)

Somehow I doubt it. Since that would mean the fourth row would be A  0 9 20 15

 

In other words, the sequence/pattern ain't clear in what you've told us.

 

The long term purpose is to create a seat manifest that will go to another company with every seat one by one in a stadium. That's it there's nothing else. We aren't assigning seats or anything right now.

 

You may not have any longer term purpose, but that other company surely is doing something with this other than framing it as a work of art to hang on the wall. To know the ultimate purpose, even if it's the purpose of that other company, would still be helpful. Or have they asked for it to be in the format you've described? If so, please make more clear what that format is, what the sequence of numbers is going to look like. You know it, it may seem obvious to you, but I've not been able to discern the pattern from the two rows you've given.

@mathethes Sorry typo. The second row would be A 3 7 20 15, Third row would be A 3 8 20 15; fourth row would be A 3 8 20 15 fifth row would be A 3 9 20 15 and so on until I hit A 3 20 20 15. Then it'll start with A 4 6 18 13, then go A 4 7 18 13 and so on. They are making a seat by seat manifest. The long term purpose has nothing to do with excel they'll important it into another site and create a model. But again the first step getting 1 seat in each row. The rest they'll worry about.

@rp247 

Not to be too nit-picky, but I think there's another typo in there. 

The second row would be A 3 7 20 15, Third row would be A 3 8 20 15; fourth row would be A 3 8 20 15 fifth row would be A 3 9 20 15 and so on...until I hit A 3 20 20 15.

 

That typo aside, I get that pattern. You just increment the second number by 1 in each successive row.

 

Then it'll start with A 4 6 18 13, then go A 4 7 18 13 and so on

 

Where do these numbers come from??!! The A 4 part I get. We hit the end of the A 3 set, so go to A 4. Even the 6 makes sense; it's just starting the 6-20 sequence from the first set.

 

BUT why in heaven's name is the third number in the sequence 18, the fourth number 13? Why aren't they 20 and 15 as they were in the first set? What is the relationship there? What happens when we get to the next set?

 

Again, you know your pattern of your seat numbering. But you aren't explaining the underlying heuristic here, the rule(s). You're taking them for granted, I suspect. This may even be common knowledge to people who frequent stadiums or venues with 3,000 seats. I don't do that often enough for it to be common knowledge with me. Yet you're asking for help in programming and programming requires clear heuristics.

 

I've created a starter spreadsheet, attached. Assuming you know a bit about Excel formulas, you can see how I've taken the rules that have made sense and written them in. There are two cells with light yellow backgrounds in them for you to write in the IF functions that will go to 18 and 13 in their respective cells.

@mathetes 

 

Where do these numbers come from??!! The A 4 part I get. We hit the end of the A 3 set, so go to A 4. Even the 6 makes sense; it's just starting the 6-20 sequence from the first set.

 

What row 2 is telling you is it's Section A row 3 that row starts with seat 6 and ends with seat 20. Which means there are 15 seats in that row. So A 3 6 20 15. The same going forward. The picture that I've attached is an example of what my current sheet looks like. So it doesn't have any blank rows in between. So I'm looking for a way to formulate something that also doesn't involve me having to manually add the rows in if I don't have to.  Does this help at all? If not then I get it, I'll use another program to try to do it and just transfer it to an excel file. Practice 4522.JPG

@rp247 

 

Does it help at all? Yes, I guess. It shows me that there probably is a meaningful relationship between the rows of that spreadsheet.  What it doesn't show me is what that meaningful relationship is. That's where you come in. 

  • I can see how the "num seats" column can be derived from the two adjacent columns. There is a discernible relationship, a recognizable mathematical relationship.
  • What is decidedly NOT apparent is the way the numbers progress, why they progress the way they do, in column C ("row name"). They go sequentially from 3 through 8, then two each 9s, 10s and 11s; then three 12s.
  • What is also not apparent is how the numbers in columns D and E progress. I.e., what is the basis for any pattern or formula that could be written. 
  • It's also evident from your data that sections A and B don't follow the same patterns within their similarly named rows.

Do you see the difficulty? Unless you can explain--EXPLAIN, not just show an existing partial sheet that raises more questions than it answers--Unless you can explain the underlying relationships there, it's awfully hard to write formulas that can fill in the rest.