If then statement + X or Vlookup + Loop

[cchueh]

Hello,

I have guest name in column A, and all the dates as header rows for the date the guest stayed at the site. The "c" in each cell indicated that person stayed on that specific date, for example person G, stayed from January 2 and onwards.

Below is my input:

I would like my output to have a new column like below columns in blue with a Check In and Check Out date where it takes the first "c" in the range of row per guest to bring the header row as check in date and the last "c" as check out date. Loop is also required to be built in that would be great, in case if there is a break in between. 

Any help would be highly appreciated!!

[fmvizcaino · Accepted solution]

Hi @cchueh ,

I'm attaching here an example showing one way to solve this problem. I've tried to comment each step to make it clear.

Best,

Fernando Vizcaino

[seinchyiwoo · Accepted solution]

Hey,

You don't really need looping but just a little bit of multi-row to identify different stay of each guest.

I have attached the workflow that does what you want. 

See if you can reverse engineer and understand the whole thought process 🙂

Cheers,

Seinchyi

[cchueh]

@ fmvizcaino Thank you so much! It works!

[cchueh]

@ seinchyiwoo Thank you for including the comments for each icon to let me know how each step works!

[cchueh]

@ fmvizcaino

@ seinchyiwoo 

If I had duplicated guest names in column A, the formulas doesn't pick up the duplicated names i.e. Person A who may have almost the exact same check in dates but the formula only picks it once or doesn't pick it up, would you be able to assist?