If a name shows up in a range more than twice I need to display the name

[britts614]

I have a lists of names in the range A8:F35. Each column A through F has a different set of names, but they may appear more than once.

How do I display the names of those in the range more than 2 times in a different column? Ex. below:

A.......B........C...................... D(Appears More than 2x?)
John....Bob.....Bob...................... John
Tim.....John....Joe....................... Bob
John....John....Fred
Bob.....Tim.....John

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H
7	Name	Name	Name	Name	Name	Name		More than 2
8	John	Bob	Bob					John
9	Tim	John	Joe					Bob
10	John	John	Fred
11	Bob	Tim	John
12
13
Sheet1

Cell Formulas
Range	Formula
H8	{=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...e-i-need-to-display-the-name.html#post5152056

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[kweaver]

This was quite a head-scratch (for me, at least). I entered your array formula in H8 and it pulled up John from the original data.
But, filling it down the H column didn't result in anything because it seems to be dealing with only the first 3 columns. What about all 6 (through and including F)?

Also, what's the significance of "R00C00" ??

 

[Eric W]

=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")

makes sure the cells we look at aren't empty -


=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")

Makes sure we haven't already found the name -


=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")

Looks for names used more than twice -


=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")

If we find one, we save the row times 100 + the column, so B8 becomes 802 -


=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")

Gets the smallest of any matching cells -


=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")

Formats the 802 value as R08C02 -


=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")

Then the INDIRECT looks up the address we just found, R08C02. The 0 parameter at the end says to use the R1C1 style of addressing, instead of B8. Then the IFERROR kicks in if there are no matches.

I can't explain why you didn't get any more matches than the first, unless you didn't use Control+Shift+Enter, or have different data/ranges.

 

[kweaver]

Here's my table:

Excel 2010
	A	B	C	D	E	F	G	H
8	John	Bob	Susan	Sam	Fred	Mary		John
9	Bob	Mary	Kevin	Justin	John	Jack		Susan
10	Susan	George	John	Scott	Susan	Janet
Sheet2

Cell Formulas
Range	Formula
H8	{=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[britts614]

Hello,

Yes, I did that on accident - Sorry it won't happen again!

 

[britts614]

Here's my table:

Excel 2010

	A	B	C	D	E	F	G	H
John	Bob	Susan	Sam	Fred	Mary	John
Bob	Mary	Kevin	Justin	John	Jack	Susan
Susan	George	John	Scott	Susan	Janet

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H8[/TH]
[TD="align: left"]{=IFERROR(INDIRECT(TEXT(SMALL(IF($A$8:$F$35<>"",IF(COUNTIF($H$7:$H7,$A$8:$F$35)=0,IF(COUNTIF($A$8:$F$35,$A$8:$F$35)>2,ROW($A$8:$F$35)*100+COLUMN($A$8:$F$35)))),1),"R00C00"),0),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much for all of your help! I was able to get what I needed