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) |
Create a separate list of duplicates
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) |
All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com