Interesting problem. I'd probably write it in VBA, because at heart I'm a programmer. But you asked this in the Excel forum, so you're probably hoping to avoid VBA. I think this might work, therefore:
1) Make up a list of all the names, combined from all the years, in your final worksheet. If you're not sure how to do that, I suggest this:
1a) Cut and paste all the names from each of the yearly worksheets (I hope you don't have hundreds of them) into column A of a new workbook.
1b) Sort column A. Now you have a bunch of names, with the duplicates all sorted together.
1c) In column B create the formula =A2=A1. Copy that formula down the whole column. Now in column B, the first occurrence of each name is marked False and all the rest are marked True.
1d) Copy column B over itself, pasting values not the formula.
1e) Re-sort the worksheet on column B. Now all the Trues are sorted together, and all the Falses separate.
1f) Delete the True rows; all the False rows have one occurrence of each name.
1g) Delete column B and re-sort column A on name.
2) Create a column for each year.
3) In each cell do a VLOOKUP that looks up, in the appropriate workbook for each year, the name in column A and returns the desired value.
If part of that isn't clear, feel free to ask.
|