Merge several sheets, with different items

This is for me the problem of the decade. I have never spent more time on a problem more than this one. It is driving me crazy and I am feeling a heavy and maddening pressure on my head. I am about to punch my 500$ monitor. I have called everyone I knew and still nothing. You are my last hope.
In my opinion whoever can solve this problem is super genius, and not only the man of Excel, but the man of office-computing.

Here we go:

I have a workbook with 7 sheets in it. All of the sheets have only two columns, the first column contains plain text and the second column contains numbers. Each of the sheets are alphabetically ordered based on the first column. They look like this:
Sheet1
Title Score
A 2
B 8
C 52
D 10

Sheet2
Title Score
A 54
C 2
D 12
F 2
G 3

Sheet3
Title Score
A 12
B 2
C 89
E 1
F 10
G 91
H 1
I 12

Now What I want to do is to merge all these sheets, into a new one, in a way that the result sheet has two columns, Title and Score, as well. And at the same time, I want all the items that are present in first columns of each one of the other 7 sheets(3 in this example) to be present in the first column of the result sheet as well. And I want the Score column of the result sheet to be the sum of score value(s) for each Item. So the result sheet for this example, accordingly, should be like this:

Results_Sheet
Title Score
A 68
B 10
C 143
D 22
E 1
F 12
G 94
H 1
I 12

What should I do?
Help me, the pain is eating me up inside.

Originally Posted by oeldere

post an excel example, without confidentional information, the forummembers could work with.

No problems at all. The actual file doesn't have any confidential information in it.
Here it is:
Book2.xlsx

Sorry, I forgot to rename sheet7 to Results_Sheet, in the attached file. Please consider sheet7 as the place where you put the final results.
Am I clear about what I want or I should explain more clearly?

I called a friend, he is really into Excel, and knows lots of stuff. He just told me to press Ctrl+A on a sheet then Ctrl+C on it (Copy the content of that sheet) and then paste it into the results page. Then copy the contents of the second sheet, and paste it on the results sheet like for the first sheet.
But this way is no good, cause not only the names are removed without notice, also all the scores are gone without any further operations on them.
I am gonna die. I have been working on this 10 hours non-stop.

Originally Posted by oeldere

Off course you can copy / paste the data of all 7 sheets into the master sheet.

If you are in a hurry this is the easied way, for the moment.

Do you have to do, the action very often?

I have done this, and the final thing was awful. You can try that on the file that I have attached earlier. Go ahead and try this:
Select all the items of the first sheet, copy and paste them into the final sheet (sheet7), then copy and paste all the content of sheet2 on the content of sheet7 in the same fashion. Now scroll down to the final row. You see? Two similar items next to each other? And also do a little calculation and you will see that the scores column is not actually the sum of the scores from sheets 1 and two.

Originally Posted by mike7952

This should do it

Please Login or Register  to view this content.

Mike you are my savior man. How can I thank you? In whatever way that it is going to be, I know that I can't thank you enough. I ran the script for the first time, there was an error, then I renamed sheet seven to 'Results_Sheet', and boom, there it was.

You are really something. I wish I had a little bit of your talent. Thanks. I wish you well, wherever you are.

Haaaah. Now I am happy.

Originally Posted by FDibbins

hi

1st you have to get all the names onto sheet6. what I did was just copy them all over to sheet6 column A, put each sheet's name underneath each other and then used data tool/remove duplicates so that only unique names remained. (this may be a pain to begin with, but should be easy to maintain after that)

then i used this really ugly formua to add from each page (it would have been far simpler if all the names were in the same rows lol)...
=IFERROR(VLOOKUP(A2,Sheet1!A2:B40,2),0)+IFERROR(VLOOKUP(A2,Sheet2!A2:B40,2),0)+IFERROR(VLOOKUP(A2,Sheet3!A2:B40,2),0)+IFERROR(VLOOKUP(A2,Sheet4!A2:B40,2),0)+IFERROR(VLOOKUP(A2,Sheet5!A2:B40,2),0)

Im sure some1 will come up with a far more eligant solution for you, but his will work

Yes, it works, but I think I have to repeat it for all the items right? This is actually, exactly what a software engineer and a dear friend, mentioned to me minutes ago. But He didn't provide the code like you did here, Cause he mentioned that it would be impossible to tell me the correct code over the phone, so he told me that first thing tomorrow morning would be the solution to this problem. But now I might have to call that off. Cause mike7952 has provided me with the best possible solution here. And it was like a load off my brain!

