I have an Excel file with three sheets of annual data. For example:
Sheet 1 is for year 2006
Site1 Site2 Site4
Jan 10 12 14
Feb 0 15 9
Sheet 2 is for year 2007
Site1 Site3 Site4
Jan 14 10 18
Feb 4 16 2
Sheet 3 is for year 2008
Site2 Site3 Site4 Site5
Jan 12 13 7 12
Feb 5 13 5 16
In Sheet 4, I want to combine these data under the specific Site_number (if the Site_number is unique, I want to add a column for that data). For example:
Sheet 4 should look like this:
Site1 Site2 Site3 Site4 Site5
2006 Jan 10 12 14
Feb 0 15 9
2007 Jan 14 10 18
Feb 4 16 2
2008 Jan 12 13 7 12
Feb 5 13 5 16
What would be a good way to go about this?
There are very many way of achieving your objective and with the columns apparently already sorted I would be tempted merely to add blank columns until each sheet has each Site in the same column. However instead with a lookup function something like:
=IFERROR(INDEX($C$10:$F$12,ROW(),IFERROR(MATCH(C$1,$C$10:$F$10,0),"")),"")
copied across and down to suit should work, provided Row1 has a complete list of unique Sites and, for the purposes of illustration, your original data is in the same sheet but moved down to start at Row10 and across one column (the latter to allow for manual addition of the year).
I'd suggest one sheet at a time and then merely copy and add/append into a new sheet as required.