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.
[Solved] Find and import data from other sheet
-
- Posts: 19
- Joined: Sat Jun 09, 2018 6:10 pm
[Solved] Find and import data from other sheet
- 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.
Re: Find and import data from other sheet.
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.
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
---
Lupp from München
-
- Posts: 19
- Joined: Sat Jun 09, 2018 6:10 pm
Re: Find and import data from other sheet.
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.
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.
Re: Find and import data from other sheet.
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.lesbentley wrote:... so I cut and pasted your "helper" column into column N and it still seems to work correctly.
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
---
Lupp from München