[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
smw51
General User

Joined: 08 May 2008
Posts: 6

 Posted: Thu May 08, 2008 8:50 am    Post subject: Would someone be kind enough to help me with a formula? Would someone be kind enough to help me with a formula? I’m pretty sure I need to use VLOOKUP to accomplish what I need to do but I am just not getting the formula right. I have Column A with 3000 Products IDs in no particular order. Column B is Empty. Column C has 4000 Product IDs and Column D contains the Weights of those products. All of the Product IDs in Column A are in Column C, but obviously there are 1000 I don’t need. I want to search Column A Product IDs with the Product IDs information in Column C. When there is an “exact match” of Product IDs I want to insert the cooresponding Weight from Column D into the empty Cell in Column B. I’ve simplified the situation. In reality I’ve got 26 Columns of data associated with the Product IDs. I’ve been using MATCH to try to return the cooresponding Cell number. I thought once I got the Cell number I could use that information to link the Weight to the same Cell number in the empty Column B. I haven’t gotten that far because I only get MATCH to match numbers. =MATCH('AB9DE';A1:A1000;0) returns an error. =MATCH(9303;A1:A1000;0) returns the correct Cell reference. Is MATCH limited to numbers?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Thu May 08, 2008 9:03 am    Post subject: Put literal strings in "double quotes"._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
smw51
General User

Joined: 08 May 2008
Posts: 6

 Posted: Thu May 08, 2008 9:14 am    Post subject: Yes. That works! Thank you. I can now continue on my original path. I'm not sure it is the most efficient way but once I have the cell numbers of the corresponding Product IDs I should be able to build the Weight column.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Thu May 08, 2008 9:24 am    Post subject: Having the row number you can get the value of any other column: =INDEX(\$List.\$X\$1:\$X\$10000;MATCH(A1;\$List.\$B\$1:\$B\$10000;0)) If you do many lookups: B1: =MATCH(A1;\$List.\$A\$1:\$A\$10000;0) C1: =INDEX(\$List.\$X\$1:\$X\$10000;B1) D1: =INDEX(\$List.\$Y\$1:\$Y\$10000;B1) E1: =INDEX(\$List.\$Z\$1:\$Z\$10000;B1) A1 is matched once in B1 and reused three times for efficiency. btw: this is the 100% perfect use case for a relational database rather than spreadsheet._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.orgLast edited by Villeroy on Fri May 09, 2008 3:25 am; edited 1 time in total
smw51
General User

Joined: 08 May 2008
Posts: 6

 Posted: Thu May 08, 2008 5:12 pm    Post subject: I'm closer. I've spent hours trying to figure this out. I know you were trying to explain something to me with the formulas above. I don’t see how the Index function is related to what I need to do but I’m sure it is because I just don’t understand everything that can be done with it. I know where the information is supposed to go, but I can’t figure out how to get it there. I now have the Weights in one Column and in another Column I have the Cell references. I Concatenated the Column B reference to the cell references so I know the Specific Cell each Weight value needs to be in. I need some sort of formula that says “take the value in D1 and put it in B375”. D2 would go to B609, D3 to B6 and so on. The D Column row numbers will be sequential and the Bs are all over the place.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Thu May 08, 2008 7:21 pm    Post subject: Re: I'm closer. I've spent hours trying to figure this out.

 smw51 wrote: The D Column row numbers will be sequential and the Bs are all over the place.

A "formula" is the expression of a pattern. When there is none, there is no formula. It must be done by hand, entering =D1, and so on wherever appropriate. If there is a pattern to what you are doing when entering the values in column B, then you might describe it. If not, then you are out of luck.

From what you say initially, VLOOKUP might be an option IF you have your columns properly aligned [adjacent to each other] for that purpose. That is sometimes accomplished by copying a distant column and hiding the copy for printing [or vise-versa].

David.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Fri May 09, 2008 3:31 am    Post subject: Just try it out, adjusting the addresses to your actual ones. B1: =MATCH(A1;\$List.\$A\$1:\$A\$10000;0) looks up a value in A1 within column A on sheet "List" =INDEX(\$List.\$X\$1:\$X\$10000;B1) gets the corresponding value in column X on sheet "List" =INDEX(\$List.\$Y\$1:\$Y\$10000;B1) gets the corresponding value in column Y on sheet "List" without performing another lookup. Again, this is a very common task in millions of spreadsheets, but you should consider using a database._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
smw51
General User

Joined: 08 May 2008
Posts: 6

 Posted: Fri May 09, 2008 8:31 am    Post subject: Am (was?) I even close? I did try all the variations of =INDEX(\$List.\$X\$1:\$X\$10000;MATCH(A1;\$List.\$B\$1:\$B\$10000;0)) I could think of. The initial result was typically #REF, but as I began to understand it better the result changed to #NAME? Is that progress?? Initially I thought \$List was some sort of function. I found a definition for INDEX. I don’t completely understand it, but I did find one. As I re-read and re-read the explanation I began to understand (I hope) I needed another spreadsheet. If I understand what I was supposed to do correctly (a long shot at best), I made another spreadsheet named weights.xls. Column A of that ‘sheet’ now contains all the B Cell references from the original spreadsheet. That is to say it has the locations (on the other spreadsheet) I want the weights to go to. Column B of the new spreadsheet (weights.xls) contains the weight values that I want to move into column B on the original spreadsheet. Below is one incarnation of the modified formula. I’ve reversed the As and Bs. Ive used ‘\$weights.xls’, ‘\$weights’, ‘weights.xls’ and just ‘weights’ as variations of \$List. I used a ‘.’ and a ‘;’ and a ‘,’ and nothing to separate my versions of \$List from the \$As and \$Bs. =INDEX(\$weights.xls;\$B\$1:\$B\$10000;MATCH(A1;\$weights.xls;\$A\$1:\$A\$10000;0)) Am (was?) I even close?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Fri May 09, 2008 8:54 am    Post subject: http://www.mediafire.com/?tm9d03nlmuv The file contains a sheet named "List" and another sheet named "LookupSheet". Names are arbitrary. Again, you should not do this in a spreadsheet unless your data and the resulting lookups are not so important (costly). Nothing prevents any user from adding duplicates to the list, forget to insert rows, enter strings instead of numbers just to mention a few common flaws of spreadsheet lookups. Today's unlucky spreadsheet user who lost his "spreadsheet-database" due to computer failure or something: http://user.services.openoffice.org/en/forum/viewtopic.php?f=34&t=5594_________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
smw51
General User

Joined: 08 May 2008
Posts: 6

 Posted: Fri May 09, 2008 9:37 am    Post subject: Did I miss something? I downloaded match_fields.zip. It has several folders and XML documents but nothing resembling "List" and another sheet named "LookupSheet"? Did I miss something? I think I understand what you’ve said about the apparent lack of stability of a spreadsheet. Unfortunately the information comes from my suppliers on spreadsheets. It has to be output as CSV or PIPE delimited files to create a ‘database’ that powers a website shopping cart program. If those things can be done by a database program, I will look into it. Will a database program accept input from spreadsheets and output CSV or PIPE delimited files?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Fri May 09, 2008 9:48 am    Post subject: I can not understand why people continue to use Windows. I uploaded match_fields.ods, it is listed as match_fields.ods and when I download match_fields.ods it gets loaded into OOo right away, a copy beeing stored in my temporary folder. Something on your system changed the file name. As a matter of fact all ODF documents are zip-archives, but this should be no reason to rename my documents. Open with soffice.exe or rename to something with suffix .ods before double-click. You can also let OOo load the file with Menu:File>Open... OOo is smart enough to recognize the Calc document. Forget my warnings about databases and such. These data sent as csv-copy or spreadsheet-copy are a widespread disease. You can't cure it._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
smw51
General User

Joined: 08 May 2008
Posts: 6

 Posted: Fri May 09, 2008 11:57 am    Post subject: I renamed the extension .ods and got the right file. But, wait a minute!! I think I just had an epiphany. I don’t think I need any of this. Once I had the cell reference numbers I got from =MATCH(C1;\$A\$1:\$A\$3000;0) to =MATCH(C3000;\$A\$1:\$A\$3000;0). I think all I needed to do was copy that column and the weights column to a new spreadsheet and sort it numerically in ascending order based on the cell reference numbers column. The resulting weight column would be in the exact order it needs to be in. Then I just copy and paste and all my weights should line up perfectly with the cooresponding Product IDs. Was it really that simple all along? I think it was! I do appreciate everything you did and even if I don’t need it now I will still look it over and test it because I may need it in the future. Thanks again.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Fri May 09, 2008 2:43 pm    Post subject: "I can not understand why people continue to use Windows." Basically because they got caught up in it. Why were people typing in nerdy DOS commands [moi aussi] while Apple users were using a GUI? (i) It was something of a status symbol to be able to do so, (ii) people are generally reluctant to change. Why do all my country neighbours drive North American pick-up trucks to get their mail instead of a Honda, or Volkswagen, or whatever? Also, cost of reprogramming. I started about 30 years back, teaching programming in high school [I didn't know that much, and everyone else knew a lot less.] I now have a ton of collected software. Back then, I said loud and clear that if I manufactured computers I'd GIVE them to all the schools. People stick with what is familiar, and they'd use it through adulthood ...as they do. I won't live long enough or earn enough to repurchase all the new software any more than I would consider completely refurnishing my house. Mind, I might find I need less. David.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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