Forum Discussion
formula to Check if cell value matches with Dynamic Row Number.
Please test the attached version.
As before:
1) I have two separate Sheets
a) Date of Departure, b) Date of Arrival
2) The Guest name can be on the same sheet multiple times with different Site#
3) The Guest can be on the Departure sheet and be staying longer so they appear on the Arrival sheet as well.
a) If they remain in the same Site# they are considered an "Extension"
b) If they want to stay but have to move to a new site they are considered a "Move" and my sheet needs to read "Move from Site# x to Site# y.
I have narrowed the options down to this formula:
=LET(
ar_site,INDEX($F$2:$F$201,MATCH($B2,$E$2:$E$201,0)),IFERROR(ar_site,""))
Where column
F - Index = Arrival Site#
B - Match = Departure Name
E - Match = Arrival Name
Issue:
If the Name appears multiple times my MATCH formula gives me the same row
number for each occurrence of the name and thereby the same Site# instead of the correct one. How can I get the correct row with multiple matches?
I will gladly upload my work if you cn direct me how to do so.
Thank you in advance for your help.
If you don't see an area below the box where you compose a reply to upload a file, you can either upload it to for example Google Drive or OneDrive, share it and post a link to the uploaded and shared file, or send me a PM (click on my profile picture) and attach the file to it.
Thank you again. I found the File Upload option after I hit the "Open full text editor" link.
This is my worksheet. The names & sites with the incorrect site number on the second ocuurance of the name are highlighted in green & yellow.
Thanks.
How can we choose between
"Move from 64 to 121 / Move from 63 to 132"
and
"Move from 64 to 132 / Move from 63 to 121"?
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.