Would someone be kind enough to help me with a formula?

smw51 · General User Joined: 08 May 2008 Posts: 6

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 · 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

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 · 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.org

smw51 · General User Joined: 08 May 2008 Posts: 6

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

Villeroy · 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

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 · 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

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 · 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.
Rolling Eyes

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

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 · 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.