Help: matching data in sheet1 based on data on sheet2

1. 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.

Benz

2. Re: Help: matching data in sheet1 based on data on sheet2

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?

3. Re: Help: matching data in sheet1 based on data on sheet2

Yes!

Thank you for your help oeldere! Appreciated it very much

4. Re: Help: matching data in sheet1 based on data on sheet2

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

5. Re: Help: matching data in sheet1 based on data on sheet2

Yup! I just did...

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

6. Re: Help: matching data in sheet1 based on data on sheet2

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

Benz

7. Re: Help: matching data in sheet1 based on data on sheet2

Maybe like this.

8. Re: Help: matching data in sheet1 based on data on sheet2

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

9. Re: Help: matching data in sheet1 based on data on sheet2

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.

10. Re: Help: matching data in sheet1 based on data on sheet2

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

11. Re: Help: matching data in sheet1 based on data on sheet2

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.

12. Re: Help: matching data in sheet1 based on data on sheet2

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

13. Re: Help: matching data in sheet1 based on data on sheet2

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.

14. Re: Help: matching data in sheet1 based on data on sheet2

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.

15. Re: Help: matching data in sheet1 based on data on sheet2

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.

16. Re: Help: matching data in sheet1 based on data on sheet2

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

17. Re: Help: matching data in sheet1 based on data on sheet2

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.

18. Re: Help: matching data in sheet1 based on data on sheet2

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. "

19. Re: Help: matching data in sheet1 based on data on sheet2

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.

20. Re: Help: matching data in sheet1 based on data on sheet2

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

21. Re: Help: matching data in sheet1 based on data on sheet2

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

22. Re: Help: matching data in sheet1 based on data on sheet2

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.

23. Re: Help: matching data in sheet1 based on data on sheet2

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

24. Re: Help: matching data in sheet1 based on data on sheet2

Hi Oeldere

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

25. Re: Help: matching data in sheet1 based on data on sheet2

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.

26. Re: Help: matching data in sheet1 based on data on sheet2

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

27. Re: Help: matching data in sheet1 based on data on sheet2

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.

28. Re: Help: matching data in sheet1 based on data on sheet2

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

29. Re: Help: matching data in sheet1 based on data on sheet2

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

30. Re: Help: matching data in sheet1 based on data on sheet2

Hi oeldere

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

31. Re: Help: matching data in sheet1 based on data on sheet2

He the formula's for the times a member came in a year (and came in total).

It is up to you to put is in a pivot table.

32. Re: Help: matching data in sheet1 based on data on sheet2

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

-benz

33. Re: Help: matching data in sheet1 based on data on sheet2

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

34. Re: Help: matching data in sheet1 based on data on sheet2

I changed the workbook in #31.

35. Re: Help: matching data in sheet1 based on data on sheet2

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

36. Re: Help: matching data in sheet1 based on data on sheet2

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).

37. Re: Help: matching data in sheet1 based on data on sheet2

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.

38. Re: Help: matching data in sheet1 based on data on sheet2

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.

39. Re: Help: matching data in sheet1 based on data on sheet2

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

40. Re: Help: matching data in sheet1 based on data on sheet2

If you want to count the day, just use that value (number_nights) in the pivot table.

41. Re: Help: matching data in sheet1 based on data on sheet2

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1