Forum Discussion
How to automatically create a variable number of rows on a linked sheet based on criteria
Hi,
I am looking for a way to automate some entries my team is making. I am dealing with a one-to-many relationship. Below is a simple example of what I currently have
TABLE 1 - Itineraries
| Date | Itinerary ID | Primary Guest | Total Number of Guests |
| 4/30/2020 | 2154-5255 | Aldous Huxley | 5 |
| 4/30/2020 | 2168-3526 | Albert Camus | 3 |
TABLE 2 - Guests
| Date | Itinerary ID | Guest Name | Age |
| 4/30/2020 | 2154-5255 | Aldous Huxley | 47 |
| 4/30/2020 | 2154-5255 | Andrea Huxley | 45 |
| 4/30/2020 | 2154-5255 | Betty Huxley | 20 |
| 4/30/2020 | 2154-5255 | Jason Huxley | 18 |
| 4/30/2020 | 2154-5255 | Agnes Huxley | 68 |
| 4/30/2020 | 2168-3526 | Albert Camus | 54 |
| 4/30/2020 | 2168-3526 | Beatrix Camus | 52 |
| 4/30/2020 | 2168-3526 | Guy Camus | 40 |
My goal: TABLE 1, the user has entered a single and unique Itinerary ID. Associated with that are a number of guests, specified in the 4th column (5 and 3, respectively).
I would like second sheet to also be created with the 1st and 2nd columns' information, and the corresponding number of rows. Essentially, setting up a guest table. So - since Itinerary ID 2168-3526 has 3 guests, the table for guests creates 3 rows. Is this possible?
3 Replies
I attempted this more as a challenge than anything else. The output is an Excel Table with conditional formatting to create the borders. There is an 'almost hidden' column that returns the primary guest name but allows it to overflow into the Guest Name column unless overwritten. I have used Office 365 using XLOOKUP but I did also implement a solution with VLOOKUP just to remind myself of what a useless function it is!
- PeterBartholomew1
wow, what a great effort my friend,, i think it is a best solution
apmulheran , Though with a series of tricky formulas this can be achieved, but i would suggest against it as the formulas are complex to understand and maintain in long term.
Attached template achieves this for upto 10 guests per 10 entries , transformation is done from Sheet1 to Sheet3 , Sheet2 is used for temporary calculation used to make Sheet3.
The whole thing breaks down if you rename Sheet2 or insert any rows or columns.
So as i said, if need is utmost, it can be achieved, but would suggest against it.
