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
Mondragger2
Newbie
Newbie


Joined: 07 Jul 2015
Posts: 1
Location: España

PostPosted: Tue Jul 07, 2015 10:06 am    Post subject: Reply with quote

Sorry for the revamp (google took me here), that code works great:

=VLOOKUP(A1&".jpg";$C$1:$C$5000;1;0)

But I need something a little bit different from the TS.

I need to match B values to the ones in A, like "Product 1-Product 1", in order, just like that code does. But I also need the values in C, to change order following B's new position.

So if i've got:

A B C
1 2 0,10€
2 1 0,30

I need it to order it like these:

A B C
1 1 0,30
2 3 0,10


Is it possible? Or is there a workaround? Like merging B and C, sorting it right, then dividing the values onto separate columns?

I really need this, thanks for reading.
Back to top
View user's profile Send private message AIM Address
lynnerlynrick
OOo Advocate
OOo Advocate


Joined: 23 May 2015
Posts: 398
Location: rafael (fletaport) garcia bouved veracruz 9 subsotano c Madrid Spain 28036

PostPosted: Tue Jul 07, 2015 11:31 am    Post subject: Silver Round Shape Crystal Earrings - $10.00 : Professional Reply with quote

you little factor.<br />
<br />
But why should you give upwards that excellent top because doing so isn't the things you want? Finding the actual cake top you want is specifically hard if your primary wedding is known for a very specific theme or simply color scheme.<br />
<br />
The so next best activity if you should not find best - will be create perfect.<br />
<br />
Uncomplicated Fix<br />
<br />
Upon having found in which nearly suitable top, you could start to add a lot of silk or even fresh flowers that may match or perhaps compliment the colors from the cake for which you commissioned? Many times you is able to do this oneself or get your florist exercise.Either way you decide to pursue it, by arranging the additional flowers close to your choice with the cake topper you can actually suddenly have a very good top this really is unique in your wedding.The egypt flowers compliment all of those other wedding cake by adding a dash of color for the top -- which sometimes has an excessive amount white in the basket anyway.<br />
<br />
Breathtaking Elegance<br />
<br />
To feature even further beauty in your cake, consider ordering cake diamond jewelry that was designed to accent the complete cake by having an element for every single tier.It's a tasteful touch that not every one provides discovered nevertheless.<br />
<br />
Another option so it certainly due for any return in order to popularity is usually to rent a water come that sits from the first and additionally second tier belonging to the cake.Then customise it as a result of wrapping several silk flowers about the pillars which holds the very best tier.These uncomplicated tips will let you add color choice and elegance for your wedding wedding cake topper, whilst your entire wedding cake.
Silver Round Shape Crystal Earrings - $10.00 : Professional wedding dresses stores, lovebridaldresses.com
#sddm
{ margin: 0 auto;
padding: 0;
z-index: 30;
background-color:#F4F4F4;
width: 80px;
height:23px;
float: right;
margin-right: 70px;}
#sddm li
{ margin: 0;
padding: 0;
list-style: none;
float: left;
font: bold 12px arial}
#sddm li a
{ display: block;
margin: 0 1px 0 0;
padding: 4px 10px;
width: 60px;
background: #EAEAE8;
color: #666;
text-align: center;
text-decoration: none}
#sddm li a:hover
{ background: #49A3FF}
#sddm div
{ position: absolute;
visibility: hidden;
margin: 0;
padding: 0;
background: #EAEBD8;
border: 1px solid #5970B2}
#sddm div a
{ position: relative;
display: block;
margin: 0;
padding: 5px 10px;
width: auto;
white-space: nowrap;
text-align: left;
text-decoration: none;
background: #EAEBD8;
color: #2875DE;
font: 12px arial}
#sddm div a:hover
{ background: #49A3FF;
color: #FFF}
<a href="http://www.lovebridaldresses.com/" onmouseover="mopen('m1')" onmouseout="mclosetime()">Language</a>

<a href="http://www.lovebridaldresses.com/de/">
<img src="http://www.lovebridaldresses.com/langimg/gericon.gif" alt="Deutsch" title=" Deutsch " height="15" width="24">Deutsch</a>
[url=http://www.lovebridaldresses.com/fr/] FrançaisFrançais[/url]
[url=http://www.lovebridaldresses.com/it/] italianoItaliano[/url]
EspañolEspañol
[url=http://www.lovebridaldresses.com/pt/] PortuguêsPortuguês[/url]
[url=http://www.lovebridaldresses.com/jp/] 日本語日本語[/url]
[url=http://www.lovebridaldresses.com/ru/] russianRussian[/url]
[url=http://www.lovebridaldresses.com/ar/] arabicArabic[/url]
<a href="http://www.lovebridaldresses.com/no/">
<img src="http://www.lovebridaldresses.com/langimg/noicon.gif" alt="norwegian" title=" norwegian " height="15" width="24">Norwegian</a>
<a href="http://www.lovebridaldresses.com/sv/">
<img src="http://www.lovebridaldresses.com/langimg/svicon.gif" alt="swedish" title=" swedish " height="15" width="24">Swedish</a>
[url=http://www.lovebridaldresses.com/da/] danishDanish[/url]
[url=http://www.lovebridaldresses.com/nl/] NederlandsNederlands[/url]
[url=http://www.lovebridaldresses.com/fi/] finlandFinland[/url]
[url=http://www.lovebridaldresses.com/ie/] irelandIreland[/url]
[url=http://www.lovebridaldresses.com/] EnglishEnglish[/url]



Payment |
<a href="http://www.lovebridaldresses.com/index.php?main_page=shippinginfo">Shipping & Returns | </a>
Wholesale |
Contact Us


Welcome!
<a href="http://www.lovebridaldresses.com/index.php?main_page=login">Sign In</a>
or Register



Powered by Zen Cart :: The Art of E-CommerceYour cart is empty


Powered by Zen Cart :: The Art of E-Commerce







Home
Wedding Dresses
Bridesmaid Dresses
Prom Dresses
Evening Dresses








Currencies
US Dollar
Euro
GB Pound
Canadian Dollar
Australian Dollar
Jappen Yen
Norske Krone
Swedish Krone
Danish Krone
CNY
Categories
Accessories
Intimates
Kids
Kids Clothing
Men
<a class="category-top" href="http://www.lovebridaldresses.com/outerwears-c-7.html">Outerwears</a>
Special Occasion Dresses
Wedding Apparel
Women Dresses
Women's Clothing
Featured - <a href="http://www.lovebridaldresses.com/featured_products.html"> [more]</a>
Chic Beading A-line Tiered Ruffles Knee-Length V-Neck Sandra's Bridesmaid Dress
Chic Beading A-line Tiered Ruffles Knee-Length V-Neck Sandra's Bridesmaid Dress$321.00 $128.00Save: 60% offBeautiful Colourful Wedding Guest Book<a class="sidebox-products" href="http://www.lovebridaldresses.com/beautiful-colourful-wedding-guest-book-p-86.html">Beautiful Colourful Wedding Guest Book</a>$39.00 $14.00Save: 64% off<a href="http://www.lovebridaldresses.com/charming-tiered-strapless-appliques-court-train-wedding-dress-p-296.html"><img src="http://www.lovebridaldresses.com/images/_small//dress3/Wedding-Apparel/Charming-Tiered-Strapless-Appliques-Court-Train.jpg" alt="Charming Tiered Strapless Appliques Court Train Wedding Dress" title=" Charming Tiered Strapless Appliques Court Train Wedding Dress " width="130" height="173" /></a>Charming Tiered Strapless Appliques Court Train Wedding Dress$489.00 $249.00Save: 49% off

<a href="http://www.lovebridaldresses.com/">Home</a> ::
Accessories ::
Silver Round Shape Crystal Earrings
.jqzoom{
float:left;
position:relative;
padding:0px;
cursor:pointer;
width:301px;
height:300px;
}
<a href="http://www.lovebridaldresses.com/silver-round-shape-crystal-earrings-p-21101.html" ><img src="http://www.lovebridaldresses.com/images//dress3/Accessories/Silver-Round-Shape-Crystal-Earrings.jpg" alt="Silver Round Shape Crystal Earrings" jqimg="images//dress3/Accessories/Silver-Round-Shape-Crystal-Earrings.jpg" id="jqzoomimg"></a>
Silver Round Shape Crystal Earrings
$24.00 $10.00Save: 58% off

Add to Cart:

<a href="http://www.lovebridaldresses.com/silver-round-shape-crystal-earrings-p-21101.html" ><img src="http://www.lovebridaldresses.com/rppay/visamastercard.jpg"></a>

Category: EarringsGender: WomenMaterial: AlloyColor: SilverSize(cm): 2.3*1.3Warm Tips:Color &amp; Style representation may varyby monitor. Not responsible for typographical or pictorial errors.
[url=http://www.lovebridaldresses.com/silver-round-shape-crystal-earrings-p-21101.html]/dress3/Accessories/Silver-Round-Shape-Crystal-Earrings.jpg
Related Products
Alloy Phoenix Shape Diamond-Set Necklace<a href="http://www.lovebridaldresses.com/alloy-phoenix-shape-diamondset-necklace-p-17071.html">Alloy Phoenix Shape Diamond-Set Necklace</a>
Luxurious Full Diamond Alloy Ball Special Ring
Luxurious Full Diamond Alloy Ball Special Ring
<a href="http://www.lovebridaldresses.com/new-arrival-womens-candy-color-clutch-hand-bag-p-21083.html"><img src="http://www.lovebridaldresses.com/images/_small//dress3/Accessories/New-Arrival-Women-S-Candy-Color-Clutch-Hand-Bag.jpg" alt="New Arrival Women'S Candy Color Clutch Hand Bag" title=" New Arrival Women'S Candy Color Clutch Hand Bag " width="160" height="160" /></a><a href="http://www.lovebridaldresses.com/new-arrival-womens-candy-color-clutch-hand-bag-p-21083.html">New Arrival Women'S Candy Color Clutch Hand Bag</a>
Exquisite Purple Rhinstones Concise Design PU Handbags<a href="http://www.lovebridaldresses.com/exquisite-purple-rhinstones-concise-design-pu-handbags-p-18159.html">Exquisite Purple Rhinstones Concise Design PU Handbags</a>

Write Review

<a href="http://www.lovebridaldresses.com/index.php">Home</a>
Shipping
Wholesale
Order Tracking
Coupons
Payment Methods
Contact Us
Wedding Dresses
Bridesmaid Dresses
Mother &amp; Guests Dresses
Flower Girl Dresses
Weekly Specials
<a href="http://www.lovebridaldresses.com/silver-round-shape-crystal-earrings-p-21101.html" ><IMG src="http://www.lovebridaldresses.com/includes/templates/polo/images/payment.png" width="810" height="58"></a>
Copyright © 2012 All Rights Reserved.

you little factor.
But why should you give upwards that excellent top because doing so isn't the things you want? Finding the actual cake top you want is specifically hard if your primary wedding is known for a very specific theme or simply color scheme.
The so next best activity if you should not find best - will be create perfect.
Uncomplicated Fix
Upon having found in which nearly suitable top, you could start to add a lot of silk or even fresh flowers that may match or perhaps compliment the colors from the cake for which you commissioned? Many times you is able to do this oneself or get your florist exercise.Either way you decide to pursue it, by arranging the additional flowers close to your choice with the cake topper you can actually suddenly have a very good top this really is unique in your wedding.The egypt flowers compliment all of those other wedding cake by adding a dash of color for the top -- which sometimes has an excessive amount white in the basket anyway.
Breathtaking Elegance
To feature even further beauty in your cake, consider ordering cake diamond jewelry that was designed to accent the complete cake by having an element for every single tier.It's a tasteful touch that not every one provides discovered nevertheless.
Another option so it certainly due for any return in order to popularity is usually to rent a water come that sits from the first and additionally second tier belonging to the cake.Then customise it as a result of wrapping several silk flowers about the pillars which holds the very best tier.These uncomplicated tips will let you add color choice and elegance for your wedding wedding cake topper, whilst your entire wedding cake.

Earrings blog

<a href="http://lesndes9.webs.com"> Outerwears </a>

About lovebridaldresses.com blog
Back to top
View user's profile Send private message Visit poster's website AIM Address Yahoo Messenger MSN Messenger
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