hi there,
i have a sistematic problem with data and i have to be creative with exported data for them to be usable.
i deal with products that have certain ID
problem is One product can have MORE diffreretnt ID's. I coded an export to combine all ID' that refer to same product in one CELL (ref. CODED TABLE)
so product 1 can have |x y z| ID's. product ID's have few spaces between them
i pull this export every quarter and keep up with table updating for unique rows for one product.
now, exported table has all unique ID's, so more ID's can refer to ONE product.
when i want to compare my Coded table made on 01.01. with my newly exported table on 28.02.
i need my unique ID's from exported table vlooked up in Coded table and have the update done from expotred table. problem is I cannot firure out the formula to look for 1 ID' within an arey where cells containe more ID's
[TABLE="width: 500"]
<tbody>[TR]
[TD]A:A
Coded table ID's[/TD]
[TD]B:B
Exported ID's[/TD]
[TD]column i need Coded table ID's found from Exported ID's table.[/TD]
[TD][/TD]
[TD]Returned value should be regarding B column:[/TD]
[/TR]
[TR]
[TD]123 234 345 456[/TD]
[TD]123[/TD]
[TD]=vlookup("*"&B2&"*";A:A;1;false)[/TD]
[TD]formula doesn't work[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]321 432 543 654[/TD]
[TD]234[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]741 852[/TD]
[TD]741[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD]963 852 741[/TD]
[TD]963[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]654[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]321[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]543[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]852[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
</tbody>[/TABLE]
i have a sistematic problem with data and i have to be creative with exported data for them to be usable.
i deal with products that have certain ID
problem is One product can have MORE diffreretnt ID's. I coded an export to combine all ID' that refer to same product in one CELL (ref. CODED TABLE)
so product 1 can have |x y z| ID's. product ID's have few spaces between them
i pull this export every quarter and keep up with table updating for unique rows for one product.
now, exported table has all unique ID's, so more ID's can refer to ONE product.
when i want to compare my Coded table made on 01.01. with my newly exported table on 28.02.
i need my unique ID's from exported table vlooked up in Coded table and have the update done from expotred table. problem is I cannot firure out the formula to look for 1 ID' within an arey where cells containe more ID's
[TABLE="width: 500"]
<tbody>[TR]
[TD]A:A
Coded table ID's[/TD]
[TD]B:B
Exported ID's[/TD]
[TD]column i need Coded table ID's found from Exported ID's table.[/TD]
[TD][/TD]
[TD]Returned value should be regarding B column:[/TD]
[/TR]
[TR]
[TD]123 234 345 456[/TD]
[TD]123[/TD]
[TD]=vlookup("*"&B2&"*";A:A;1;false)[/TD]
[TD]formula doesn't work[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]321 432 543 654[/TD]
[TD]234[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]741 852[/TD]
[TD]741[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD]963 852 741[/TD]
[TD]963[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]654[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]321[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]543[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]852[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
</tbody>[/TABLE]