Extract data from all rows with specific number in col one

Discuss the spreadsheet application

Extract data from all rows with specific number in col one

Postby colin » Fri May 16, 2008 1:06 am

Can't find a solution to this. Tried before but perhaps didn't explain myself too well.

Write a formula in a cell b2, see "sample of departure instance extraction.ods", that extracts data from DEPARTUREINSTANCES.csv (saved as ods to allow for upload).

The idea is that the formula will:

- Reference A2 to look for the appropriate FKey number. So if there is a 1 in A2 the then selected data from rows where the Fkey is one is aggregated along with some html.

- Should be able to copy the formula down to b3...b30 so that data is aggregated from Fkey rows 1...30.

Please have a peak at the spreadsheets. Would surely appreciate any help.

Thanks

Colin
Attachments
DEPARTUREINSTANCE - Copy.ods
Please change extension to .csv
(20.08 KiB) Downloaded 53 times
sample of departure instance extraction.ods
(10.18 KiB) Downloaded 95 times
colin
 
Posts: 9
Joined: Mon Apr 28, 2008 7:33 am

Re: Exract data from all rows with specific number in col one.

Postby Villeroy » Fri May 16, 2008 1:13 am

Short answer: This is exactly what relational databases are made for and where spreadsheets tend to fall short since there is no concept of relations between unique entities.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29300
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Exract data from all rows with specific number in col one.

Postby Villeroy » Fri May 16, 2008 1:38 am

Longer answer: In fact your csv data look as if they have been exported from a database. Is that database accessible somewhere in your network?
If the exported csv is the only format you have at hand and you want to lookup DEPARTUREINSTANCE_PKEY in a spreadsheet you can import the csv into the spreadsheet and then use the usual lookup techniques which are commonly used in Excel, Calc and many other spreadsheets, namely function VLOOKUP or a combination of MATCH and INDEX. These techniques are documented in literally every tutorial on spreadsheets since the 80ies.
For import I would use method #1 from my tutorial on links
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29300
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Exract data from all rows with specific number in col one.

Postby colin » Sat May 17, 2008 2:38 am

Thanks for getting back to me on this.

Absolutely right on using a data base however I am using a shopping cart with an automated update function that grabs a csv file and then extracts the data to a rather complex set of tables...there are 118 tables in total which are all interrelated and it just seems a whole lot easier to resolve this one formula then to start messing with tables and code.

The csv data is extracted by an automated process every three hours from xml feeds which themselves are varied and complicated.

Anywho, the spread sheet integrates the csv data with html and other code in preparation for auto upload by the carts automated updater.

One formula and everything will work just fine...already tested...just need the darned formula.
colin
 
Posts: 9
Joined: Mon Apr 28, 2008 7:33 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests