Matching values from two columns and making a third column with the results - possible?
 

I have a list of products in an Excel/CSV table
I've been given a disk with images for about a 2/3 of the products in the
table.
Fortunately, the images match the product code, mostly.
Thing is, there's 5,000 products, and I'm a bit stuck on how to match the
two up side by side.
I can make it so I end up with a simple list of the image names, and a
simple list of the product names.

SO, let's say I had the following columns

A is a list of products
B is the image name for that product, if it exists (ie: matches)
C is a list of images

So hopefully, there's some function that will make me end up with the
following (I just know this isn't going to line up!)
A B C
123 123.jpg 789.jpg
456 123.jpg
789 789.jpg 456.jpg
234 354.jpg
345 345.jpg
456 456.jpg
I can strip off and then replace the .jpg extension if needed, also not all
the product codes are the same length, and include alphanumeric characters
as well as hyphens, but fortunately no apostrophes.

Would really REALLY appreciate someone pointing me in the right direction
here! Thanks.
PS - if this is not possible in Excel, is there anything else that might do
this?

Move the list in column C over a couple of columns to, say, Column G
In Cell C1 (Assuming row 1 is the start of data), enter the following
formula...
=IF(ISNA(VLOOKUP(A1&".JPG",G:G,1,FALSE)),"",VLOOKU P(A1&".JPG",G:G,1,FALSE))
Copy this formula down your list.

HTH,
--
Gary Brown

Please rate this posting if it is helpful to you.


"Jamie Furlong" wrote:

I have a list of products in an Excel/CSV table
I've been given a disk with images for about a 2/3 of the products in the
table.
Fortunately, the images match the product code, mostly.
Thing is, there's 5,000 products, and I'm a bit stuck on how to match the
two up side by side.
I can make it so I end up with a simple list of the image names, and a
simple list of the product names.

SO, let's say I had the following columns

A is a list of products
B is the image name for that product, if it exists (ie: matches)
C is a list of images

So hopefully, there's some function that will make me end up with the
following (I just know this isn't going to line up!)
A B C
123 123.jpg 789.jpg
456 123.jpg
789 789.jpg 456.jpg
234 354.jpg
345 345.jpg
456 456.jpg
I can strip off and then replace the .jpg extension if needed, also not all
the product codes are the same length, and include alphanumeric characters
as well as hyphens, but fortunately no apostrophes.

Would really REALLY appreciate someone pointing me in the right direction
here! Thanks.
PS - if this is not possible in Excel, is there anything else that might do
this?

"Gary Brown" m wrote in
message ...
Move the list in column C over a couple of columns to, say, Column G
In Cell C1 (Assuming row 1 is the start of data), enter the following
formula...
=IF(ISNA(VLOOKUP(A1&".JPG",G:G,1,FALSE)),"",VLOOKU P(A1&".JPG",G:G,1,FALSE))
Copy this formula down your list.

Thank you very much! You're a genius!

Thanks, Jamie.
Since you seem to be satisfied with the answer, I'd appreciate it if you
would indicate that the post was helpful to you by selection 'YES' at the
bottom of the post.
Sincerely,
--
Gary Brown

If this post was helpful to you, please select 'YES' at the bottom of the
post.



"Jamie Furlong" wrote:
Thank you very much! You're a genius!

He is using a newsreader
--
Regards,

Peo Sjoblom

(No private emails please)


"Gary Brown" m wrote in
message ...
Thanks, Jamie.
Since you seem to be satisfied with the answer, I'd appreciate it if you
would indicate that the post was helpful to you by selection 'YES' at the
bottom of the post.
Sincerely,
--
Gary Brown

If this post was helpful to you, please select 'YES' at the bottom of the
post.



"Jamie Furlong" wrote:
Thank you very much! You're a genius!

"Peo Sjoblom" wrote in message
...
He is using a newsreader

Indeed - I didn't even know there was another way of reading this.. Is there
a copy of these posts on a forum or something, then?
I prefer news-readers thought - it's all just so much....quicker!

There was one problem with the formula - the first 5 characters of a product
determine the actual main generic product, for which there might or might
not be extra characters, and there may or may not be an image with a
different ending.
In other words: product: 12345-large image: 12345-various.jpg
I got totally stumped because I made an error (didn't put a . before the *)
and went hunting in forums, one of which seemed to suggest that regular
expressions must be prefixed by "/..", and so I tried a variation and ended
up with:
=IF(ISNA(VLOOKUP(A16&/..*;$D$2:$D$5000;1;0));"";VLOOKUP(A16&/..*;$d$2:$d$5000;1;0))
Of course, it didn't work, and I spent a good couple of hours trying to wrap
a LEFT statement round it (you can tell I'm new to this!).
In the end, it actually just ended up being
=IF(ISNA(VLOOKUP(A16&".*";$D$2:$D$5000;1;0));"";VL OOKUP(A16&".*";$D$2:$D$5000;1;0))

(Know a lot more about regular expressions and vlookup than I did 3 hours
ago, though!)

Anyway, thanks for all the help.