+ Reply to Thread
Results 1 to 7 of 7

Help comparing two columns of data with same names different values (index match??)

  1. #1
    Registered User
    Join Date
    07-14-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Help comparing two columns of data with same names different values (index match??)

    Hi All,

    Hope everyone's having a nice Wednesday morning. I am having trouble comparing two lists of data. They each have the same (or very similar names) but have different values. They're two sets of data from two different quarters, and I need to get the names and changed values side by side so I can calculate the differences. Unfortunately, I cannot sift through thousands of individuals and all the changes that occurred. I'm having trouble devising a formula to do this.

    I've attached a screenshot. What I need to have happen is have John Smith and his value in the second group match up on the same row with the John Smith in the first set so on and so forth (but for about 5000 individuals). I really hope this is doable. Thanks in advance!
    Attached Images Attached Images

  2. #2
    Forum Contributor
    Join Date
    09-26-2014
    Location
    Moscow, Russia
    MS-Off Ver
    MSE 10, MSE 13
    Posts
    179

    Re: Help comparing two columns of data with same names different values (index match??)

    Hi there!

    Just add another column to one table and use formula like this one:

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


    Where by Match you find row number of name you seek; by Index you see what value is placed on the same row with seeking name; by IfError you replace any errors with 0.

    Example in the attachment.
    Attached Files Attached Files
    Best wishes and have a nice day!

  3. #3
    Registered User
    Join Date
    07-14-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Help comparing two columns of data with same names different values (index match??)

    Thank you very much! Worked perfectly. One last question, for values that didn't change from quarter to quarter, or values that exist now but didn't exist then, how can I adjust the formula to not show 0 for those values (i.e. Lindsey's).

    And say John's value didn't change from 1525, how would I adjust the formula so it would say 1525 next to his value instead of 0?

    Thanks again, really appreciate your help.

  4. #4
    Forum Contributor
    Join Date
    09-26-2014
    Location
    Moscow, Russia
    MS-Off Ver
    MSE 10, MSE 13
    Posts
    179

    Re: Help comparing two columns of data with same names different values (index match??)

    May you attach a sample file with what you need? I don't quit get your ideas.

  5. #5
    Registered User
    Join Date
    07-14-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Help comparing two columns of data with same names different values (index match??)

    Thanks for taking the time to help me with this.

    So basically I would just like to get rid of the 0's under the Shares for 2009 column and replace them with the actual amounts they had in 2009. For example, Bill. He had 10 shares in 2009 and 10 in 2010, but under Column C it says 0. I'm trying to get it to show 10 so I can show the difference in Column D.
    Attached Images Attached Images

  6. #6
    Forum Contributor
    Join Date
    09-26-2014
    Location
    Moscow, Russia
    MS-Off Ver
    MSE 10, MSE 13
    Posts
    179

    Re: Help comparing two columns of data with same names different values (index match??)

    Bill is not presented by formula, because formula doesn't see him. Just prolong area of formula observing. Your E$2:E$8 range is not covering E9 cell. Replace it with E$2:E$9.

  7. #7
    Registered User
    Join Date
    07-14-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Help comparing two columns of data with same names different values (index match??)

    I figured it out! Thanks again for all your help. Hope you have a nice weekend.

+ 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. Index+Match 2 values in multiple columns
    By Beginer25 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-17-2015, 11:23 AM
  2. Looking for Values in Different Columns - VLOOKUP, MATCH, INDEX ???
    By juandyer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2015, 01:41 AM
  3. [SOLVED] Match Issue - Consolidate Columns of Data With Match/Index/etc?
    By excelsior123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2014, 05:14 PM
  4. [SOLVED] Comparing values in two columns and highlighting cells that don't match
    By moosetales in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-10-2014, 07:51 AM
  5. [SOLVED] dynamic index match formula to transpose values across rows and then down columns
    By Bananas212 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-30-2014, 03:07 PM
  6. [SOLVED] Lookup match index multiple values return other columns
    By martypocock in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 11-07-2012, 10:57 PM
  7. Replies: 1
    Last Post: 11-22-2010, 07:03 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1