I get a regular data export in a CSV format, which I like to pull into excel and report on via helper columns and pivot tables.
I do this by linking to the csv file, and copying the cell contents from the columns of data I'm interested in.
I use something like =if('file path[filename.csv]Sheet1'!A1<>"",'file path[filename.csv]Sheet1'!A1,"")
I then auto fill down the entire column.
This works well, and pulls the data into my current sheet, where I can then add a helper column, and run a pivot table from it. Once a week, I overwrite the CSV file with a fresh one, and refresh the pivot, to produce the reports I require.
The problem I have is that occasionally, the business department that produce the CSV that is my source, will add a new field to the data, and as a consequence, all my cell references then get broken, and I have to go and change all my formulas to accomodate the new field, even though I've no requirement for it at all.
All of the columns in the CSV have unique column headers.
What I would like to do is use a formula to find the specific column that I require in the CSV, and then pull it into my worksheet.
Catalogue Number Price Purchase Date Sales Office 1 £500.00 11-Sep EMEA 2 £606.00 8-Aug APAC 3 £454.00 3-Jul NA 4 £2,132.00 29-Jan NA 5 £548.00 30-Nov APAC 6 £514.00 23-Apr NA 7 £36.00 3-Feb EMEA 8 £998.00 11-Oct EMEA 9 £1,454.00 11-Sep EMEA
In the above example (dummy data), for instance, I'd like to identify the column headed 'Price' (D1:D10), and then pull the entire column into a new sheet.
I've tried experimenting with HLOOKUP, INDEX and MATCH, but can't quite work out how to get the entire column. In the example below, you can see what I've been trying, and whilst I can identify the right column, I cant get the autofill to successfully iterate the formula in the way I would require.
Formula "=MATCH("Catalogue Number",5:5,0)" Output 3 Formula "=MATCH("Price",5:5,0)" Output 4 Formula "=HLOOKUP("Price",C1:F10,2)" Output 500 Formula "=HLOOKUP("Price",$1:$1048576,2,0)" Output 500 500 Formula "=INDEX($1:$1048576,2,(MATCH("Price",$1:$1,0)))" Output 500 500 500 500 500 500
I'm using Excel 2010.