Collect and organize data from many sheets to one unique-item list with values

Hello all.

I got some basic knowledge of Excel, but this is too much for me right now. To clarify things little bit up I included a simplified example what I'm trying to achieve. I would like to avoid Visual Basic, but if you don't see any other choice, so then use it.

The story in a nutshell. I have many worksheets which have a list (unfortunately I had to use an advanced filter to get it) of the unique items there are on the sheet. These items can occur on the other sheets also. They have some properties. I would like to list the items on the first (summary) sheet, only one time each specific item. This means I would need some way to collect them from the "sub"sheets and add to the master sheet unique list only if this item is not there already, otherwise add only the value to the row where is the same named item already.

E.g. I could be importing fruit packages from many countries. On the countrys' sheets there are unique lists of the fruits and their important values (like in the attachment): country1 - fruit1: 40 kg, fruit2: 30 kg, country2 - fruit1: 20 kg, fruit3: 60 kg. Now I want on the first tab fruit1 displayed only once and also fruit2 and fruit3, and their total weights. This is the idea explained very briefly - please check out my excel-file, which has some real random values. Below the red bar, there's the place for the desirable stuff I was talking about. I included also a blue bar, with another possibilities.

The ideal situation is to get this kind of work as much automated as possible. So when I time to time paste a new database (as columns with data) to the worksheets I wouldn't need to do pretty much anything more to get some processed information.

I'm using INDIRECT-function collecting some data at the top of the summary page. Could this help also the process I'm asking, as all the worksheet names are listed on the first sheet and you can refer those easily? But I can't figure it out if this complex procedure is really possible with Excel! Thanks for your time! I'm sorry for my European version of Excel (2003), which has ; as separators - not commas. And sorry for my very long explanation!

Originally Posted by shg

The easy way would be to put all the data on one sheet, with a column for country.

Thanks shg, but I must use this formatting at the workbook. In real life I have much more data on one sheet, and I find this way quite convenient.

So how do you guys see this case, is it possible to filter the information from many sheets?

[OT]
I didn't find whether you have a rule to use comma-style function formatting, but my version makes the semicolon-style separation. For me, 90% of the functions I see online I must manually change to work here. Very stupid from Microsoft not creating really an international program. I even think that the function names should be international, like coding normally is...
[/OT]