Results 1 to 15 of 27
Thread: Match Text (Office XP)

20050727, 14:30 #1
 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

20050731, 15:14 #2
 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

20050731, 16:38 #3
 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

20050801, 07:46 #4
 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

20050801, 08:05 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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,"")

20050801, 13:45 #6
 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

20050801, 13:50 #7
 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

20050802, 00:24 #8
 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

20050802, 02:17 #9
 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

20050802, 07:13 #10
 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

20050802, 07:24 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20050803, 02:21 #12
 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

20050803, 05:28 #13
 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

20050803, 06:27 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20050803, 13:51 #15
 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