code consolidate data from multiple sheets to one sheet

[abdelfattah]

hi, guys

i'd like code works to consodilate data from multible sheets to one sheet
i have sheets1,2,3,4,5 the data columns a,b,c,d in all sheets what i would be showing data in sheet5 but the column d contains brand is formula like this sheets5=(sheet1+sheet2+sheet4)-sheet3 and when i fill repeated brands (the same brand twice or more) in sheet1,2,3,4 here it must show brand in column d in sheet5 one time not more or repeated
like this :

SHEET1 SHEET2 SHEET3 SHEET4 SHEET5
A B C D A B C D A B C D A B C D A B C D
ITEM BRAND TYPE BALANCE ITEM BRAND TYPE BALANCE ITEM BRAND TYPE BALANCE ITEM BRAND TYPE BALANCE ITEM BRAND TYPE BALANCE


AA 1200R20 G 250 AA 1200R20 G 100 AA 1200R20 G 50 AA 1200R20 G 20 AA 1200R20 G 395

AA 1200R20 G 25 AA 1200R20 G 50 AA 1200R20 G 50 AA 1200R20 G 20
NOTE : THE VALUE IN SHEET5 395 IT DOES THE FORMULA ROW1,2 BECAUSE THE SAME BRAND