VBA Table Comparison

[mthwi]

Dear Excel community,
I'll like to compare excel data.
These Excel tables are extended or reduced monthly, making it difficult to compare.
Here is an example of an Excel sheet before using the macro.
The table columns are not so important.
The names of the lines in the first column that are to be the order criterion are much more important. They're marked in yellow.

Here is the BEFORE-Sheet:
http://www.evernote.com/l/AGDXuafuCe...6MvJYVFkKlO3c/


The VBA should now always compare the first column with the column that has the most content in common (here is column M). The contents of the columns should then be distributed in such a way that the columns with the same row labels are listed in one row and thus become comparable. No lines may be lost even if they are not comparable with others.
More than 2 data sets can be compared, unlike in the example. sometimes it can be up to 12 data sets per sheet.
The number of columns that belong to a data set can also vary.
Columns A-K and M-W are to be understood as data sets.
Here is the AFTER-Sheet

http://www.evernote.com/l/AGDXuafuCe...6MvJYVFkKlO3c/

I have attached an Excel Sheet in the Links,
Thank you very much for your Suggestions

[Olly]

This works for your sample data:

Please Login or Register  to view this content.

Not sure how you want it to work with more than 2 data sets - can you provide an example of that, please?

[mthwi]

Thank you very, very much for your support.

Unfortunately I receive an Error Message.
i posted the screenshot in the Link youŽll find below.
Thanks again for the support.

Here is the same workbook extended by 2 more sheets.
Thanks again.
Tom

http://www.evernote.com/l/AGCf8ZQTuC9Ikb63f47SJkqjVuBpS9FxHJQ/"]Thank you very, very much for your support. Here is the same workbook extended by 2 more sheets. Thanks again. Tom http://www.evernote.com/l/AGCf8ZQTuC...qjVuBpS9FxHJQ/[/URL]



I am so appreciative and happy.

[Olly]

Okay - that's a lot more complicated!

Try this:

Please Login or Register  to view this content.

[mthwi]

This is awesome.
I only tested the script briefly.
I'll test it tomorrow with different numbers.

Thank you so much. I'm really looking forward to it and hope that others in the community can also benefit from this fantastic script.
You're a god.
Thank you very much.

[mthwi]

Hi Olly,
I found one Workbook where the script didn't work.
I Attached it in the existing link above.
it is calles sample2.xlsx
In there you'll find a sheet called 'Original'. That is the raw data.
And another called 'Result'. I created it by hand to show how the result of the script should look like.

http://www.evernote.com/l/AGCf8ZQTuC...qjVuBpS9FxHJQ/

Thank you again from the bottom of my heart for your help.
Bst,
Tom

[Olly]

Yeah, that source file has blanks in row 1. All your previous examples followed a pattern of repeating tables by column, with headers in row 1.

It's REALLY hard to write code for a spec which keeps changing... I can't guess at which data layouts your source data might have!

Do you have the capacity to delete the blank row at the top of the data in cases like this, or do you need the code to be changed to allow for datasets which may not start at row 1?

EDIT: That source also has multiple blanks in the header row, and inconsistent number of columns in each region.

[mthwi]

I saw that too and eliminated row 1 but it still doesn't work on my PC.
I can adapt the tables according to your code as long as contents isn't concerned.

I guess these blanks were in the other workbooks as well so were the inconsistent columns in each region?

What Do I have to do with this table to make this incredible Makro work?

[Olly]

I guess these blanks were in the other workbooks as well?

No, none of the original spec source data had blank headers for your primary column AND blanks between table regions. It makes it very difficult to work out what the table regions actually are, when they're inconsistent. This latest dataset also doesn't have an value to indicate the end of the rows, either.

Try this - it test okay for me on all the datasets you've provided so far:

Please Login or Register  to view this content.

[mthwi]

Hi, Olly,
i am sooooo sorry, here is another dataset where the script does not work properly.
It's basically nothing new.
Could you take another look at it?

http://www.evernote.com/l/AGADlvmpxv...A-bWPe_H_XZ2c/

Thank you so much.
You're awesome.

[Olly]

No. You need to do some manual adjustment to prepare that sheet. There's no way I can write code to determine which ranges you are actually interested in comparing, without any manual input at all. Even looking at it manually, I have no idea what you're expecting the output to look like: You haven't even prepared an expected output for this sheet.

I think I've taken this about as far as I can, given the ever shifting scope, and lack of specification.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'Add Reputation' to those who helped you. Thanks.