Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Match Text (Office XP)

    Hi,
    I would like to compare the text in column A of wb1 to column A of wb 2, thereafter,
    save or highlight the duplicates of the entire row in a new sheet.


    thanks always, kun

  2. #2
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Hi Legare,

    Thank you for the tip. It works wonderful. btw, Can we also write VBA on this? If I have both workbooks consist of 4 sheets each.

    thanks, kun

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    What do you want the VBA code to do with the four sheets?
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Legare,

    I noticed that the formula can only matched in a row by row basis, and if eg, abcd is in row 4 of the first column in wb1 and it is also in row 6 of the first column in wb2, it will not show any duplication. Is there a way which can show that there are duplicates if same data are in different rows.

    thanks, kun

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match Text (Office XP)

    You could use a formula like this (with the appropriate names substituted):

    =IF((COUNTIF(Sheet1!A1,[wb2.xls]Sheet1!$A:$A)+COUNTIF(Sheet1!A1,[wb2.xls]Sheet2!$A:$A)+COUNTIF(Sheet1!A1,[wb2.xls]Sheet3!$A:$A)+COUNTIF(Sheet1!A1,[wb2.xls]Sheet4!$A:$A))>0,Sheet1!A1,"")

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Go to an empty sheet in wb1 and enter the formula below in cell A1. Copy it down as far as needed. You will get a list of the entries in Sheet1 column A that are duplicated in Sheet1 of workbook wb2.

    <code>
    =IF(COUNTIF([wb2.xls]Sheet1!$A:$A,Sheet1!A4)>0,Sheet1!A4,"")
    </code>
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Sorry <img src=/S/blush.gif border=0 alt=blush width=15 height=15> , my original formula had an error in it. I have corrected it in my original post, and as corrected it should work no matter what row the diplicate appears in. I also see that Hans has copied my original formula with the error and modified it to work on four sheets, but unfortunately did not fix the error. Here is the correct formula:

    <code>
    =IF((COUNTIF([wb2.xls]Sheet1!$A:$A,Sheet1!A1)+COUNTIF([wb2.xls]Sheet2!$A:$A,Sheet1!A1)+COUNTIF([wb2.xls]Sheet3!$A:$A,Sheet1!A1)+COUNTIF([wb2.xls]Sheet4!$A:$A,,Sheet1!A1))>0,Sheet1!A1,"")
    </code>
    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    The revised formula shown as text and can't be used. pls advise

    thanks, kun

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Are you putting it in a cell that is formatted as Text? The cell must be formatted as "General" BEFORE the formula is entered into the cell.
    Legare Coleman

  10. #10
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Legare

    It is General format before paste into it. I have retry pasting but the result is the same with the whole formula showing outright.

    thanks, kun

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match Text (Office XP)

    Make sure that the format is still General after pasting the formula. Then press F2 to edit the formula, and press Enter without changing anything.

    BTW You haven't checked the Formulas box in the View tab of Tools | Options..., have you?

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    You didn't say that you were getting an error message, so I was looking at the wrong thing. I unfortunately introduced a typo error into the formula when I corrected Hans' formula. Try the formula below (I have actually tested this one):

    <code>
    =IF((COUNTIF([wb2.xls]Sheet1!$A:$A,Sheet1!A1)+COUNTIF([wb2.xls]Sheet2!$A:$A,Sheet1!A1)+COUNTIF([wb2.xls]Sheet3!$A:$A,Sheet1!A1)+COUNTIF([wb2.xls]Sheet4!$A:$A,Sheet1!A1))>0,Sheet1!A1,"")
    </code>
    Legare Coleman

  13. #13
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Screenshot cropped by HansV because it was too wide. Please don't post images over 640x480.

    Hans and Legare

    I have check all that you have mentioned, but the formula still shown as text. Don't understand why. I have even copied all data ( about 18000 ) to a new workbook but the result is the the same.

    thanks, kun

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match Text (Office XP)

    We can only work with the information you provide. If that information is incomplete or incorrect, you won't get the results you want.

    In <post#=504707>post 504707</post#>, you stated that the workbook you wanted to check had 4 worksheets. Hence, the formula proposed by Legare assumes that wb2.xls has 4 worksheets Sheet1 through Sheet4. However, the workbook you attached has only 3 worksheets, so the formula fails.

    You must either add a 4th worksheet Sheet4 to wb2.xls, or remove the part referring to Sheet4 from the formula.

  15. #15
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Text (Office XP)

    Hans,

    I thought that I can test for 1 sheet ! I have added a fourth sheet to wb2, but it shown #VALUE.
    Attached is a dummy sample for wb1 and I have change the wb2 with 4 sheet at my earlier post ( notice that I can't attach 2 wb together). Appreciate you or Legare can let me know where did I did wrong.

    thanks, kun

Page 1 of 2 12 LastLast

Posting Permissions

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