Extract data from all rows with specific number in col one

colin · Post by **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

edit this topic's initial post · Post by **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.

edit this topic's initial post · Post by **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

colin · Post by **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.

Extract data from all rows with specific number in col one

Extract data from all rows with specific number in col one

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

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

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