| View previous topic :: View next topic |
| Author |
Message |
digitaltoast
General User
Joined: 04 Apr 2005
Posts: 24
|
 Posted: Mon Jun 13, 2005 12:10 pm Post subject: Matching values from two columns-possible? OO Calc 1.9.104 |
 |
|
I have a list of products in a CSV table
I've been given a disk with images for about a 2/3 of the products.
Fortunately, the images match the product code, mostly.
Thing is, there's 5,000 products, but I'm confused about 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 in OpenOffice that will make me end up with the following
| Code: |
..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. |
|
| Back to top |
|
 |
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
| Posted: Mon Jun 13, 2005 1:01 pm Post subject: |
|
|
Hi digitaltoast,
So column B is the one you're trying to produce using a formula right?
In column B =VLOOKUP(A1;$C$1:$C$5000;1)
You would need to strip out the ".jpg" extension first, so that you can find matches.
HTH  ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8995
Location: Lexinton, Kentucky, USA
|
| Posted: Mon Jun 13, 2005 1:57 pm Post subject: |
|
|
Building on 8days' formula, the following appears to work and saves stripping the .jpg.
=VLOOKUP(A1&".jpg";$C$1:$C$5000;1) |
|
| Back to top |
|
|
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
| Posted: Mon Jun 13, 2005 2:03 pm Post subject: |
|
|
Ah, I thought about trying to take the .jpg out, perhaps using an additional column, but that's a more elegant solution, excellent 
BFN,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site |
|
| Back to top |
|
|
digitaltoast
General User
Joined: 04 Apr 2005
Posts: 24
|
| Posted: Mon Jun 13, 2005 2:06 pm Post subject: |
|
|
| JohnV wrote: | Building on 8days' formula, the following appears to work and saves stripping the .jpg.
=VLOOKUP(A1&".jpg";$C$1:$C$5000;1) |
Thank you! That was the one that worked - thanks for all the suggestions.
JohnV, you're a star!
As it happens, I'd seen something similar for Excel, but I couldn't work out how to search a complete column.
Again, thank you for a simple, elegant and speedy solution!
(now setting it to work on the whole spreadsheet - it's been running my P4 3Ghz 1Gb RAM PC at 75% for about 5 minutes now - think I might check on it in the morning!)
EDIT: OOOPS! It just finished its run, but on scrolling down I see some problems have crept in - where the image name is a straight run of numbers, it's working fine.
Where it is something like "123 box.jpg", it is matching it with items that start with the correct first two letters, but the rest is wrong!
In other words, I now have
1234 1234.jpg < correct as expected
2345 23AB box.jpg < seems to mash up with spaces.
The other thing is that the spreadsheet has 4200 rows and has finally finished, but now every time I click anything in the "result" column, it locks up for about a minute.
Should I log this as a bug, or is it recalculating and is that to be expected bearing in mind the amount of work it must be having to do? |
|
| Back to top |
|
|
uros
Super User
Joined: 22 May 2003
Posts: 601
Location: Slovenia
|
| Posted: Wed Jun 15, 2005 12:18 am Post subject: |
|
|
Hi digitaltoast!
Use this formula:
=VLOOKUP(A1&".jpg";$C$1:$C$5000;1;0)
0 is added as the last argument - this means exact match.
If there is no exact match in range C1:C5000 you get #N/A.
To avoid errors use
=IF(ISNA(VLOOKUP(A1&".jpg";$C$1:$C$5000;1;0));"";VLOOKUP(A1&".jpg";$C$1:$C$5000;1;0))
You're probably right, it could be recalculation that take a minute...
It can be disabled: Tools > Cell Contents > Automatic recalculation
Now press F9 to recalculate manualy...
Hope this helps!
Uros |
|
| Back to top |
|
|
digitaltoast
General User
Joined: 04 Apr 2005
Posts: 24
|
| Posted: Thu Jun 16, 2005 6:30 am Post subject: |
|
|
Stopping auto-recalc solved the crashing.
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:
=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));"";VLOOKUP(A16&".*";$D$2:$D$5000;1;0))
(Know a lot more about regular expressions and vlookup than I did 3 hours ago, though!) |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
