OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Matching values from two columns-possible? OO Calc 1.9.104

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
digitaltoast
General User
General User


Joined: 04 Apr 2005
Posts: 24

PostPosted: Mon Jun 13, 2005 12:10 pm    Post subject: Matching values from two columns-possible? OO Calc 1.9.104 Reply with quote

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
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Mon Jun 13, 2005 1:01 pm    Post subject: Reply with quote

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 Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Mon Jun 13, 2005 1:57 pm    Post subject: Reply with quote

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
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Mon Jun 13, 2005 2:03 pm    Post subject: Reply with quote

Ah, I thought about trying to take the .jpg out, perhaps using an additional column, but that's a more elegant solution, excellent Very Happy

BFN,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
digitaltoast
General User
General User


Joined: 04 Apr 2005
Posts: 24

PostPosted: Mon Jun 13, 2005 2:06 pm    Post subject: Reply with quote

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
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Wed Jun 15, 2005 12:18 am    Post subject: Reply with quote

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
View user's profile Send private message
digitaltoast
General User
General User


Joined: 04 Apr 2005
Posts: 24

PostPosted: Thu Jun 16, 2005 6:30 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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