[Solved] Find and import data from other sheet

Discuss the spreadsheet application
Post Reply
lesbentley
Posts: 19
Joined: Sat Jun 09, 2018 6:10 pm

[Solved] Find and import data from other sheet

Post by lesbentley »

Find and import data from other sheet.

In Sheet2, column E contains a list of file names, a file name may repeat more than once in the column. Example:

S6042240_14.zc
S6042241_05.zc
S6042241_05.zc
S6042241_20.zc
S6042241_20.zc
S6042241_33.zc
S6042241_48.zc
S6042242_03.zc

Each file name also appears in in column A of Sheet3 (but only once). In column M and N of Sheet3 are latitude and longitude. In other words each file name in Sheet3 Column A has an associated latitude and Longitude in N and M respectively.

What I would like to do is to take the latitudes and longitudes from Sheet3, and place them in Sheet2, in columns I and J respectively, in the same rows as their respective file names.

Can this be done? How?

Attached below is an example of the spreadsheet. In Sheet2 I have filled in the first few rows of the Lat and Long Columns manually as an example.

Oh, and if you are wondering what it is all about, it's to do with counting bats.
SEND-0001.ods
(24.87 KiB) Downloaded 85 times
Attachments
SEND-0001.ods
(24.87 KiB) Downloaded 94 times
Last edited by lesbentley on Tue Jun 12, 2018 3:12 am, edited 3 times in total.
LibreOffice Version: 6.2.0.3 on Windows 10.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Find and import data from other sheet.

Post by Lupp »

You can do it using VLOOKUP(), but a combination of MATCH() with INDEX() may be more efficient. (?)
See attached reworked file. The formula is prepared for up to 10000 data rows in the third sheet.
Attachments
aoo93897_SEND-0001_reworked_1.ods
(39.1 KiB) Downloaded 123 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
lesbentley
Posts: 19
Joined: Sat Jun 09, 2018 6:10 pm

Re: Find and import data from other sheet.

Post by lesbentley »

Thanks, this is a tremendous help! Saves me a shed load of work.

I needed to keep column G empty, so I cut and pasted your "helper" column into column N and it still seems to work correctly.
LibreOffice Version: 6.2.0.3 on Windows 10.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Find and import data from other sheet.

Post by Lupp »

lesbentley wrote:... so I cut and pasted your "helper" column into column N and it still seems to work correctly.
I cannot confirm your assumption. With Copy/Paste and then Del the original (or with Cut/Paste) the helper-based formuale using INDEX() should fail. The correct way to relocate helpers is to select them and then to Drag/Drop them with the mouse. (In case of a complete column drag the first cell to to the first cell in the new position.) The references in formulae depending on the helpers will then adapt automatically.

The formulae using VLOOKUP() don't depend on the helpers anyway.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply