compare 4 different columns from 2 tables and extract data

[fowzan]

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

 

[fowzan]

anyone?

 

[Taul]

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

On sheet Inven cell E2 use this formula

=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
Product Name	Packing	Manufacturer	MRP	Product Code
silky hair 1000ml shampoo	1000ml	Claris Life sciences
hair 50gm gel	50gm	wella
pencil h4 pack	fabre castle
paper clip pack	office equip
seacod tablet	Aglow med
bathing bar 75gm soap	75gm	itc
silky hair 100ml shampoo	100ml	FDC
seacod tablet	Aglow med
hair clips big	office equip
beard trimmer	panasonic
office file 5's pack	office equip
glass cleaner 2x500ml pack	2x500ml	FDC

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]34.00[/TD]
[TD="align: center"]no match[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]61.90[/TD]
[TD="align: center"]NM231051[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]65.00[/TD]
[TD="align: center"]TP164015[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]31.20[/TD]
[TD="align: center"]GO249407[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]12.50[/TD]
[TD="align: center"]FC315440[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]42.00[/TD]
[TD="align: center"]X162352[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]15.20[/TD]
[TD="align: center"]no match[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]1.10[/TD]
[TD="align: center"]no match[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]68.75[/TD]
[TD="align: center"]DF230134[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]45.00[/TD]
[TD="align: center"]no match[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]26.25[/TD]
[TD="align: center"]TG265320[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]19.75[/TD]
[TD="align: center"]HJ62991[/TD]

</tbody>

Inven​

 

[fowzan]

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

 

[Taul]

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.

 

[fowzan]

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.