transpose data and merge for each sheet based on multiple words in one column

[Ali-M]

hello
I have multiple sheets contains multiple words in column B
so what I want showing the whole report as in FINAL sheet . in column A create the Lis of sheets names and create headers B:H based on in column B for the sheets ,as to columns I: K should calculate as the formulas are existed, if there are duplicates items in the same sheet , then should merge and sum amounts for columns C or D , but should just merge for each sheet individual , not across sheets and should clear data in final sheet when run the macro every time because I will update data for the others sheets and I will add new sheets before FINAL sheet with same structure.
thanks

[JohnTopley]

Please Login or Register  to view this content.

NOTE: Many entries in Column B have trailing blanks so need to be removed (done in attached)

[Ali-M]

thanks
but the column C,E will show zero why?
I put your result above and my result bottom to you can compare.

[JohnTopley]

Did you read my reply .....???

NOTE: Many entries in Column B have trailing blanks so need to be removed (done in attached)

"PURCHASE " does not match "PURCHASE"

This is why!

[Ali-M]

Did you read my reply .....???

sorry !
I will check again and will come back to inform you.

[Ali-M]

perfect!
there is problem about formatting .the code delete the colors in headers and number format like this #,##0.00 ,how I can fix it,pleas?

[JohnTopley]

Please Login or Register  to view this content.

[Ali-M]

thank you very much