Locate In 1 Spreadsheet and Replace In Another

Discuss the spreadsheet application

Locate In 1 Spreadsheet and Replace In Another

Postby adventurehouse » Wed Feb 20, 2019 3:55 pm

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?
OpenOffice 4.1.2 on Mac Os X
adventurehouse
 
Posts: 14
Joined: Thu Jun 23, 2016 4:42 pm

Re: Locate In 1 Spreadsheet and Replace In Another

Postby MrProgrammer » Wed Feb 20, 2019 4:41 pm

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.
[Tutorial] Rearrange rectangular data values

adventurehouse wrote: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

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.

adventurehouse wrote: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
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3619
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Locate In 1 Spreadsheet and Replace In Another

Postby adventurehouse » Wed Feb 20, 2019 5:10 pm

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.
Attachments
sample_inventory.xls
(55.5 KiB) Downloaded 8 times
OpenOffice 4.1.2 on Mac Os X
adventurehouse
 
Posts: 14
Joined: Thu Jun 23, 2016 4:42 pm

Re: Locate In 1 Spreadsheet and Replace In Another

Postby RusselB » Thu Feb 21, 2019 2:23 am

=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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4875
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 37 guests