+ Reply to Thread
Results 1 to 13 of 13

Merging Spreadsheets, based on two columns

  1. #1
    Registered User
    Join Date
    07-27-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    6

    Merging Spreadsheets, based on two columns

    Hi all,

    I was wondering if it was possible to merge two separate spreadsheets, based on specific information.

    I have two spreadsheets, one has information regarding transactions, with about 15 different columns. The other has information regarding indexes with about 60 different columns.

    Both spreadsheets however, contain two columns which provide, 1) a specific date (monthly), and 2) a specific postcode. (The rest of the columns for the two spreadsheets contain completely different information ).
    What I want to do is to merge rows from the two sheets, which contain BOTH the same postcode and same specific monthly date. I want also to retain all the information from both spreadsheets' columns when I merge the tables together. (So each row should have 70+ collumns of information).

    I know v-look up tables may offer a solution. However, is this still possible when the merging depends on matching two columns of information and when i'm looking to merge all potential information fro both tables? Additionally, to make things more complicated, the spreadsheet containing information regarding indexes will contain some combinations of postcodes and dates which don't exist in the spreadsheet containing transactions.

    Is there a way of doing this?

    Many Thanks

  2. #2
    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,365

    Re: Merging Spreadsheets, based on two columns

    Welcome to the forum.

    What you seem to be describing may well be possible with PowerQuery, but we'd need to see some sample data.
    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.

  3. #3
    Registered User
    Join Date
    07-27-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    6

    Re: Merging Spreadsheets, based on two columns

    Hi thanks for your response.

    The reason why I've got the two separate sheets, is because it was the only way I could obtain the data (using code) from a specific source.
    Ideally, I would have liked to use code to obtain all the data at once, however after trying various code, I'm pretty certain that this cannot be done.
    Hence, I'm just concerned that a PowerQuery, may result in similar issues? If you believe that it may work using a Power Query,
    I will certainly give it a go!

    Many thanks.
    Last edited by econ 24; 07-27-2019 at 10:32 AM.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Merging Spreadsheets, based on two columns

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  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,365

    Re: Merging Spreadsheets, based on two columns

    PowerQuery can merge two tables into one new one.

  6. #6
    Registered User
    Join Date
    07-27-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    6

    Re: Merging Spreadsheets, based on two columns

    Hi, I do apologize, please excuse my ignorance. I have attached the excel workbook, with sheet 1 and sheet 2, showing the two spreadsheets, that I am trying to merge.
    Also thank you for your advice on posting attachments.
    (I have taken the advice and have made alterations to the sheet before posting. However, all the information I am using, is publicly available to anyone).
    (In total I have about 11,000 transactions, so have deleted most rows and some columns in order to successfully upload the attachment).

    Thanks again for everyone's help!
    Attached Files Attached Files

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Merging Spreadsheets, based on two columns

    Sheet 1 Column V when formatted as a date appears to be a valid date format. However, Sheet 2 has a date field that is not in a valid date format and has no matches to Sheet 1. Need a better example with matching common fields to join the two tables. Additionally, which fields in each of the sheets needs to included in the merged data.
    Last edited by alansidman; 07-28-2019 at 01:32 AM.

  8. #8
    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,365

    Re: Merging Spreadsheets, based on two columns

    I echo Alan's call for clarification: you have Wiltshire in one sheet and Leicestershire and Devon in the other - where's the common ground? These are three different counties and don't overlap.

  9. #9
    Registered User
    Join Date
    07-27-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    6

    Re: Merging Spreadsheets, based on two columns

    Hi, sorry I took the info about desensitizing the data a bit far! I've attached a new spreadsheet below, again deleting certain rows and columns in order that don't exceed the file size upload limit.
    Not all information in sheet 1 and sheet 2 will match up (e.g there may be a certain postcode and date combination which appears in sheet 1 but does not appear in sheet 2).
    However, there are those that will match (e.g some of the Essex post codes match up in sheet 1 and 2, in terms of combinations of postcodes and address).
    I need to match the postcodes (column T) and dates (column V) in sheet 1, to the exact corresponding postcode and date (columns C and D) in Sheet 2.
    I wish to retain the rest of the info from the rest of the columns, when merging. So for each data entry/row there should be around 30+ columns worth of information (based on this example spreadsheet).

    Sorry for the confusion and again many thanks!
    Attached Files Attached Files

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Merging Spreadsheets, based on two columns

    I have attempted to merge your two tables using both Power Query and then Access. I tested the tables and found that there are no matching dates. There are matching post codes, but when you apply both criteria, then there are zero results.

  11. #11
    Registered User
    Join Date
    07-27-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    6

    Re: Merging Spreadsheets, based on two columns

    Hi I should have been more clear, I am not concerned about the specific day that the transaction took place.
    I am only concerned about the month. As long as the year and the month match, that's what is important.
    In this case there are matches, I should have converted that into the correct format before sending.
    (Apologies for this oversight).

    Many thanks

  12. #12
    Registered User
    Join Date
    07-27-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    6

    Re: Merging Spreadsheets, based on two columns

    Hi all, just wanted to say a big thank you. After altering the format slightly, I have managed to merge the two tables
    successfully, using the power query. Thank you for pointing me in the right direction, as I didn't have a clue of where to begin
    solving the issue. You have saved me probably a weeks + worth of copying/pasting information between the two sheets.
    Thank you for everyone's help!

  13. #13
    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,365

    Re: Merging Spreadsheets, based on two columns

    Thanks for letting us know.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Merging Tables with Different Columns, based on 1 column's data
    By dantastical in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-18-2019, 07:03 AM
  2. [SOLVED] Merging Spreadsheets with Identical Columns
    By sherylt13 in forum Excel General
    Replies: 2
    Last Post: 12-18-2016, 09:39 PM
  3. Merging two spreadsheets using VBA based on a number of conditions
    By methuselah90 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-15-2014, 11:26 AM
  4. Merging Columns based on two criteria
    By puppy26 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2013, 05:03 AM
  5. Merging Columns based on two criteria. URGENT.
    By puppy26 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-06-2013, 11:37 AM
  6. [SOLVED] Merging spreadsheets based on Variable "Index"
    By jfclogston in forum Excel General
    Replies: 7
    Last Post: 05-07-2012, 06:36 PM
  7. Replies: 3
    Last Post: 05-16-2006, 08:25 PM

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