compare 4 different columns from 2 tables and extract data

fowzan

Board Regular
Joined
Aug 5, 2015
Messages
59
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
63281f27b4f16258b426e06106b91bde.png


Table2
3ee0adaeef77df2bfeee0b17f66d15b2.png


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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
ABCDE
1Product NamePackingManufacturerMRPProduct Code
2silky hair 1000ml shampoo1000mlClaris Life sciences 34.00no match
3hair 50gm gel50gmwella61.90NM231051
4pencil h4 pack10fabre castle65.00TP164015
5paper clip pack50office equip31.20GO249407
6seacod tablet50Aglow med12.50FC315440
7bathing bar 75gm soap75gmitc42.00X162352
8silky hair 100ml shampoo100mlFDC15.20no match
9seacod tablet10Aglow med1.10no match
10hair clips big10office equip68.75DF230134
11beard trimmer1panasonic45.00no match
12office file 5's pack5office equip26.25TG265320
13glass cleaner 2x500ml pack2x500mlFDC19.75HJ62991

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Inven
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top