Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#3
|
|||
|
|||
"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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making only specified columns visible | Excel Discussion (Misc queries) | |||
Making list with unique columns | Excel Worksheet Functions | |||
making used values fall from a list | Excel Discussion (Misc queries) | |||
making columns automatically turn numbers into negatives | Excel Worksheet Functions | |||
Making Rows into Columns | Excel Discussion (Misc queries) |