Forum Discussion
formula to Check if cell value matches with Dynamic Row Number.
Please test the attached version.
Let me see if I can clarify.
We have to create a daily report for our Courtesy Escort Team so they know:
- Which Guests are leaving,
- Which Guests are staying in the same site,
- Which Guests are going to stay but have to change sites because their current site was previously reserved by another Guest,
- Which new Guests are coming in and their site.
When a customer decides to stay additional days, we have two to choices in our system.
- If their current site is available for the length of time they want to stay, the Report needs to show “Extended Stay”.
- If their current site has been previously reserved by another Guest and is not available for them to stay, they will have to move. The Daily Report needs to show which site they are on now and which site they are moving to. The Report needs to show “Move from “Old Site#” to “New Site Number”.
In addition, the Guest will often make reservations under the same name for multiple sites. This is the part that is causing my issue.
On the DR/AR Report,
"Evans, Tony" originally reserved two sites under the same name.
The day he was to leave he shows up on the Departure Report, (DR).
But since he wants to stay longer but needs to move, our system requires a new reservation for the length of his stay. He then also shows up on the Arrival List, (AR).
Now the DR shows he is leaving his original two site (64 and 63) and his new reservation shows up on the AR, for the new sites (121 an 132). The report needs to show:
Criss, Lisa “Extended Stay” (staying in the same site)
Davis, Connie “Extended Stay” (staying in the same site)
Evans, Tony Move from 64 to 121 <<<<<<<<<<<<<<<<<<
Evans, Tony Move from 63 to 132 <<<<<<<<<<<<<<<<<<
They only way I could find to check for duplicates on DR and AR is Match, which only gives me the first new site of 121 for both of Evans, Tony instead of 121 and 132.
I so appreciate your time in assisting me. And I hope this helps to clarify my dilemma.
Yes, I understood that. But why wouldn't the solution be
Evans, Tony Move from 64 to 132 <<<<<<<<<<<<<<<<<<
Evans, Tony Move from 63 to 121 <<<<<<<<<<<<<<<<<<
- I sincerely apologize, when I reviewed the solution you proposed, I didn't see the countif() statements which makes it dynamic. Your solution is spot on and I appreciate all of your assistance. Please keep doing what you are doing, it is so helpful. Best Regards.
Did you have a specific reason for doing it completely different from what I proposed in my previous reply?
I filtered your raw data for the guest name "Evans, Tony":
It appears that he checked in at two different sites on the same day and, on each occasion, departed that day from a different site. I suspect more precise timing information is required to make sense of that in order to return the information you require.
I don't believe I have yet explained my needs correctly. Attached is the final Report I need to have with the ar_site being dynamic, but as illustrated, the Guest Evans, Tony shows 121 on both occurrence with my current formula. I just need a work around so it gives the ar_site for each occurrence. Thank you.
Please test the attached version.
- I am not sure how to get site 132 to return. it always returns 121 twice.
