creating table in new sheet based on two other sheets

[young_86]

hello,
i've got same problem with writting a macro which will create a table in one sheet based on two other sheets. Like you can see in att i've got one table in sheet1 and second one in sheet2. I need to create new table in sheet three based on those two tables. But it's no so easy for me, becouse values should apear in specific column depend from which table they were taken.
e.g.
if name1 apear only on sheet1, on sheet3 there should be name1 and values from sheet1 in column value1, and 0 in column value2

if name apear only on sheet2, on sheet3 there should be name and values from sheet2 in column value2, and 0 in column value1

and

if name apear on both (sheet1 and sheet2) there should be name on sheet3, values from sheet1 in column value1 and values from sheet2 in column value2.

I hope Everything will be more clear if you check att excel file and picture.
I know what should we write in the code but i have no idea how to write it I hope that someone will help me. If something is unclear in this post, just write i'll try to explain in
ps.
i think that i should add that sometimes my tables got 500 and sometimes 900 rows, and values are different, e.g. -100, 0, +100. I don't know is it important?
I'll be really greateful if someone can help me.
Thank You in advance

[oeldere]

If you are able to change your data in the format of sheet 4 you can use pivottable.

[young_86]

Oeldere thanks for reply, but I can't use pivot tables. This is will be just part of my code, so i need different solution than pivot tables

[young_86]

maybe i should add that if some temporary sheet is necessary of course you can create one. I was trying to do this with formula, but it doesn't work

[young_86]

help stil needed

[young_86]

up.
anyone can help, or msybe give some tips how i can do thid?
thank you in advance

[young_86]

up.again

[oeldere]

With an pivot table.

add new data

and run the macro Sub AddAllDataOnTheFirstSheet()

The pivot table will automatic refresh.

[young_86]

thanks for reply. Your solution help me a bit

[oeldere]

Please Login or Register  to view this content.

It helps a bit ?

What need to be changed?

I see now you marked your question as solved.

[young_86]

well i cant use pivot tables.like i wrote before this is just part of macro which i `m trying to write. im trying to solve my problem from different side..but if you got any solution i well he very pleased to hear it i`m really greateful for yout solution,becouse this post is on the forum for quite long time and not many people give any solution.so once again,Thanks