+ Reply to Thread
Results 1 to 9 of 9

Matching 2 columns from different datasets when 1 column is missing values

  1. 11-06-2013, 06:29 PM #1
    kwdrff
    kwdrff is offline
    Registered User
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Matching 2 columns from different datasets when 1 column is missing values

    Hi,

    I am trying to consolidate country data from two different sources. One of the sources has data on GDP growth for 252 countries. The other has data on Voice and Accountability ratings for only 216 countries. I want to get rid of Column C (country names from data set with only 216 countries) and match up Column D values to their appropriate country names in Column A.

    Thus, my goal is to keep Columns A and B the same, get rid of Column C, and get the values for Column D in the appropriate rows (some rows will be empty to represent missing countries data).

    Please let me know if you know how to do this (otherwise I will be laboriously doing this by hand). Thank you in advance!!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-06-2013, 09:29 PM #2
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Matching 2 columns from different datasets when 1 column is missing values

    you can use index/match

    =INDEX(D:D,MATCH(A2,C:C,0))
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  3. 11-06-2013, 09:41 PM #3
    kwdrff
    kwdrff is offline
    Registered User
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Matching 2 columns from different datasets when 1 column is missing values

    Thank you for your reply! Where do I enter "=INDEX(D:D,MATCH(A2,C:C,0)" and should I have any cells highlighted when I click enter?
    Reply With QuoteRegister To Reply
  4. 11-06-2013, 09:49 PM #4
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Matching 2 columns from different datasets when 1 column is missing values

    you can just paste the formula say in cell F2 then fill the formula down using fill handle..
    Reply With QuoteRegister To Reply
  5. 11-06-2013, 09:59 PM #5
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Matching 2 columns from different datasets when 1 column is missing values

    I inserted a new column C to hold the values in column D that match the countries in Column A. I also added two columns to cross-check if the other country list contained countries that are not in Column A. Countries missing from Column A are marked in Red. (Conditional Formatting) Countries in column A that are not in the other list of countries have blanks in column C.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  6. 11-09-2013, 01:17 PM #6
    kwdrff
    kwdrff is offline
    Registered User
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Matching 2 columns from different datasets when 1 column is missing values

    Thank you for your help, newdoverman! Would you mind explaining how you did that? I need to repeat this process for a few more sets of data. I see that you used the formula "=IFERROR(VLOOKUP..." but am not sure what this means. Thank you again!
    Reply With QuoteRegister To Reply
  7. 11-09-2013, 03:10 PM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Matching 2 columns from different datasets when 1 column is missing values

    You're welcome.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    The IFERROR part returns a blank cell if the VLOOKUP formula doesn't find a value. The VLOOKUP takes the value in A2 and searches down the column D from D2:D216 for a match. When a match is found, the value in column E is returned. The 0 means that the match has to be exact.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This VLOOKUP formula does the same thing as the first formula only this time the formula is looking for a match in the country name. If a match is found, the country name is returned and if not "Not in column A" is returned. This is accomplished by the 1 just after the range $A$2:$A$253.

    As a visual cue, Conditional Formatting was used to highlight "Not in column A" by using this formula in Conditional Formatting:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Select column G and then go to Conditional Formatting, New Rule, Use Rule.... and enter the formula.
    Reply With QuoteRegister To Reply
  8. 11-09-2013, 04:46 PM #8
    kwdrff
    kwdrff is offline
    Registered User
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Matching 2 columns from different datasets when 1 column is missing values

    Great, I was able to repeat that process successfully. Thank you!
    Reply With QuoteRegister To Reply
  9. 11-09-2013, 04:50 PM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Matching 2 columns from different datasets when 1 column is missing values

    Great!! Thanks for the feedback.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. compare 2 columns and create new column with missing values in second column
    By Jroelan2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2012, 12:33 AM
  2. Matching and printing values from two excel columns in another column on certain condition
    By meetvivek72 in forum Excel General
    Replies: 5
    Last Post: 08-24-2012, 10:59 AM
  3. Matching values from 2 columns and returning the value in 3rd column
    By Shaz_79 in forum Excel General
    Replies: 2
    Last Post: 12-30-2011, 07:30 PM
  4. Matching values from 2 columns and returning the value in 3rd column
    By Shaz_79 in forum Excel General
    Replies: 8
    Last Post: 12-25-2011, 08:39 PM
  5. Matching values from two columns and making a third column with the results - possible?
    By Jamie Furlong in forum Excel General
    Replies: 5
    Last Post: 06-16-2005, 02:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1