Help for excel or access

BobBridges · #2 01-04-2020, 05:43 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel or Access	hollies	Excel	17	07-18-2017 03:21 AM
Excel or Access for DB?	John H. Power	Word	3	08-03-2016 11:52 AM
Excel to Access to Excel for report automation	Nicholaspoe	Excel Programming	10	10-08-2013 08:13 PM
Should I uses Access or Excel?	tinkertron	Office	5	03-29-2009 08:30 PM
Excel or Access ?	inferno	Excel	0	10-03-2008 03:42 AM