+ Reply to Thread
Results 1 to 41 of 41

Help: matching data in sheet1 based on data on sheet2

  1. #1
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    Post 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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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?
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    Yes!

    Thank you for your help oeldere! Appreciated it very much

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

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

    Glad I could help. Thanks for the reply.

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

  5. #5
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #6
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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

    Many thanks again in advance.

    Benz
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

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

    Maybe like this.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #10
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #12
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #14
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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.

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

  16. #16
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #18
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    Quote Originally Posted by oeldere View Post
    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. #19
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #20
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #21
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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

  22. #22
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #23
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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

  24. #24
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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
    Attached Files Attached Files

  25. #25
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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.
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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

  27. #27
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #28
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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

  29. #29
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #30
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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
    Attached Files Attached Files

  31. #31
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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.
    Attached Files Attached Files
    Last edited by oeldere; 06-23-2012 at 02:23 PM.

  32. #32
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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

  33. #33
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #34
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

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

    I changed the workbook in #31.

    Thanks for your quick reaction.

  35. #35
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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
    Attached Files Attached Files

  36. #36
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #37
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #38
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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. #39
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    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

  40. #40
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    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.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  41. #41
    Forum Contributor
    Join Date
    06-23-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    127

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

    reply no 40.

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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