Need Formula

[CARBOB]

Need a formula in column E, that returns the digit from columns A:E, which represent the double digits(2 of the same digits). If no double digits(2) or triple digits(3) or quad digits(4), a blank.

Excel Workbook
	A	B	C	D	E
1	3	4	3	2	3
2	1	2	5	5	5
3	3	4	3	3
4	6	6	7	6
5	9	7	3	4
6	4	1	0	0	0
Sheet1

Excel 2007

 

[T. Valko]

Need a formula in column E, that returns the digit from columns A:E, which represent the double digits(2 of the same digits). If no double digits(2) or triple digits(3) or quad digits(4), a blank.

Excel Workbook
	A	B	C	D	E
1	3	4	3	2	3
2	1	2	5	5	5
3	3	4	3	3
4	6	6	7	6
5	9	7	3	4
6	4	1	0	0	0
Sheet1

Excel 2007

Try this...

Array entered** in E1 and copied down as needed:

=IFERROR(INDEX(A1:D1,MATCH(TRUE,COUNTIF(A1:D1,A1:D1)=2,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[T. Valko]

Try this...

Array entered** in E1 and copied down as needed:

=IFERROR(INDEX(A1:D1,MATCH(TRUE,COUNTIF(A1:D1,A1:D1)=2,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

P.S.

If there are 2 entries with dupes, Like this:

1212

The formula will find the first dupe, in this case, 1.

 

[CARBOB]

Works great, many thanks!!!

 

[T. Valko]

Works great, many thanks!!!

You're welcome. Thanks for the feedback!

 

[CARBOB]

P.S.

If there are 2 entries with dupes, Like this:

1212

The formula will find the first dupe, in this case, 1.

Glad you brought to my attention. Is there a way to do those also?

 

[Matty]

Is there a way to do those also?

Given Biff's example, what would you want shown? 1 or 2? Or both?

Matty

 

[CARBOB]

Given Biff's example, what would you want shown? 1 or 2? Or both?

Matty

Both, one in column E, the other in column F

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A1:$D1,MATCH(<font color="Green">TRUE,COUNTIF(<font color="Purple">$A1:$D1,$A1:$D1</font>)=2,0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">COUNTIF(<font color="Green">$A1:$D1,$A1:$D1</font>)</font>)=8,INDEX(<font color="Red">$A1:$D1,MIN(<font color="Green">IF(<font color="Purple">$A1:$D1<>E1,COLUMN(<font color="Teal">$A1:$D1</font>)-COLUMN(<font color="Teal">$A1</font>)+1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[CARBOB]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A1:$D1,MATCH(<font color="Green">TRUE,COUNTIF(<font color="Purple">$A1:$D1,$A1:$D1</font>)=2,0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">COUNTIF(<font color="Green">$A1:$D1,$A1:$D1</font>)</font>)=8,INDEX(<font color="Red">$A1:$D1,MIN(<font color="Green">IF(<font color="Purple">$A1:$D1<>E1,COLUMN(<font color="Teal">$A1:$D1</font>)-COLUMN(<font color="Teal">$A1</font>)+1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

Works like a charm, many thanks to all of you.