enhancing Column A through Column B

Hello,

i have a list containing six columns A,B,C,D,E,F in Sheet"original". It seems like follows ( the columns C,D,E,F are not relevant for macro therefore i am puting only Column C as example for the Columns D,E,F):

Please Login or Register  to view this content.

Now i need a macro which enhances the column A through column B. The macro should put those members of Column B in Column A which donot already exist in column A. These new entries should be done in another sheet "new". For example the first entry in Column A is "A" and the corresponding members to that are "B,C,D,E" in column B. Now as far the corresponding members "B,C,D,E" dont exist as members in column A they should be added one after another to the new sheet to column A. This new entry in Column A in sheet "new" should have the original member from column A the "A" and "C,D,E" (not "B" itself") members in column B in sheet "new". All the corresponding members of other columns to the original column A should also be pasted to the new sheet in corresponding columns. No Duplicates in Column A in Sheet "new" should be allowed. After running the macro it should generate the following new list in sheet "new".

Please Login or Register  to view this content.

I am uploading an image and an excel file. The colors in the image means nothing but only an effort to make the problem understandable.

image

Thank you very much for each answer.

Take "A" in your example,

How do you determine which "A" ?

Please Login or Register  to view this content.

Hi Jindon,
thank you for your response.

Well the macro is supposed to read the content of column B and search for each member of column B in column A. If the member does not exist in column A, then it should be added to column A in a new sheet. According to my example "B" in column B would be the first member the macro would look for in column A. And as far as it does not exist, it should be added to column A in new sheet. And now as second step it should also read the corresponding member of "B" in original column A which is "A" and put it in new column B along all original members of original collumn B except "B" itself. And that proccedure should be done with "C,D,E" from original column B as well.

Now after first cell is finished the macro would read the second cell of column B which is "A,F,G". The first member to be read in second cell of column B would be A and it would be searched if it already exists in original column A already. Now as far it already exists, it will not be added as new entry in new sheet. Now the macro will jump to the second member "F" and so on...

That would mean that it is allowed that A can be member of many cells in new column B. Important is only that in new column A it should not appear cus it already exists in original column A. The same way in new column A no duplicates should be allowed.

I hope that i could express what i mean. If not please let me know.

Hi Jindon,
thank you very much for your code. But anfortunately i cant run it. I will be very thankful if you could put the macro in the excelfile i have uploaded and upload it again. It shows me error in line "next". There are two sheets in the excel file. The "original" sheet contains the list which is to be processed. And the results of the process are supposed to be put in other sheet"new".

This should work

Please Login or Register  to view this content.

Dear Jindon,

It works very good. Thank you very much.

I dont want to be rude! but is it possible to attach the remaining columns D,E and the column F to the results in sheet "new" as well the way you have attached the column C to it?

thank you very much.

try

Please Login or Register  to view this content.