Merging multiple worksheets into one

[Rogue]

@SamT -- NOTHING is ever that easy


1. There are different number of rows on the source worksheets
2. Data in not 1-1, 1-N, maybe not even N-N between the sheets. For example,

regno=200044 is repeated 13 times in 'extract-financial' (apparently for 13 different dates) and 3 times in 'extract-class' (apparently for 3 different classes)
regno=200053 is repeated 13 times in 'extract-account-submit', each with a different 'arno'
regno=200053 has 13 different trustees in 'extract-trustee'
regno=200064 has 4 'sub-names' in 'extract-name' in the 'subno' field

3. Only regno's with class=110 from 'extract-class' are required (OP #1)
4. So I think that just 're-arranging' the columns from multiple sheets onto a single sheet by copying is not going to line up the rows even after sorting by regno

BUT ... then again I could be wrong

All those points are correct apart from #3. The Collected Data book (47mb) only has data for regno numbers with class=110. I filtered the original data between the OP and now. So, all the regnos from the Collected Data book are needed.

Well, #*(%^)#&%#@)##%^*(#*!!! I forgot the structure of that 47Mb file
First I did not download the cloud file in your last post. Not going to if it's another 47M.

I am a bit confused. You want all Regnos that have at least one instance with a Class 110

The Collected Data Book (47Mb) is only those Regnos?

You want One Table with all Data from all Sheets? Can't be done with the data structures you have provided.

If you look at my Table Headers attachment, you will see that there is no common Secondary Key that would enable consolidating records. It may be different in either the csv files or the bcp files. I dunno, you won't discuss them with us.

So. I've been looking at everything to see what we can do. I know that (with several consolidation) we can provide a table that has most things on it, But... Listing Trustees and Volunteers (By Regno & Charity name) requires another Table. The financials - Part B,also require another table .Charity Objectives would be on final Table.

These are the tables I think ATT we can iteratively consolidate into one. There is a linkage of Columns that can be used as Secondary Keys:

extract_name as table_name

extract_acct_submit

extract_ar_submit

extract_charity_aoo

extract_registration

extract_financial

extract_main_charity

You're right. The Collected Data Book is the regnos with class 110. So, all the data in the 47mb file is needed.

The cloud file in my last post is exactly the same as the Collected Data book. I just re-linked it to make sure we were on the same page.

Regarding the bcp and csv files. The original data was in bcp format (you can download from the first link in the OP). I converted that into CSV and imported it into a database. Then, I cleaned up the data and, after that, filtered it to only show data for class=110. I exported this into an Excel format. There were multiple files. Then, I merged these files into the Collected Data file (47mb) which I sent to you.

Of course, one table would be better. But, if that's not possible, then I suppose we'll have to make multiple tables.

This might not work. But, we've got a list of regno numbers in the first sheet. Can't we use a search function to find each regno number in the other sheets. When it finds a regno number, it would copy the contents of the X nearby columns and paste that data into the first sheet. Would that work? Obviously, we'll have to code for blank cells and when there's multiple instances of a regno number.


PS: I'm working on what you requested in the last post. I'll send it over soon. Sorry about the delay.

Edit: Could this help: http://sitestory.dk/excel_vba/merge-data.htm The 'Combine 2 Tables Part'? I also found this: https://superuser.com/questions/1965...ed-excel-files