+ Reply to Thread
Results 1 to 8 of 8

Column comparison using match and index function

  1. #1
    Registered User
    Join Date
    01-12-2013
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    14

    Column comparison using match and index function

    Hi..
    My query is as follows:
    I want to compare the values of two columns. But it is not that simple. Data in 1st column is not in proper order so I have to use text to columns twice to extract data. Extracted data will be in two separate columns say A and B. I have to compare data from column A with data in Column C (master list). If match is found, copy data or cell value from column A to Column D. If match is not found, go to Column B and copy corresponding value from Column B to Column D. For this comparison, I want to use index and match function.
    Kindly help me in this regard.
    Thank you for time spent in going through this thread.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Column comparison using match and index function

    i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that. I would also include what your raw data looks like before you use TtoC on it
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-12-2013
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Column comparison using match and index function

    My data will be as follows:
    Column A Column B Column C Column D
    Bulb Tubelight Bulb
    LED Bulb LED
    Tube LCD Tubelight
    AB LCD LED LCD
    LED LED

  4. #4
    Registered User
    Join Date
    01-12-2013
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Column comparison using match and index function

    My data is as attached:
    Data.jpg

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Column comparison using match and index function

    Quote Originally Posted by FDibbins View Post
    i would suggest you upload a sample work book,

    please do not upload a picture of your file, we cannot edit a picture, and no1 wants to retype your data

  6. #6
    Registered User
    Join Date
    01-12-2013
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Column comparison using match and index function

    PFA herewith excel sheet containing my data.
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Column comparison using match and index function

    Thanks for the file. (a quick tip - please dont give columns names like column A and column B, especially when column A is not actually IN column A, it just makes things messy.)

    Im a bit confused here though...is the data in column A (the actual column A - "raw data") what your actual data looks like before you use text-2-columns?
    If so, are you looking to find the contents of "column C" (actually column D) in "raw data" (lol see what I mean about column headings getting messy?)


    Im thinking it might be easier if you rename your column headings, and upload the file again, showing what you have, what you want, and explain how you got that

  8. #8
    Registered User
    Join Date
    01-12-2013
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Column comparison using match and index function

    PFA updated file.
    My raw data is in Column A. Result of TtoC is shown in 3 different tables.
    Data in Columns K and N is pasted in columns B and C. Then, Column B data is compared with Column D data i.e. masterlist. If match is found, coreesponding matched data or cell value from masterlist column is copy-pasted in Column E i.e. output. For match found, truncated string should be considered i.e. if column B contains data as "Tube" and masterlist contains "Tubelight", then it should be considered as match found and cell data from masterlist i.e. "Tubelight" should be pasted in Output column i.e. Column E. If match is not found, corresponding value in column C should be pasted in Output column i.e. Column E.
    I hope now my problem statement is clear.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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