Create a separate list of duplicates
Assuming that the first column of your list is named AllData you can
try this formula in cell A4 in "another sheet":
=IF(ROW()-3COUNTIF(AllData,$A$1),"",OFFSET(INDEX(AllData,SM ALL(((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
On Thu, 24 Apr 2008 07:00:38 -0700, "Rob L"
wrote:
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)
|