Find duplicate value in Column B to show on list with lookup

[Kenny Yip]

Dear fellow members


Trying to figure out what formulas to apply to achieve the outcome of the table showing names more than 2 counts with the required details shown.

I have attached a sample working file, with example text in red.


Thanks

Kenny Yip

[alansidman]

Kenny, you will need to provide some sort of relationship between your tables. Excel needs a flow to make things happen. Suggest you explain in simple English terms what you are attempting to do instead of trying to make us guess.

[Kenny Yip]

@alansidman

What i am trying to do is when there is a person with more than one entry, then his name together with the dates of offence will be show on another table (Column N to Column T).
Maximum allowed offences is three times.

Therefore, in a short quick glance, we will be able to know which person has more than one offences together with the dates and type of action.

[JohnTopley]

in L4

=IF(AND(COUNTIF(C:C,C5)>1,COUNTIF($C$4:C5,C5)=1),COUNTIF(C:C,C5),"")

copy down

in N4

=IFERROR(INDEX($B$4:$B$1000,AGGREGATE(15,6,ROW($B$1:$B$1000)/($L$4:$L$1000<>""),ROWS($1:1))),"")

in O4

=IFERROR(INDEX($E$4:$E$1000,AGGREGATE(15,6,ROW($B$1:$B$1000)/($B$4:$B$1000=$N4),1)),"")

in P4

=IFERROR(INDEX($F$4:$F$1000,AGGREGATE(15,6,ROW($B$1:$B$1000)/($B$4:$B$1000=$N4),1)),"")

copy to other columns and change 1 to 2 annd 3