Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-29-2019, 03:06 AM
rocky67 rocky67 is offline Help for excel or access Windows 8 Help for excel or access Office 2007
Novice
Help for excel or access
 
Join Date: Dec 2019
Posts: 2
rocky67 is on a distinguished road
Default 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"
Attached Images
File Type: png Untitled.png (96.9 KB, 27 views)
Reply With Quote
  #2  
Old 01-04-2020, 05:43 PM
BobBridges's Avatar
BobBridges BobBridges is offline Help for excel or access Windows 7 64bit Help for excel or access Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 01-09-2020, 01:04 PM
rocky67 rocky67 is offline Help for excel or access Windows 8 Help for excel or access Office 2007
Novice
Help for excel or access
 
Join Date: Dec 2019
Posts: 2
rocky67 is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
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.
thanks a lot
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel or Access hollies Excel 17 07-18-2017 03:21 AM
Help for excel or access 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
Help for excel or access 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft