selection of suppliers, need help

Hallo all competent knowledge carrier!

I have the following Problem:

I have a table which looks like this:

mat.group:_564
supplier:_____alpha____beta____cesar____dora
volume:_____2000_____1500____3500____1200

mat.group:_789
supplier:_____beta____cesar_____gamma
volume:_____2000_____1500_____3500

mat.group:_123
supplier:_____alpha____cesar_____dora
volume:_____5000_____2000_____1000

Now I would like to make an extract which shoul look like this:

Supplier:_______alpha____beta____cesar____dora_____gamma
mat.group:

564___________2000_____1500____3500____1200________0
789______________0_____2000____1500_______0_____3500
123___________5000________0____2000____1000________0

Total_________7000_____1500____7000____1200_____3500

That means, that I want to present all existing suppliers, without doubling them in order to have an clear array with total volume per supplier.

Could somebody help me please? I tried it per excel-function "IF", but the if-loops are limited....

Can somebody please post me a solution by excel-function via macro?

much thanks in advance

Yes, no problem

please find enclosed my excel-sheet

thanks in advance

bartek bartek

Hallo all

could somebody please help me regarding my problem?

thanks in advance

bartek

Hello Leith Ross,

first thanks for your effort! It works so far But I have some further requirements regarding that table.

1. I want to uptade the figures without clicking on a button. I have read something about like an eventmakro. To go easy on resources we could also limit the application field. I think the macro code can look like this one here:

Please Login or Register  to view this content.

This is just a unfinished concepton as a suggestion, so would like to ask you to correct/to finish that code, please.

2. In Range (B19:G19) I would like to read out all the suppliers which are listed in each of the material groups. The reason is, that the amount and names of suppliers per materialgroup can change depending on which product I have chosen beforehand. (The materialgroups will stay the same. Maximum Suppliers per mat.group = 6).

3. Just a question: Make it sense to replace the sum-functions in range ("C24:G24") by a macro-code like i.e. this:

Please Login or Register  to view this content.

...it could be also built in an event-macro-code, like stated above.

I appreciate your knowledghe and thanks very much in advance.

respectfully

Bartek