Thank you all the the loveliest members of ExcelForum.com. Especially mike7952 and FDibbins. Love you all.

Hi
Is it possible to post here? even-though this post is closed?

Well, I have found myself in problem again. This time I tried to use this exact algorithm and code on a much bigger spread sheets, two of them with one containing about 143000 rows(items) and the other containing about 420000 items. What I want to do is exactly the same thing, two columns, the first having the names of the items in it and the second having the number of times each of these items has occurred. Now I want to combine these two sheets into one. I tried using mike7952's code on this list too, but it gives me and error of "type mismatch" and then yellows out the line which says:

Please Login or Register  to view this content.

What should I do?
How can I change this code so it is useful for much bigger lists?

I may be able to help you if you could attach your bigger sample. It is difficult to see the error with out the actual sheet.

Okay,
Can you mimic the layout of the actual to the attached? There is an attachment with a code, so I can modify it if it resembles the actual data.
Remember, we do not need your actual data, but a sample which shows us the layout and format of the data. You need to remove all confidential information.
If the code works on the attached sample, you need to increase the data so that to find out where the error is.

I have a suggestion though
You could run a simple script, which would fill two sheets of your workbook with random data. 140000 random words (3 to 10 characters) for the first column of the first sheet, and the same exact amount (140000) random numbers (1 to 6 digits) for the second column of the first sheet. Then 420000 random words (3 to 10 characters) for the first column of the second sheet, and the same exact amount (420000) random numbers (1 to 6 digits) for the second column of the second sheet.
This would give you a similar set of data, and the error.

I am generating one similar file.... hang on please.

You can try by Pivot table also.
File attached.

xinx,
Sorry! I have to shoot-off now and will log in tomorrow morning.

Originally Posted by avk

You can try by Pivot table also.
File attached.

Can you please explain how this could be used? It looks very amazing.

All five sheet data shifted in Consolidated sheet & used simple Pivot table.

Originally Posted by avk

All five sheet data shifted in Consolidated sheet & used simple Pivot table.

Thank you AVK, I am currently studying this page, to see how I can get one pivot table for my sheets. Thank you so much.

I will keep you informed about the results and what happened.

Morning everybody.
I have been working on it for some hours now, and I still have problems:
First I tried Mikes's algorithm and code, but it kept breaking no matter what I did. Now I thought that avk's pivot table idea might work so I did this:

I tried consolidating all my sheets, using Excel's built it consolidation tool (Data -> Data Tools -> Consolidate), but what actually happens, is that first Excel freezes (because my data sheets are so big) and then it gives me nothing on my results sheet. I think it happens because of some overflow or something.

Then I tried PivotTable and PivotChart Wizard, and I got a good looking pivot table, but the problem with it was that the numbers it gave me were not correct and they did not correspond to the actual sum of each items' number.

What should I do?
Does anyone has any idea how has avk made his pivot table?
I am frustrated...

Good news everyone! (Futurama refrence)
Professor-Farnsworth.png
I have asked a friend who happens to have a very strong computer to generate an exact copy (clone) of my original datasheets, without any copyrighted material in it. So I can easily share it with you. You could consider it this way: it is exactly what I am working on.

Go ahead and see for yourself. Absolutely nothing works on this file. No way you could put the data together.
What should I do?

You could download the sample from here, cause the forum has some restrictions about big files:
http://xeebexa.1z.com/BIG_Sample.xlsx

Thank you.

Use this code. I have tested it on your data. It is too large to attach it with a code.

Please Login or Register  to view this content.

xinx,
Please do not reply with quote, just a reply will do.
You need to show me which line does not tally.

I have tested it again using a PT and comes up with the same result.

I do not know how and why the code does not work on the actual data. The other option would be to use as Oeldere suggested- need to have a consolidate sheet macro with a PT.

Okay,
As an option, which you can fall back if the code does not work. You need to try this code on your actual data. The code is very fast. It consolidates all sheets except The Pivot Table and consolidation sheet in to one sheet. The consolidation sheet is linked to the PT sheet. I do not know how comfortable you are changing some of the sheet names. You can compare the result from the first code with this code.

I think it may be easier for you if you just a create a sheet with the name "Consolidated" in your actual data and run it. I trust you know how to create a PT from the consolidation sheet.

xinx,

Make sure that you do not have gaps in your data(Row).In other words, there should not be a complete blank row in your data.