+ Reply to Thread
Results 1 to 9 of 9

Combining two seperate with common ID

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    bangalore,india
    MS-Off Ver
    Microsoft 365 MSO (Version 2208 Build 16.0.15601.20858) 64-bit
    Posts
    34

    Combining two seperate with common ID

    Dear Gurus,
    I looking for excel formula that needs to combine the Business and Country data file which needs to lookup the unique common ID for both Business and Country.
    I have defined the ID with color code. Thanks in advance. The input sheet is just an example.

    regards
    Jaffir
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2407 Win 11 Home 64 Bit
    Posts
    24,134

    Re: Combining two seperate with common ID

    With Power Query aka Get and Transform Data

    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files
    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

  3. #3
    Registered User
    Join Date
    01-13-2011
    Location
    bangalore,india
    MS-Off Ver
    Microsoft 365 MSO (Version 2208 Build 16.0.15601.20858) 64-bit
    Posts
    34

    Re: Combining two seperate with common ID

    Hi Alan, super i can see the output. Thanks. I will learn the power query for sure, but before quickly, how to load the two sources?
    can you mention in simple steps?
    thanks. I tried and failed.

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

    Re: Combining two seperate with common ID

    Highlight each range and convert each to a table Ctl +T

    then open Power Query to a blank query, click on Advanced Editor and paste my Mcode into the Editor. Read the links I have provided for more detailed instructions.

  5. #5
    Registered User
    Join Date
    01-13-2011
    Location
    bangalore,india
    MS-Off Ver
    Microsoft 365 MSO (Version 2208 Build 16.0.15601.20858) 64-bit
    Posts
    34

    Re: Combining two seperate with common ID

    but your query is not adding the 'country' related values, means added only the country not their corresponding values
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2407 Win 11 Home 64 Bit
    Posts
    24,134

    Re: Combining two seperate with common ID

    Reviewed your expected results. I mis interpreted your requirements. I am confused and need to understand on the criteria for joining the two tables. Please explain in simple terms the criteria for merging the two tables. When I join on the Metric ID. I end up with 1300 rows of data versus the 234 you have in your results.

  7. #7
    Registered User
    Join Date
    01-13-2011
    Location
    bangalore,india
    MS-Off Ver
    Microsoft 365 MSO (Version 2208 Build 16.0.15601.20858) 64-bit
    Posts
    34

    Re: Combining two seperate with common ID

    In my attached file. the output having 234 rows that equals the input file. I donot see the rows exceeding i.e., 1300 rows.
    I hope you can understand that the ID is common for Business and Country, I always want the ID in the first column so wherever the count for business or country is higher, the ID should be in last row. Hope this helps.
    Note: I have 5 more tables same like business and Country.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Combining two seperate with common ID

    Here is a formula-based proposal.
    To match the business with the country in columns G:K
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The final output in columns R:Z is populated using reference formulas such as
    In column R: =G2
    In column Z: =P2
    Note that the sequence skips the reference to column L.
    Note that the formatting for the %age columns has to be changed from general to percentage.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    01-13-2011
    Location
    bangalore,india
    MS-Off Ver
    Microsoft 365 MSO (Version 2208 Build 16.0.15601.20858) 64-bit
    Posts
    34

    Re: Combining two seperate with common ID

    Thats beautiful. Thanks JeteMC. Let me implement the formula for in actual sheet with dependent columns and know you for any hiccups.

+ 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. linking up two seperate sheets via common field name
    By Rambergs in forum Excel General
    Replies: 2
    Last Post: 02-24-2012, 03:02 PM
  2. Replies: 1
    Last Post: 02-11-2012, 08:24 AM
  3. Combining common columns from two worksheets
    By Keiras12 in forum Excel General
    Replies: 1
    Last Post: 04-11-2011, 07:25 PM
  4. combining different records w/ common ID#s
    By scrapeape in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-16-2009, 02:58 PM
  5. Combining 2 worksheets using common ID
    By axa0105 in forum Excel General
    Replies: 0
    Last Post: 04-02-2009, 08:28 PM
  6. Seperate numerous cells into a common cell.
    By sabegirl in forum Excel General
    Replies: 3
    Last Post: 09-15-2006, 07:03 PM
  7. Numbers from seperate worksheet to common sheet
    By S.C in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-13-2005, 11:06 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