Results 1 to 6 of 6

Thread: compare 4 different columns from 2 tables and extract data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default compare 4 different columns from 2 tables and extract data

    hi,
    i have got 2 tables.
    I want to compare 4 columns from both these tables and retrieve data from table2 into table1, if the criteria matches.
    Now the problem is that the values to be compared will not match exactly.
    2 columns would match exact
    2 columns would be similar

    Table1


    Table2


    The columns Product Name and Manufacturer will not be an exact match, but would be similar. This first word would usually be an exact match
    The Packing and MRP columns would be an exact match.
    the sequence of the rows would not match and there can be multiple products with similar names and that the reason to take other 3 columns into consideration

    I would like to add the Prod Code from Table2 to Table1, while comparing the 4 columns on each table.

    Link to the sample excel file


    Can someone help me with a formula or a VBA to achieve this

  2. #2
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compare 4 different columns from 2 tables and extract data

    anyone?

  3. #3
    Board Regular Taul's Avatar
    Join Date
    Oct 2004
    Location
    Uxbridge
    Posts
    639
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compare 4 different columns from 2 tables and extract data

    Hi,
    Your Table1 is actually called MG (according to the downloaded file)

    On sheet Inven cell E2 use this formula
    Code:
    =IFERROR(INDEX(MG[Product Code],MATCH(1,INDEX((LEFT(MG[Manufacturer],3)=LEFT(C2,3))*(MG[MRP]=D2)*(MG[Packing]=B2)*(LEFT(MG[Product Name],3)=LEFT(A2,3)),),FALSE)),"no match")
    columns Product Name & Manufacturer are partial matched based on the first 3 characters of the text but you can change it to any number of characters.
    However, the column for MRP are not correct with the table MG_2, also the manufacturer FDC is listed twice on the table MG but only listed once in table MG_2. Where these error occur the formula returns "no match"

    Paul.

    Excel 2010
    A B C D E
    1 Product Name Packing Manufacturer MRP Product Code
    2 silky hair 1000ml shampoo 1000ml Claris Life sciences 34.00 no match
    3 hair 50gm gel 50gm wella 61.90 NM231051
    4 pencil h4 pack 10 fabre castle 65.00 TP164015
    5 paper clip pack 50 office equip 31.20 GO249407
    6 seacod tablet 50 Aglow med 12.50 FC315440
    7 bathing bar 75gm soap 75gm itc 42.00 X162352
    8 silky hair 100ml shampoo 100ml FDC 15.20 no match
    9 seacod tablet 10 Aglow med 1.10 no match
    10 hair clips big 10 office equip 68.75 DF230134
    11 beard trimmer 1 panasonic 45.00 no match
    12 office file 5's pack 5 office equip 26.25 TG265320
    13 glass cleaner 2x500ml pack 2x500ml FDC 19.75 HJ62991
    Inven
    Excel 2010 on Win7 at home - 2013 on win7 at work

  4. #4
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compare 4 different columns from 2 tables and extract data

    THIS IS GREAAAAATTTTT. worked flawless.
    At start i could not get it to work but then i found out that the issue was with the cell formatting. Some cells were formatted as text and some were numbers. had to make sure all respective columns had the same formatting to work.

    Now the only issue would be to run this formula on 50k rows.

    Hey one more thing. how can i change the PACKING column format to number.
    If i select the entire column and change the format to number the formula doesnt work. (although the cell, when clicked shows that it is formatted to be a number)
    The cell shows green arrow on the top left. I have to select he cell and click the yellow popup ( ! ) and click CONVERT TO NUMBER. Only then does it work.

    The problem is only with the packing column and only with cells containing only numerics. If the cell has value like 60ml or 75gm, the formula returns the correct value.
    But if the cell value is 10 or 50 or 100, then formula says no match

  5. #5
    Board Regular Taul's Avatar
    Join Date
    Oct 2004
    Location
    Uxbridge
    Posts
    639
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compare 4 different columns from 2 tables and extract data

    Quote Originally Posted by fowzan View Post
    The problem is only with the packing column and only with cells containing only numerics. If the cell has value like 60ml or 75gm, the formula returns the correct value.
    But if the cell value is 10 or 50 or 100, then formula says no match
    Hi
    In the example data there are 7 items that have numeric only data in the Packing column.
    These are in rows 4,5,6,9,10,11 & 12 but only rows 9 & 11 are showing “no match”

    This is because row 9 has a MRP value that is incorrect, it is showing as 1.10 and should be 12.50
    Row 11 is showing “no match” because the MRP is showing as 45.00 and it should be 35.00
    The data in both the tables need to match.

    When you enter data like 5, 10 or 15, it will automatically format as a Value and will indent to the right of the cell. If you enter abc, 50ml or 50gm, it will automatically format as text and will indent to the left of the cell.
    The formula will work provided the data in the packing column of both tables match.

    If you prefer, there is an option to create an extra column called “Unit” in which you can enter the product unit such as ml, gm or 2xml and then keep the “Packing” column just for the numeric value.
    You can include or exclude the “Unit” column from the formula. I’m not sure if this approach would work for you but it’s worth a mention.

    Paul.
    Excel 2010 on Win7 at home - 2013 on win7 at work

  6. #6
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compare 4 different columns from 2 tables and extract data

    well what you said should be the ideal case. but somethings wrong with my file. I will try copy pasting the data into a new file and try over there.
    Unfortunately i cannot create an additional units column.
    And the mrp in row 9&11 is not exactly incorrect. The mrp has been updated (which has to be treated as "no match", no other go).

    Once again thanks for the help. You saved alot of time doing manual entries.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •