I would use a helper column and concatenate the fields that I wanted to base the
duplicates on:

=a2&"|"&e2&"|"&f2&"|"&g2
the vertical bar is just a character (unused in any of the fields) that serves
as a separator--so that joining two fields won't match an existing field.

Then drag down the column.

Then I'd use another helper column that counted each of these:

=countif(x:x,x1)
(with column X holding the concatenated string)

And then apply Data|Filter|autofilter to show the values I want (the 1's). And
copy those visible cells to the new home

or show the greater than 1's and delete those???



Shon wrote:

Is there a facility in Excel 2007 to KEEP duplicate records? I can find ways
of removing them through remove duplicates and advanced filtering but I want
to be able to retain duplicate records and remove all others based on
duplicate values in some of the columns e.g. if I have the following data

Customer Number Invoice Number City Reponsible Branch Amount
21500 1234 London LO14 £150
21500 1235 London LO12 £99
21500 1236 London LO13 £45
21500 1237 London LO14 £150
21600 1238 Glasgow GL56 £80
21600 1239 Glasgow GL57 £60
21600 1240 Glasgow GL56 £80
21700 1241 Leeds LE01 £50
21700 1242 Leeds LE02 £40
21700 1243 Leeds LE01 £50

I would expect to see the following result based on finding duplicate values
per record in the fields Customer number, City, reponsible branch and amount.

Customer Number Invoice Number City Reponsible Branch Amount
21500 1234 London LO14 £150
21500 1237 London LO14 £150
21600 1238 Glasgow GL56 £80
21600 1240 Glasgow GL56 £80
21700 1241 Leeds LE01 £50
21700 1243 Leeds LE01 £50

--

Dave Peterson