Formula for connecting a persons name to several dates to show how many times they have stayed.

Chandler8

New Member
Joined
Jun 29, 2022
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey guys if I could get some help with a formula that would be great! So basically what I need is "Our policy is 2 different stays maximum in a lifetime" So I have a check_in 1, check_out 1, check_in 2, check_out 2 box in excel. What I need is a formula that basically grabs anytime that their name is mentioned on the guest_Info sheet; it pulls the check in and check out days per person to be listed on the guest list. If that makes sense. Here is some mini sheets to take a look. I assume its going to be a v-lookup formula but not totally sure. And you may not understand what I'm saying, but here it is!

Rental Arrivals.xlsm
ABCDEFGHIJ
3Check_InCheck_Out#_of_DaysPropertyLease_HolderRegistered_Guest1Registered_Guest2Registered_Guest3Registered_Guest4Registered_Guest5
45/19/20225/21/20222R Hart: 152 Island Drive, Unit BCatherine FryeCatherine FryeRick Jones
55/21/20225/22/20221Leary: 170 Loch Dornie, Unit EGreg ProctorGreg ProctorTrina ProctorMark PooveySharon Poovey
65/26/20225/30/20224Laskin: 1236 Mountain SpringsNeal BlindeNeal BlindeJessica BlindeArden BlindeNorah BlindeGraham Blinde
75/26/20225/31/20225L. Hart: 934 Hillside DriveStacie MenichellaStacie MenichellaDan MenichellaBryce MenichellaBrandon ArtiquaLauren Artiqua
85/27/20225/30/20223Belk: 987 Mountain SpringsDave PowellDave PowellMeredith PowellPorter PowellRivers PowellElla Powell
95/27/20225/30/20223Young: 282 North WildflowerDavid TaylorDavid TaylorElizabeth TaylorBroughton TaylorWilliam Taylor
106/17/20226/19/20222Sewell: 210 Loch Dornie, Unit BMichael PattonMichael PattonAmy Patton
116/18/20227/1/202213Laskin: 1236 Mountain SpringsAnne WarrenAnne WarrenEllie WarenTimothy WarrenClaire WarrenJeff Warren
126/19/20226/22/20223Porterfield: 161 Mountain ViewPatrick WalshPatrick WalshEmily WalshMichael WalshMartha WalshPatrick Jr. Walsh
136/22/20226/26/20224Leary: 170 Loch Dornie, Unit EMark FrischMark Frisch
146/23/20226/26/20223Porterfield: 161 Mountain ViewAnne McGowanCam FindlayAmy FindlayDavid FindlaySusan FindlayKathryn Beiser
156/23/20226/30/20227L Hart: 934 Hillside DriveHans Stig MollerHans Stig MollerNancy Stig MollerCatherine Stig MollerRita Stig MollerJens Stig Moller
Guest_Info
Cell Formulas
RangeFormula
C5:C15C5=(B5-A5)



Rental Arrivals.xlsm
ABCDEFGH
3Guest_NameGuest_Last_NameCheck_In_1Check_Out_1Check_In_2Check_Out_2Sponsor NameLease_Holder
4#VALUE!
5#VALUE!
6#VALUE!
7#VALUE!
8#VALUE!
9#VALUE!
10#VALUE!
11#VALUE!
12#VALUE!
13#VALUE!
14#VALUE!
15#VALUE!
Guest_List
Cell Formulas
RangeFormula
B4:B15B4=RIGHT([@[Guest_Name]],LEN([@[Guest_Name]])-FIND(" ",[@[Guest_Name]]))



You can ignore the B column in Guest_List. Its C4,D4,E4, and F4. Filled all the way down. This will tell me if the person has had their 2 maximum lifetime stays! Once again the Guest_Info list starts at F4:V1000 for future entrys! Thanks for taking a look.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top