#1
|
|||
|
|||
Help for excel or access
I have multiple files of each year like in pic 2000,2001,2002 and want to merge in single file like "final file" i thought of copy paste but there are 100's of names and each name is on different position so now how can I make a single file like in pic "final file" |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
Quote:
|
|
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 |