Help: matching data in sheet1 based on data on sheet2

Good day!

Kindly help with the attached excel file. It containts two sheet. I would like to group the guest name by Hotel Name in Sheet1 based on the hotel code. The Sheet2 contains the Hotel Name with it corresponding Hotel Code.

Any help would be very much appreciated. You may used the file and upload it with the formula.

Many thanks in advance

Benz

Is this what you're up to.

You want to find the name of the hotel on sheet 2 and place it in sheet 1?

Yes!

Thank you for your help oeldere! Appreciated it very much

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?

Yup! I just did...

And maybe one more favor if u dont mind, I have another request. pls refer below.

Hi oeldere

Hope you could help me with the file below.

In the spreadsheet, Column B shows Member and Column D shows departure date. Is there a formula to get the following:

1. No. of repeat stays based on the departure date per member
2. No. of repeat stays based on the departure date per member by year

Many thanks again in advance.

Benz

Maybe like this.

Yes! Thats it. However, will this be possible to apply lets say if members are in thousands?

Yes, that is possible.

The pivot table put every unique member (in this case) in the row.

If there are 15, there be shown 15 members.

Just try in your on exemple.

It is also possible to show (count of the members) a day.

In that case you need to add a formula the extract the day from the data.

And you can also count the min or max members per day.

Its up to you (if the format is in table-form).

Do you have questions, just ask.

Off the record
Notice my main language is not English.
I would appreciate if you help me to improve my (written) English.

Hi Oeldere

Thanks! That you mentioned above are actually some of the things that I need to present in a report. My knowledge of excel is very limited haha. Any help from you is very much appreciated.

On a side note, your english is perfect to me! English is not my native language too

Anyhow, do you have skype or any messenger ID? It is easier to communicate that way. If your not comfortable, conversation here is fine

Thank you that you think my English is good enough.

I will post it in the future, so native English people can improve my language.

On this moment i like you to communicatie on this forum.

Please give a list of wanted options.

Together with other forummembers you get an better result.

No worries

Going back, since you are aware that my excel skills is very basic, im just wondering how you made the blue column on the side with the total per year? Im sorry, i know this is very basic question

Benz

You get even better result if you learn the skills of the pivot table yourself (with the help of the forum).

It would be appreciated if your shown us the result of your studie.

Thanks for the tip.

I can actually play around with pivot tables. Its just that the membership no. are in thousands and they are mostly unique membership. So the dilemna I have, which for some maybe just a piece of cake to solve, is to actually get the no. of active members who have repetitive stays in our hotels. This will help me to basically formulate better strategies in targeting those members. This is helpful when I do market segmentation too.

The blue collumn in the pivot table are made by Excel.

If you mean the blue collumn G and H, i made them and placed a formula in it.

You will get the formula if you go to cell G2.

Please add in the reply to whom (person) of to what (table / pivot table) you're replying.

Thanks.

Yes I managed to do tha month and year formula, im referring to the table with the total per year on the right side That part im not sure how to go about it

refering to #14

maybe you can categorise them in groups (e.g. 0- 10 / 10 - 50 / 50 -100).

then you can get different market strategies for each group.

Originally Posted by oeldere

Off the record
Notice my main language is not English.
I would appreciate if you help me to improve my (written) English.

By the way, better to write it this way. " I would appreciate if you would share/show us the result of your research/study. "

Thank you for the reply #18.

I noticed you topic is still closed.

It is better to open it again.

Some member don't read closed threats.

I actually set the thread to unresolved again. Is that what you mean? I just signed up to this forum today so still getting the hang of it

Reply #13

I created a pivot table to filter membership no with repeat stay per year. Just another enquiry, is there a formula to compute the percentage of the repeat stay per year?

Thanks
benz

I see you put the question unresolved again (thank you for that => probably it was my fault).

Please give a list of wanted options. (see the #13)

You get better help if you add your workbook youre working on (without confidential information).

Then it is easier to analize your problem.

Reply to post #22

I will create a list of desired outcome and will just create a sample spreadsheet since the file size capacity is just 1mb

Reply post #22

Hi Oeldere

Please refer to the attached spreadsheet.

Just wondering if the desired outcomes below are possible.

1. Due to the large number of unique membership no, is there are simple way to compute the Percentage of repeat stay per year/ per resort / per membership level?

Many thanks
Benz

Is this youre desired outcome.

Notice i don't have it in %.

In don't know yet, how to add them.

But, as always, you start at the begin.

Reply post no. 25

Thanks for this. I can actually start with this and play around. You have been very patient and helpful. Definitely you are a big help to this online forum. I hope I can ask assistance from you for my future report. My upcoming project/study is accuracy forecasting haha wish me luck!

-Benz

Thanks for the compliments.

On this moment
Try to work with the pivot table for yourself and if you have any (further) questions, jusk ask.

Reply post no. 27

I have my pivot tables and let say that I was able to generate the numbers that I needed. Now I am creating another sheet that will serve as a dashboard, like a representation of the numbers I got from pivot table. Is there a way to link the total number to that sheet? Instead of creating the pivot table on the same sheet? I hope it makes sense haha Its like the number will auto populate to the fields in the dashboard sheet

As already mentioned, you get better help if you give us an example of your workbook, with the desired result.

Reply post #29

Hi oeldere

Sorry, I forgot about that part. Please refer to the attached sample spreadsheet below.

Desired Outcome:

Sheet 1
Due to the large amount of membership count in my pivot table, is there a formula to count the number of members who stayed once in 2010, then 2x and 3x and 4x and so forth in the same year. Same goes for 2011 and 2012.

Would like also to have a count of:
1. members who stayed in our hotel in 2010 and came back in 2011.
1. members who stayed in our hotel in 2010, 2011 and 2012.

Many thanks
Benz

reply to post #31

Thanks oeldere, however, i noticed that the file you attached is the same one that I uploaded. No formula included?

-benz

post no. 31

Was thinking of count function, to count all the 1 stay, and 2 stay, and 3 stay in every year (2010,2011,2012). Thats just my idea, hahaha not sure though

I changed the workbook in #31.

Thanks for your quick reaction.

reply post no. 34

Im sorry but Im not really sure if thats the count I need. I attached the spreadsheet and created a table below the pivot. I have entered the desired count no. manually. Wondering if you could assist to create a function to get that count no.

Many thanks

For the future, please use the last example to add new things into.

In that way, we don't loose information (formula's) you could need for future use.

I appreciate you added you value manualy.

But I have no idea witch value you count (which collumn / under whitch conditions).

post no. 36..

Im sorry for the confusion, that value are all based in the pivot table. For column 2010, there's two 1 stay, then one 3 stay and one 5 stay. Thats how i come up with the value.

The same thing for column 2011, theres five 1 stay and two 2stay. hope this clarified the confusion.

No, it didn't clarified.

The result of member 8815879251 are 1, 3 and 5.

This means 1 stay in that year.

It don't say anything about the amount of days they stayed. (that could be 30 days).

Maybe i'm messing things up, but you know what you want to resolve.

reply post no.38

I understand where you are coming from.

How I come up with the value is actually simple, for 2010 column, i just sum up all the 1x stay manually. If you notice in the pivot table, 8567232761 and 8910102045 are the two member who had one stay in that year.

I think i confused you with the label no. of stay

reply no 40.

I Will. Thanks for the help and the patience. I have learnt a lot today.