+ Reply to Thread
Results 1 to 8 of 8

Match Multiple column contents

  1. #1
    Registered User
    Join Date
    07-08-2019
    Location
    singapore
    MS-Off Ver
    1906 Build 11727.20230
    Posts
    6

    Match Multiple column contents

    Hello Everyone.
    I am having with matching the multiple columns .
    I have a file, which shows customer record of ordering the food from tablet menu .
    I want to display the data of customer who ORDERED after SCREENSAVER is displayed.

    My data sample are as follows

    Date- Time- INFO-Table -Bill Number-Unique ID-Status- Order Status Product Name

    7/1/2019 20:43:57 INFO 1 266 16bad5278a1 ORDER 注文します。 商品:ダブルフレンチトーストセット/ホット茉莉龍珠茶/黒蜜きなこ=1
    7/1/2019 20:44:02 INFO 1 266 16bad5278a1 ORDER 注文完了 7/1/2019 8:45
    7/1/2019 21:07:05 INFO 1 266 16bad5278a1 ORDER 注文します。 商品:角ハイボール=1
    7/1/2019 21:07:10 INFO 1 266 16bad5278a1 ORDER 注文完了 7/1/2019 9:09
    7/1/2019 21:23:55 INFO 1 266 16bad5278a1 SCREENSAVER クリックされました。 MENU:651
    7/1/2019 21:24:25 INFO 1 266 16bad5278a1 ORDER 注文します。 商品:ブルーベリークリームチーズジェラート(3枚)=1
    7/1/2019 21:24:31 INFO 1 266 16bad5278a1 ORDER 注文完了 7/1/2019 9:26

    In this table Table Number 1 Have Bill Number 266 and its Unique ID is 16bad5278a1 .
    On 21:23:55 SCREENSAVER pops up at screen and the customer touches it .
    After the word SCREENSAVER there is one more order made from same customer "注文完了" means order confirmed.
    Now First of all
    -How to match the Table Number ,Bill Number and Unique ID , and display all the remaning customers "ORDER" after and before SCREENSAVER?
    I have also attached the workbook.
    What I want is to find the total number of customers who ordered after screensaver .
    Attached Files Attached Files

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Match Multiple column contents

    Format your source data as a table, then you can use Power Query (Get & Transform Data) to analyse your data, and count the customers who ordered after screensaver:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    07-08-2019
    Location
    singapore
    MS-Off Ver
    1906 Build 11727.20230
    Posts
    6

    Re: Match Multiple column contents

    OMG . It worked. I tried by adding more data which had more Order after screensaver, But in output tab you created, it still showed "1" , until I hit refreshed. It displayed the correct number.
    Thank you so much.
    I didnt know these can be done by queries , coz I dont know anything about it.
    Any good place to start learning queries from beginning?
    Thank you so much.

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Match Multiple column contents

    My canned Power Query overview:

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Match Multiple column contents

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You may also 'add reputation' to those who helped. Thanks.

  6. #6
    Registered User
    Join Date
    07-08-2019
    Location
    singapore
    MS-Off Ver
    1906 Build 11727.20230
    Posts
    6

    Re: Match Multiple column contents

    I am sorry to follow up again. Yes it did solved the problem, but can I ask one more thing here or should I open up new post ?
    If I can ask here.
    Here is my question.
    In the file you sent , How can we display the contents of Last Order after Screensaver?

    Currently, we can know the total number of Order after Screensaver.
    In Next column of `Order Status`, there is Product Name.
    So How can we only display those product name taken after screensaver?

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,427

    Re: Match Multiple column contents

    What exactly do you mean by 'screensaver'?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Match Multiple column contents

    Ah, the classic "scope creep"!

    A little tricker - we need to "partition" the data for each Table / Bill Number / Unique ID, then establish which orders were placed after a Screensaver event, then recombine and filter those orders:

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Match multiple column contents
    By blinkblink in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-09-2019, 12:27 AM
  2. [SOLVED] Show row if cell contents in column A match filter selection from column B
    By SHANNON MAUGHAN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2017, 01:28 AM
  3. [SOLVED] Trying to get a column in one table to match the contents of a column in another
    By peteypops33 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-28-2017, 11:50 AM
  4. Match cell contents with column
    By hannsg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2016, 11:23 AM
  5. Match One Column's Contents with Anothers
    By AlexNsvc in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-29-2016, 07:16 PM
  6. Replies: 6
    Last Post: 12-07-2012, 05:38 PM
  7. [SOLVED] Vlookup? Pivot Table? How do I match column A and B to contents in column C?
    By rushnit in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-08-2012, 10:16 AM

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