Extracting duplicates

[GLPedley]

Hello

The attached file contains numerous duplicate records in column A, I want to extract the first 5 records ( or as many records are available if less than 5) of each different value in column A. The list is pre sorted by Column D and then Column A to give the current order. Columns G to J shows how i would want the result to look.

At the moment i am manually deleting rows to achieve the result in Columns G to J which is ridiculously time consuming and makes me go bog eyed doing it.

Many Thanks

 

[GraH - Guido]

Ideal situation for Power Query (in 2016 Get&Transform), as it is really simple to accomplish.

1. Load as table
2. Remove duplicates
3. group by name, "All Rows" (column named AsTable)
   
4. Add custom column with formula Table.FirstN ([AsTable],5)
5. Extract the records from this table
6. delete redundant columns
7. Load as table

When new records are added, simply click refresh.

Even better, you don't need to sort up front, you can do that in Power query in the first steps.

 

[GraH - Guido]

Including the sort steps. You would need version 2010 or higher on Windows OS to use PQ, fyi.

 

[GLPedley]

G.

This works a treat, many thanks for your solution, you've saved me hours of time and a potential trip to the opticians

Thanks

 

[rahulshewale1]

Hii @GLPedley,

Here formula solution

but as already told by @GraH - Guido

power query is powerful function in excel and fast.


Helper column A2 copy down

=IF(COUNTIFS($B$2:B2,B2)<=5,1,FALSE)&B2


Cell G2 copy across

=IFERROR(INDEX(B$2:B$15000,AGGREGATE(15,6,(ROW($B$2:$B$15000)-ROW($B$2)+1)/(SEARCH("1",$A$2:$A$15000)),ROWS($P$22))),"")



Regard
Rahul shewale

 

[GraH - Guido]

You're welcome, glad you liked it, GLPedley (so why not press like ). Humour me and try to replicate it by simply using the UI, except for the Table.FirstN step, though I can think if a way to have it all via the UI. Perhaps I'll post it later.
See it as an invitation to explore that tool by yourself.

 

[GraH - Guido]

2 more alternatives
1. using only the UI, but I had to create a function using a parameter for "Golfer": so in the function query you need to change the hard coded selection by the parameter name...
2. loading the TOPx results as a list instead of rows (it uses the first solution, just to show the result table may be in a different shape)

Changing the functionQuery (e.g. you want top 10), can be done by using the gear icon of the last step and change the 5 to 10.


Not sure I created the function in the most optimal way though.
My first offered solution seems the easiest one after all.

 

[bosco_yip]

Another formula option with helper

1] In "Helper" E1, enter: 0

2] In "Helper" E2, copied down :

=IF(COUNTIFS($A$2:A2,A2)<6,LOOKUP(9^9,E$1:E1)+1,"")

3] In G1, enter :

=LOOKUP(9^9,E:E)
and >> Custom Format: "Golfer"

4] In G2, copied across to J2 and all copied down :

=IF(ROWS($1:1)<=$G$1,LOOKUP(ROWS($1:1),$E$1:$E$10000,A$1:A$10000),"")

Regards
Bosco