Locate In 1 Spreadsheet and Replace In Another

[adventurehouse]

I'm having to make some changes in my spreadsheet to match the new accounting system. I created a spreadsheet a number of years ago, and a brief breakdown of this spreadsheet shows a 5 digit catalog number and a two digit vendor number.

Sample:
A B C D E F G H
17 23421 'TEC 05/39 0 $50.00 39 05

So labels were created for each inventory item with the 5 digit number. So the problem is that over the past year I've combined A and B with a divider "-" making what would be in the sample above 17-23421. This is fine, but I've created a number of new inventory labels with this number, but I have thousands of old labels with just the 5 digit number.

To take inventory, we simply scan the labels into a simple spreadsheet and now I have a mix of old and new.

IE:
23421
10-99845
45888
78-99435

What I'd like to do is take this simple inventory spreadsheet, and have it locate the 5 digit number in the main spreadsheet and once found return and append the simple spreadsheet with the 2 digit vendor number.

I've tried MATCH, but that didn't seem to work very well. Any suggestions?

[MrProgrammer]

E:
23421
10-99845
45888
78-99435

Your task will be easier if you put the new and old values in separate columns.
[Tutorial] Rearrange rectangular data values

What I'd like to do is take this simple inventory spreadsheet, and have it locate the 5 digit number in the main spreadsheet and once found return and append the simple spreadsheet with the 2 digit vendor number.

Consider using VLOOKUP. [Tutorial] VLOOKUP questions and answers

I've tried MATCH, but that didn't seem to work very well.

That is vague. You should explain in more detail what you did and did not accomplish with MATCH.

Any suggestions?

For further assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[adventurehouse]

Thanks for the help in getting the information needed to make this work.

adventurehouse wrote:
E:
23421
10-99845
45888
78-99435
Your task will be easier if you put the new and old values in separate columns.

I actually do have these in separate columns. The two digit vendor number in column A and the 5 digit stock number is in column B. Combining the numbers into the 10-99845 in a third column and which is used to create the inventory label.

adventurehouse wrote:
I've tried MATCH, but that didn't seem to work very well.
That is vague. You should explain in more detail what you did and did not accomplish with MATCH.

I tried match...and what I got in return wasn't the proper line number:
=MATCH(C26;'file:///Volumes/AH_Files/Accounting/2019/Database Adjustments/Users/adventurehouse/Dropbox/Databases/catalog_complete_accountedge.xls'#$Catalog.B10881:B33866;-1)
Returned a line number that wasn't even close to matching the 5 digit number from the simple spreadsheet and the complete spreadsheet.

Anyway, I'm trying to take the complete inventory spreadsheet and place into the scanned simple spreadsheet to do a VLOOKUP. Playing with it now, although I can't get the syntax right to make it work.

[RusselB]

=MATCH(C26;'file:///Volumes/AH_Files/Accounting/2019/Database Adjustments/Users/adventurehouse/Dropbox/Databases/catalog_complete_accountedge.xls'#$Catalog.B10881:B33866;-1)
Returned a line number that wasn't even close to matching the 5 digit number from the simple spreadsheet and the complete spreadsheet.

I can't say for sure, but a common problem when using a different file in a formula, for Calc, is the fact that the formula uses the data that is in the last saved version of that file.
Please also note that your filenames show the .xls extension, which is a Microsoft Excel extension, and Excel formulas do not always work in Calc.
I highly recommend that you change your working files to use the .ods extension, which is the Calc default. It may make things easier to get a working solution.
Personally I try to stay away from using formulas that use references to anywhere except the local (current) spreadsheet.

Since you do reference a different file, it would be most helpful to have samples of all of the related files, as that way we can see just how the different files are interrelating.