Create a separate list of duplicates

[Lars-Åke Aspelin]

Assuming that the first column of your list is named AllData you can
try this formula in cell A4 in "another sheet":

=IF(ROW()-3>COUNTIF(AllData,$A$1),"",OFFSET(INDEX(AllData,SMALL(((AllData)=$A$1)*ROW(AllData),ROW()-3+ROWS(AllData)-COUNTIF(AllData,$A$1))),0,COLUMN()-1))

Copy this formula to the cells B4 and C4.
Then copy the formula down as far as needed.

The cells A3, B3, and C3 you edit manually to get the column headings

Hope this helps. / Lars-Åke

 

[Rob L]

I have a list (of names of employees inducted) called AllData. On another
sheet, I want to be able to enter a name in (say) A1, and have the rows
below list all the entries from AllData that have that name. This is (for
instance) to find all the "Smiths" and "Jones". Vlookup only finds the first
instance, so if we have inducted 3 people with the surname "Smolenskovnich",
then my new list would have all 3, and their induction details.

Example

A1 - Enter
"Smolenskovnich"

A3:C6 would then list....

LastName FirstName DateInducted
Smolenskovnich Ivan 1/1/08
Smolenskovnich Ivana 23/2/08
Smolenskovnich Evan 2/2/08

Thanks learned gurus.

OM
(Old Man learning Excel)