Compare Columns?

[sezuh]

Hi,
I've been looking since 10:00 am and i did not find any solution anywhere,would you please, shed light to this problem.I have 3 columns with string of 5 letters(it could be numbers)in each cell,what i need to find a furmula to find duplicate letter(single,double ,triple...etc),if found to delete that row,but if there are no duplicate in any column copy that row to next columns.here is an example;
Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 74px"><COL style="WIDTH: 69px"><COL style="WIDTH: 71px"><COL style="WIDTH: 21px"><COL style="WIDTH: 76px"><COL style="WIDTH: 73px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 14pt">List</TD><TD> </TD><TD> </TD><TD> </TD><TD>RESULT</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>ABRSZ</TD><TD>CGUVZ</TD><TD>JLTXZ</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>ABRSZ</TD><TD>DGILR</TD><TD>JLTXZ</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>ABRSZ</TD><TD>DTVWY</TD><TD>FHLOU</TD><TD> </TD><TD>ABRSZ</TD><TD>DTVWY</TD><TD>FHLOU</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>BCLNW</TD><TD>BDEOX</TD><TD>BFGMT</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>ACOPT</TD><TD>DGILR</TD><TD>PRUWX</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>CIKMX</TD><TD>DTVWY</TD><TD>EGJNP</TD><TD> </TD><TD>CIKMX</TD><TD>DTVWY</TD><TD>EGJNP</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>ABRSZ</TD><TD>DGILR</TD><TD>INOYZ</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>ABRSZ</TD><TD>BHIPV</TD><TD>BJKUY</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>CIKMX</TD><TD>EGJNP</TD><TD>FHLOU</TD><TD> </TD><TD>CIKMX</TD><TD>EGJNP</TD><TD>FHLOU</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>ACOPT</TD><TD>AFIJW</TD><TD>HKNRT</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>AGHXY</TD><TD>LMPSY</TD><TD>PRUWX</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>DFKPZ</TD><TD>EISTU</TD><TD>JMORV</TD><TD> </TD><TD>DFKPZ</TD><TD>EISTU</TD><TD>JMORV</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>ABRSZ</TD><TD>ADMNU</TD><TD>BHIPV</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>CEFRY</TD><TD>DFKPZ</TD><TD>FHLOU</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>DGILR</TD><TD>EHMWZ</TD><TD>FNSVX</TD><TD> </TD><TD>DGILR</TD><TD>EHMWZ</TD><TD>FNSVX</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>ACOPT</TD><TD>BDEOX</TD><TD>INOYZ</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>ABRSZ</TD><TD>ADMNU</TD><TD>BFGMT</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>DTVWY</TD><TD>EGJNP</TD><TD>FHLOU</TD><TD> </TD><TD>DTVWY</TD><TD>EGJNP</TD><TD>FHLOU</TD></TR></TBODY></TABLE>

thank you
Kind Regards
sezuh

 

[p45cal]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	List				Formula result				Your RESULT
2	ABRSZ	CGUVZ	JLTXZ
3	ABRSZ	DGILR	JLTXZ
4	ABRSZ	DTVWY	FHLOU		ABRSZ	DTVWY	FHLOU		ABRSZ	DTVWY	FHLOU
5	BCLNW	BDEOX	BFGMT
6	ACOPT	DGILR	PRUWX
7	CIKMX	DTVWY	EGJNP		CIKMX	DTVWY	EGJNP		CIKMX	DTVWY	EGJNP
8	ABRSZ	DGILR	INOYZ
9	ABRSZ	BHIPV	BJKUY
10	CIKMX	EGJNP	FHLOU		CIKMX	EGJNP	FHLOU		CIKMX	EGJNP	FHLOU
11	ACOPT	AFIJW	HKNRT
12	AGHXY	LMPSY	PRUWX
13	DFKPZ	EISTU	JMORV		DFKPZ	EISTU	JMORV		DFKPZ	EISTU	JMORV
14	ABRSZ	ADMNU	BHIPV
15	CEFRY	DFKPZ	FHLOU
16	DGILR	EHMWZ	FNSVX		DGILR	EHMWZ	FNSVX		DGILR	EHMWZ	FNSVX
17	ACOPT	BDEOX	INOYZ
18	ABRSZ	ADMNU	BFGMT
19	DTVWY	EGJNP	FHLOU		DTVWY	EGJNP	FHLOU		DTVWY	EGJNP	FHLOU
Sheet2

Important to note the the formula is array-entered in E2 with with CTRL+SHIFT+ENTER. Then copy down and across.

This isn't deleting anything, merely replicating your results with a formula.
It's not elegant, just the first thing I tried which seemed to work.

 

[sezuh]

Hi P45cal,
thank you for your speedy response ,its a little bit slow but whocares.
but when i tried formula with numbers did not work. what do i have to do to make it work with numbers as well? Any suggestion?
I realy appreciate your help.
Sezuh

 

[p45cal]

