PDA

View Full Version : Combining and aligning data from multiple sheets



Zig208
05-11-2010, 12:39 PM
I have a large number of worksheets containing date series data (first column has date, second has value corresponding to that date). I'm looking for a macro which will combine these worksheets and align the data so that the data for any given date end up in one row. I've found a macro which combines multiple sheets into one, but don't know how to align the data by date. Any suggestions are welcome. Also...I'm trying to do this with Excel 10 (64 bit) because the resulting combined worksheet will be very large. Thanks in advance for any guidance.

rbrhodes
05-11-2010, 07:51 PM
Hi Zig#

1) Are these 'WorkSheets' all in one 'Workbook' eg one file? (A lot of people use the words interchangeably)

1a) If they are in separate WB's are they all in one folder (directory)?

Zig208
05-11-2010, 07:58 PM
Hi DR and thanks for your reply. The worksheets are all separate files in one folder. One additional piece of info is: the individual worksheets will span a variety of different dates, but I'd like to be able to select a range of dates and have the macro pick out those values that correspond to those dates. Thanks again, Zig

rbrhodes
05-11-2010, 11:42 PM
Ok, so 'Worksheets' is actually 'Workbooks'.

Just so you know:

- a workBOOK is a file
- a workBOOK is composed of min of 1 workSHEET sometimes many more

This is a common problem of vernacular, but when coding for something it is _fatal_ which is why it was my first question. The second question is usually: "What didn't you tell me?" but you got that one <grin>

And as always - you get three OH-BY-THE-WAY's included free - after the 3rd one they will cost you dearly!


I'll have a look.

rbrhodes
05-11-2010, 11:50 PM
Questions:

- Drive\Directory name is same everytime, or browse for it each time?

Put path in code or ask user to select path?


- Are WB's (files) named with dates or open all files?

Look for files with 'date' in name or open each and every file and look for dates in file

- 1 sheet per WB or check all?

Always Sheet1 or could be multiple sheets per WB (file). Do all

- Do you have macro you speak of and example files?

Post or email them to me

'//End

Zig208
05-12-2010, 10:25 AM
Hello dr,
I've attempted to answer your questions and provided example files in an email. Please let me know if you need further info.
Best,
Zig

rbrhodes
05-12-2010, 02:58 PM
Hi Zig,

I got notice of this post but no email?

Decode this for my address:

ExcelVBA <shift + 2> (the at sign) dot shaw dot ca

Zig208
05-12-2010, 03:10 PM
Hmmm... I sent it to your ExcelVBA@joe... address from your website. Did you get it there?
Best,
Z

rbrhodes
05-12-2010, 04:20 PM
got it & will have a look...

Aussiebear
05-12-2010, 11:05 PM
Please don't use a public forum for private discussions. Ifyou take the time to raise an issue publicly..... then keep it there. Don't want it to be discussed in public then don't raise it here.

rbrhodes
05-13-2010, 12:23 AM
Hi AB

Noted.

Sorry for taking it 'off the board' but continuing here.

Dusty