merge data based on column

[leap out]

hi
I would merge data by macro based on COL B with summing the values. in col I if contains the same values not shouldn't sum based on duplicated item in COL B and if the values are different for duplicate items in COL B then should calculate and using function average . so I put expected result in sheet OUTPUT
note: the duplicated items are existed in the same sheet and the others and I creat a new sheet every time so it should be dynamic

[jindon]

Please Login or Register  to view this content.

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[leap out]

@Jindon this is magnificent !
but I said

I creat a new sheet every time so it should be dynamic

it doesn't seem clear . what I want when creat new sheets (report3,4...and so on ) before sheet output should be dynamic when summing data all of data before sheet ouput .
about this

if the values are different for duplicate items in COL B then should calculate and using function average

if I have BAT-OIL-100 contain differnt values like 400,420 should calculate average
thanks again

[leap out]

@Bo_Ry thanks but I have three thing to fix it
first should show number formatting
second should calculate average if I have two different values for duplicate item
third this is my mistake and I really sorry in COL D should also merege with comma as in COL C
I put in COL D and highlight how should be like COL C

[leap out]

@jindon please see the file in post # 5 about COL D . forgive me I forgot to mention that . it should like COL C.

[jindon]

If the number always have 4 digits then simply

Please Login or Register  to view this content.

[leap out]

If the number always have 4 digits then simply

sorry I don't understand . what you mean?
I would change this

to this

to become more dynamically . not every time when add new sheet I have to write sheet name in array line
about the calculate average I highlighted the different values by yellow and highlighted by red for duplicate item and highlight by blue should merge data in COL D as in col C with comma
I hope this help you

[jindon]

Please Login or Register  to view this content.

[leap out]

@jindon
every thing is ok except one thing in COL D should be like COL C CUS-OL-1,2

[jindon]

change to

Please Login or Register  to view this content.

[leap out]

@jindon great work ! thanks for your time and solution

[Bo_Ry]

Try

Please Login or Register  to view this content.

[leap out]

@Bo_Ry thanks again for updating your code