+ Reply to Thread
Results 1 to 10 of 10

Formula to match and merge data with Excel/VBA

  1. #1
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Question Formula to match and merge data with Excel/VBA

    Hi Gurus,

    Would need your kind expertise to help on this request.

    Assuming I’ve the following sample data (SampleData1.xlsx) and wishing if the output data could be formatted using some kind of excel formulas/VBA coding.

    For example:
    Sheet1 (Sample Data)
    It contains a simple matrix showing list of students and subjects that they've selected, marked with "X".

    Sheet2 (Desired Output)
    This is a sample output showing students and subjects that they've selected, without the mark "X" and any blank space in between.
    The sample data could be long and massive, so it's best if the formula can be made flexible to cater for this.

    Appeciate for any of your help and advice.

    Thank you a lot in advance.


    - Jack
    Attached Files Attached Files
    Regards,
    Jack

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Formula to match and merge data with Excel/VBA

    If you're happy to copy the student names into the target sheet in column A then you could use this in B1 and copy across/down as necessary:

    Please Login or Register  to view this content.
    Enter using Ctrl+Shift+Enter to create an array formula. Alternatively, you can generate the sheet from scratch using this VBA code:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Re: Formula to match and merge data with Excel/VBA

    Hi WBD,

    Great, tested working as expected.

    Appreciate for your kind help.


    - Jack

  4. #4
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Question Re: Formula to match and merge data with Excel/VBA

    Hi Gurus,

    Need some help to change its output from vertical instead of horizontal.

    See attached sample. Thanks.


    - Jack
    Attached Files Attached Files
    Last edited by SmallJack; 02-09-2020 at 11:43 AM. Reason: update information

  5. #5
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Formula to match and merge data with Excel/VBA

    This thread has not been closed. You just need to remove the SOLVED tag. You should continue here, please - your duplicate thread will be closed.

    EDIT:
    I have removed the SOLVED tag for you.
    Last edited by AliGW; 02-09-2020 at 11:41 AM.
    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.

  6. #6
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Re: Formula to match and merge data with Excel/VBA

    Hi Ali,

    Okay, thanks for your help.


    - Jack

  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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Formula to match and merge data with Excel/VBA

    Here's a PowerQuery solution.

    M Code:

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

  8. #8
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Re: Formula to match and merge data with Excel/VBA

    Hi Ali,

    Yes, I've checked that, but I'm not very familiar with Power Query and have not used it.

    Will need to study it 1st. Thanks for your help.


    - Jack

  9. #9
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Formula to match and merge data with Excel/VBA

    I suggest you get stuck in - it's a very useful tool. There's a link in my subject line to some help.

    Also this (courtesy of forum regular Olly):

    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.

  10. #10
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Re: Formula to match and merge data with Excel/VBA

    Hi Ali,

    Sure, that'll be interesting.

    Thanks for sharing the info.



    - Jack

+ 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. [SOLVED] Match data and merge tables
    By maralita in forum Excel General
    Replies: 12
    Last Post: 12-18-2019, 03:09 PM
  2. [SOLVED] Match and merge two data sets by dates- for each permno
    By p99 in forum Excel General
    Replies: 3
    Last Post: 04-11-2017, 11:51 AM
  3. [SOLVED] How to match, merge and sum data
    By owum in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-19-2013, 08:12 PM
  4. Macro to merge/match data
    By celebrationnm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2012, 02:07 PM
  5. How to merge and match two data files using mismatched format D.O.Birth info
    By HS_Counselor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2012, 04:36 PM
  6. Need to match data on 2 sheets and merge matching
    By innovativesmoking in forum Excel General
    Replies: 1
    Last Post: 08-23-2010, 12:03 AM

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