Formula to List Rows for Duplicate Cells in a Column

[pbornemeier]

For a given column, this helper-column formula will indicate if the text in an entire cell has not appeared previously with "First". If cell text has appeared before, display the row where it first appeared:

=IF(ROW(F3)<>MATCH(F3,F:F,0),MATCH(F3,F:F,0),"First")
 
Example:
Row Data Helper
1   A    First
2   B    First
3   C    First
4   A    1
5   C    3

Is there a formula that would show each row where duplicate text appears in the helper column?

Example:
Row Data Helper 
1   A    1,4
2   B    2
3   C    3,5
4   A    1,4
5   C    3,5

The real-life data runs about 1500 rows with no more than 20 duplicates.

Organization involved wants to avoid the various VBA solutions.

 

[Dazzawm]

Can you not use a pivot table?

 

[pbornemeier]

Thanks for the suggestion. The only Pivot table operation I could figure creates a large, sparse matrix. I have been trying to get a CSE formula to do what I wanted, but have not yet been able to do so.

I will keep working on it and update this thread if I find something that works.

 

[Marcelo Branco]

Hi,

Maybe this

Copy this UDF to standard module

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

F G
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>1, 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>3, 5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>1, 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>3, 5</TD></TR></TBODY></TABLE>

Array-formula in G1
=SUBSTITUTE(aconcat(IF($F$1:$F$5=F1,", "&ROW($F$1:$F$5)-ROW($F$1)+1,"")),", ","",1)

confirmed with Ctrl+Shift+Enter

copy down

HTH

M.

 

[GaryWMn]

We had a similar discussion on another thread.

See if this helps....
http://www.mrexcel.com/forum/showthread.php?t=556132&p=2746780

Gary

 

[pbornemeier]

Thanks for the inputs. Still working on a non-VBA solution.

If I put 1,2,3... across starting in D1 and use this formula in D2 and copy it across and down, it gives me a matrix that contains the values I need, but not in the form I need.

=INDEX(--($A$2:$A$6=$A2)*ROW($A$2:$A$6),D$1)

and examine the result in the fx popup, I can see the argument for the index range is
{2;0;0;5;0}.

Is there a way to convert this array to text? I can then use SUBSTITUTE to remove the ";0" items.

 

[Marcelo Branco]

Thanks for the inputs. Still working on a non-VBA solution.

Is there a way to convert this array to text? I can then use SUBSTITUTE to remove the ";0" items.

Hi Phil,

The problem, i think, is that Excel has not a Concatenate range/array function (as far as i know...).

That was the reasoin i suggested the aconcat UDF and an array-formula - a little afraid that this would yield a poor performance with 1,500 rows.

If you get a different solution, let me know.

Regards,

M.

 

[Marcelo Branco]

Phil,

If you got an array with a regular formula the performance issue would be resolved.

Tell me what you have in A2:A6 and D1, so i can try to understand your formula.

M.

 

[pbornemeier]

D2 is copied down and over.

Sheet1

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:74px;"><col style="width:19px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="background-color:#c0c0c0; text-align:center; ">Data</td><td style="background-color:#c0c0c0; ">Helper</td><td style="background-color:#c0c0c0; ">
</td><td style="background-color:#c0c0c0; text-align:center; ">1</td><td style="background-color:#c0c0c0; text-align:center; ">2</td><td style="background-color:#c0c0c0; text-align:center; ">3</td><td style="background-color:#c0c0c0; text-align:center; ">4</td><td style="background-color:#c0c0c0; text-align:center; ">5</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; ">A</td><td>
</td><td>
</td><td style="font-family:Arial Unicode MS; text-align:center; ">2</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">5</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">B</td><td>
</td><td>
</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">3</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">C</td><td>
</td><td>
</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">4</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">6</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">A</td><td>
</td><td>
</td><td style="font-family:Arial Unicode MS; text-align:center; ">2</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">5</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">C</td><td>
</td><td>
</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">4</td><td style="font-family:Arial Unicode MS; text-align:center; ">0</td><td style="font-family:Arial Unicode MS; text-align:center; ">6</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>D2</td><td>=INDEX(--($A$2:$A$6=$A2)*ROW($A$2:$A$6),D$1)</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4

 

[pbornemeier]

This works for the 5 rows shown, but it would be useless for a 1500 row list:

=SUBSTITUTE(INDEX((--($A$2:$A$6=$A2)*ROW($A$2:$A$6)),1)&","&INDEX((--($A$2:$A$6=$A2)*ROW($A$2:$A$6)),2)&","&INDEX((--($A$2:$A$6=$A2)*ROW($A$2:$A$6)),3)&","&INDEX((--($A$2:$A$6=$A2)*ROW($A$2:$A$6)),4)&","&INDEX((--($A$2:$A$6=$A2)*ROW($A$2:$A$6)),5)&",","0,","")