Extract unique items for two columns and get totals

[YasserKhalil]

Hello everyone
I have three sheets (two would be the data input : Data1 and Data2)
I have in these two sheets : column C for items and column D for Type and in column E I have the amount for each item & type

In Result sheet I need to extract unqiue items (Item & Type) from both sheets and get the total amount for them
After that substract the two amount extracted in column G in sheets("Result")

For example :
The item "Eggs" & the Type "Kind2" in sheets("Data1") exist in row 7 and row 19 .. the amount = 30 + 4 =34
The item "Eggs" & the type "Kind2" in sheets("Data2") exist in row 14 and row 18 .. the amount = 2 + 4 = 6

So in result sheet I would like to have the Item name and its Type and the total amount in both sheets (each amount in each sheet) and calculate the difference between Data1 - Data2 related that item
The expected result for that example will be:
Columns: C D E F G
Eggs Kind2 34 6 28

Hope it is clear
Thanks advanced for help

[Crooza]

I'd suggest pivot table.Copy sheet two to the bottom of sheet 1 data, move far right column of the sheet 2 columns to the right one column and label this column data2. This way you'll have all data in one sheet with 4 data columns - item, kind, data1amount, data2amount then run a pivot table to find the sums of data1amount and data2 amount for each item and kind, then a calculated field to find the difference.

[YasserKhalil]

Is it possible to do it with codes .. I don't prefer pivot tables (although I know it is a strong tool) this is a point and another point I need to do some other tasks related to these two sheets.. these columns are for the sample file but there are many other columns related to both sheets

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

Really really awesome & wonderful Mr. Karedog
How can you type these magic lines?!!
Just a little point : What about column G ? I need to get the remain (the difference between the two amounts) .. Just refer me to the line I should add
Thank you very much for this great and perfect help

[karedog]

Ah, I only read first half of OP, and missed the rest. Here it is :

Please Login or Register  to view this content.

[YasserKhalil]

Mr. Karedog
YOU ARE GENIUS. You have solved a lot of my issued in perfect way. You gave me unique and perfect solutions
Thanks is not enough for all this great help ..but I have to say it (although it is not enough as the words are unable to thank you)
Thank you very much
Best Regards

[karedog]

You are welcome, and many many thanks for the kind words.


Regards