Some examples of it not working with numbers?
(If there are 15 digits, then there are guaranteed to be duplicates if we're using decimals)

 

[sezuh]

Just lo let you know i'll not use numbers with decimal and each cell will not have more than 8 digit in it. i'll use that formula only when i know there
are not duplicate.
thanks

 

[p45cal]

I didn't mean decimal points, I meant base 10. But you're saying now there will only be one value per cell, so we only need to compare 3 numeric values per row looking for duplicates.
The letter version I wrote will find a duplicate letter, in any cell, or even the same cell. Is that what's required?

Some examples of numbers would clarify things a lot, eg. will you be mixing letters and numbers?

 

[sezuh]

I'm sorry, but in each cell going to be 8 or 3 numbers and i'll not mix letters with numbers,here is an example;
Sheet3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 119px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"><COL style="WIDTH: 13px"><COL style="WIDTH: 118px"><COL style="WIDTH: 119px"><COL style="WIDTH: 127px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>LIST</TD><TD> </TD><TD> </TD><TD> </TD><TD>RESULT</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>01 02 17 18 25</TD><TD>03 09 11 13 23</TD><TD>04 07 09 12 17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>01 02 17 18 25</TD><TD>03 09 11 13 23</TD><TD>04 19 21 22 24</TD><TD> </TD><TD>01 02 17 18 25</TD><TD>03 09 11 13 23</TD><TD>04 19 21 22 24</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>01 03 15 16 19</TD><TD>02 10 11 20 24</TD><TD>04 06 11 16 25</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>01 03 15 16 19</TD><TD>02 10 11 20 24</TD><TD>04 07 09 12 17</TD><TD> </TD><TD>01 03 15 16 19</TD><TD>02 10 11 20 24</TD><TD>04 07 09 12 17</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>01 03 15 16 19</TD><TD>02 10 11 20 24</TD><TD>04 19 21 22 24</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>01 06 09 10 22</TD><TD>02 04 05 15 23</TD><TD>03 07 20 21 25</TD><TD> </TD><TD>01 06 09 10 22</TD><TD>02 04 05 15 23</TD><TD>03 07 20 21 25</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>01 06 09 10 22</TD><TD>02 04 05 15 23</TD><TD>03 09 11 13 23</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>04 06 11 16 25</TD><TD>05 09 18 19 20</TD><TD>10 12 19 23 25</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>04 06 11 16 25</TD><TD>05 09 18 19 20</TD><TD>10 13 15 17 21</TD><TD> </TD><TD>04 06 11 16 25</TD><TD>05 09 18 19 20</TD><TD>10 13 15 17 21</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>04 06 11 16 25</TD><TD>05 09 18 19 20</TD><TD>12 13 16 18 24</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

thaks a lot.
Sezuh

 

[sezuh]

Hi,
If anyone would be able to provide formula for numbers version,would be
greatly appreciated.
Have a great Day..
sezuh

 

[p45cal]

This would be difficult to do with a formula using just built-in functions.
Having said that, hopefully someone here can prove me wrong.
So here's a formula using a UDF (User Defined Function):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	LIST				Formula result				Your RESULT
2	01 02 17 18 25	03 09 11 13 23	04 07 09 12 17
3	01 02 17 18 25	03 09 11 13 23	04 19 21 22 24		01 02 17 18 25	03 09 11 13 23	04 19 21 22 24		01 02 17 18 25	03 09 11 13 23	04 19 21 22 24
4	01 03 15 16 19	02 10 11 20 24	04 06 11 16 25
5	01 03 15 16 19	02 10 11 20 24	04 07 09 12 17		01 03 15 16 19	02 10 11 20 24	04 07 09 12 17		01 03 15 16 19	02 10 11 20 24	04 07 09 12 17
6	01 03 15 16 19	02 10 11 20 24	04 19 21 22 24
7	01 06 09 10 22	02 04 05 15 23	03 07 20 21 25		01 06 09 10 22	02 04 05 15 23	03 07 20 21 25		01 06 09 10 22	02 04 05 15 23	03 07 20 21 25
8	01 06 09 10 22	02 04 05 15 23	03 09 11 13 23
9	04 06 11 16 25	05 09 18 19 20	10 12 19 23 25
10	04 06 11 16 25	05 09 18 19 20	10 13 15 17 21		04 06 11 16 25	05 09 18 19 20	10 13 15 17 21		04 06 11 16 25	05 09 18 19 20	10 13 15 17 21
11	04 06 11 16 25	05 09 18 19 20	12 13 16 18 24
Sheet5

The formula in E2 should be copied down and across to fill the green range.

This should be backed up by the UDF itself:

Function NoDuplicates(TheRange)
myStr = ""
NoDuplicates = True
For Each cll In TheRange
    myStr = myStr & " " & cll.Value
Next cll
myStr = Application.Trim(myStr)
myArray = Split(myStr, " ")
For i = 0 To UBound(myArray) - 1
    For j = i + 1 To UBound(myArray)
        If myArray(i) = myArray(j) Then
            NoDuplicates = False
            Exit For
        End If
    Next j
    If Not NoDuplicates Then Exit For
Next i
End Function

This assumes spaces are used to separate the numbers within the cells.
It also counts as duplicate, duplicate numbers within a cell, as does the formula given earlier for letters (you didn't respond to my question in my last post regarding that).

 

[sezuh]

Hi P45cal,
thank you very very ..........very much for your outstandig help and effort
it work perfect and much quicker than the first formula,thats is what i want.As you said if also duplicate numbers within a cell that icing on the cake for me,thank you ever so much.I hope, I'll pay back for the help i've got from this forum.... ,you people deserve every credit.
Kind Regards
Sezuh