+ Reply to Thread
Results 1 to 23 of 23

Merge two worksheet into 3rd sheet (modifying a macro)

  1. #1
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472

    Merge two worksheet into 3rd sheet (modifying a macro)

    Hello,

    i have three worksheets: "List 1", "List 2" and "New ist".I want to merge the first two sheets into third.

    List 1:
    Please Login or Register  to view this content.
    List 2:
    Please Login or Register  to view this content.
    The first four columns A,B,C and D of "New List" are all the same as in "List 1". Now the column E of " New List" contains members of column D from "List 2" but without duplicates.
    Now the members of column D "List 1" should be seached in Column A "List 2" and if they match (member Column D "List 1" = member Column A "List 2") then the corresponding member of column D "List 2" should be put in column E of "New List".

    Jindon had once kindly sent here a code which works very well but it allows duplicates in column E which now i want to be changed. No duplicates should be allowed in Column E "New List".

    New List:
    Please Login or Register  to view this content.
    Here is the code which is supposed to be changed, so that column E has no duplicates:

    Please Login or Register  to view this content.
    Thanks for each help in advance!

    Here is excel file containing the macro:
    Attached Files Attached Files
    Last edited by wali; 06-02-2008 at 08:36 AM.

  2. #2
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    can any one please help!!

  3. #3
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    Hello Jindon,
    i have an older code of yours here. I really need it to be changed. I will be very thankful, if you could find time to modify it.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    Can you just attach xls file (w/o zipped) with before/after and clear explanations?

  5. #5
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    heelllooo Jindon,
    thanks god that you are here:-)
    here is the excel file. Please rename the txt to xls
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    I can't see .txt file.

  7. #7
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    ok here is the link for excel file:

    excel file

  8. #8
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    Explinations:

    the macro is supposed to read the members of Column D in "List 1" and compare them with Column A in "List 2". If they match then the corresponding members of D column from "List 2" shoud be copied and pasted into Column E of "new List". There should be no duplicates in column E.

    Your older code which i have posted in this thread works very good. But it allows duplicates in column E which is supposed to be changes. There shoud be no duplicates in E.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    Not clear to me.

    What do you mean by red and green ?
    How do you want it ?

  10. #10
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    no, i dont need the colors. I had colred the members only for explination. I dont need them.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    Can you just attach the file before and after ?
    I don't understand what you mean by "duplicate".

  12. #12
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    sorry for being unclear. I will try again:

    There are three worksheets "List 1", "List 2" and "New List". The "New List" is the copy of "List 1" but with one more column. The column E.

    The column E in "New List" contains the members of column D from "List 2". But with a criteria:
    "List 1" Member of column D = "List 2" column A ==> copy and paste members of column D from "List 2" into column E of "New List".

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    wali

    Confusing...

    What do you want as a result out of your 2 worksheets (List 1 & 2) ?
    I need a pure result, not by colored data which I don't understand the meaning.

    Anyway, I must go now for the day.

  14. #14
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    Hello Jindon,

    yes i agree its really confusing and tricky. And my way of explaning it, makes it may be more confusing then it really is.

    I give it another try without colors! :-)

    Here is the pure Excel file with "List 1", "List 2" and their resulting worksheet "New List".

    Excel fie



    And here are the screenshots of the worksheets:
    Screenshots


    Short explination:


    IF a member of L1 Col.D = Col.A L2
    then copy and paste Col.D L2 into NL Col.E

    ==>

    NL= L1 + col. E
    where
    E= col. D of L2 (no duplicates)


    Here is your older code, which works very good. Only one thing must be changed: No duplicates should be allowed in Column E

    Please Login or Register  to view this content.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    try
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    Hello Jindon,

    thank you very much for the code. Unfortunately it shows error at the very beginning. As i do the debug on the very first line of sub it shows error: "Sub or Funktion not defined"

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    What do you mean ?
    Do you still have old code in that moduel ?
    If so, delete the old code first.

  18. #18
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    no i dont have the old code there. I also controlled the sheet names and data containing columns. Everything seems to be ok. But it still shows error " sub or funktion" not defined.

  19. #19
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    Do you have the possiblility to run the macro in your computer ?

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    Quote Originally Posted by wali
    Do you have the possiblility to run the macro in your computer ?
    No
    try
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    I changed it. but it doesnt run
    The odd thing is that by debuging it shows error by first click. Its very strage that it doesnt even go one stept forward and shows " Sub or funktion not defined".

  22. #22
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    ok
    its running
    there was another dic without 1
    I changed
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Thank you very much for your patience. It was really a biiig help. This macro will save me the work of 10 years. Thank you very much.

    P.S I love your makros cus they are fast like hell! :-)

    Now i will put this macro into the real wordlist and give u the report.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,108
    OK that's good.
    Last edited by jindon; 06-05-2008 at 03:02 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1