Searching Table Array/ Reading CSV file

[ACreaner]

Hi,

I'm looking for some help both on best approach and method on the below - sorry for the long description!

Situation

The situation is that I work as a contractor and do my own payroll.  The Revenue issue a CSV file annually with 39 values for tax allowances etc.  I use Excel spreadsheets for my payslips and pay myself monthly. Each year, I copy in the content of the Revenue CSV file into my January spreadsheet and use it's content in that to calculate the payslip. The February spreadsheet and onward look back at the previous files to handle the cumulative allowances. Once set up, this has worked fine for me for the last few years. This year the file includes additional fields (not added at the end) so my use of HLOOKUP as it stands will not work.  I could just change the names of the Column headers so the right data is found but my concern is that this leaves me always changing it hard-coded whenever they change the layout.  

The CSV file contains 4 rows with first and third rows being headers for the second and fourth rows.  The file when read in, has 6 columns in row 1 and 2 and 33 columns in rows 3 and 4. In my payslip file as it stands, I copy in this data on a separate sheet and it is a table array above which I have a row with fixed headings that I use with HLOOKUP and then select the value in row (2 or 4) I need.

Desired Result

I'd like to be able to make it such that it can handle future changes in the CSV file where headings and their values may change location/ layout again. I was thinking that it would be good to be able to search the table array to find the appropriate header (on either row 2 or 4 of the Table array) and select the value beneath.

The headings (either on Row 1 or Row 3) are terms like the below with the actual value in the cell beneath each one (either on Row 2 or Row 4). Some values are blank.

agentTain	taxYear	totalRPNCount
	2020	1
employmentID	employmentCessationDate	rpnIssueDate
1		01/12/2019

On approach, is it best to try and pursue a good method to find the headers and their values or is there a better way altogether to handle reading the data from the CSV file? I've spent a while looking at Match and Index but the point is that the column with the header and the associated value is subject to change though it is likely that the header row will always be either row 1 or 3 (with associated data on row 2 or 4).

All advice appreciated, thanks a lot.

[#2]

@ACreaner 

A few methods.

[#3]

@ACreaner 

First, I commend you for doing your own payroll. Back in the 70s, before Excel even existed, I was using one of the precursor spreadsheets (just for fun, mind you) to do my own taxes. And that's when I learned the benefits of creating a tax table, having the cutoffs for each income level and associated tax percent etc., all there, so I could do "what-if" analyses based on different income levels, and so forth. It was fun, but over time I realized I'd learned what I could, and my income got more complicated, so I moved to tax packages. My point is not that you should do that; rather, I'm just saying I appreciate how your goal is to have a workable table that you can refer to using HLOOKUP or INDEX and MATCH.... You are being wise to avoid hard-coding as much as possible.

That said, my career also included a stint as the director of the HR and Payroll database of a major corporation, and during that time I extended my learning of the value of business tables, not only to avoid hard-coding data that can change from year to year, but also to help make SQL queries legible/understandable. That applies here, in the sense that you want your Excel formulas to be things you can make sense of.

All of which leads me to wonder if you wouldn't benefit from taking that CSV file you get from Revenue sources and converting it (once each year) to a table of your own design, a table with only one header row instead of two, and a table with headers that are clear. You could even use HLOOKUP or whatever to do that conversion, so it would be consistent so long as they were consistent from year to year; changing it only when they changed. But YOU would have a single table that conformed to good Excel principles (single header row), and could even then use the Name....Create capability to assign names to the various cells from that Revenue table....and your formulas would then be intelligible.

You asked for any/all advice, so I thought I'd throw those thoughts into the hopper.

If you'd like to pursue further....I'd be interested in seeing what that CSV file looks like in its entirety.