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

You could insert a helper column, and then, assuming invoice number is in
column C...

=COUNTIF(C:C,C2)1

Filter the column for "TRUE" to find all your record that have duplicates.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"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

Assume your sample data as posted is within A2:E11
Based on your specs for "duplicates", viz.:
.. finding duplicate values per record in the fields
Customer number, City, responsible branch and amount
ie data in cols A, C, D, E will collectively define "duplicates" here

Place in F2:
=IF(SUMPRODUCT((A$2:A$11=A2)*(C$2:C$11=C2)*(D$2:D$ 11=D2)*(E$2:E$11=E2))1,ROW(),"")
This is the criteria to mark duplicate lines

Then in G2:
=IF(ROWS($1:1)COUNT($F:$F),"",INDEX(A:A,SMALL($F: $F,ROWS($1:1))))
Copy G2 to K2. Select F2:K2, copy down to K11 to return the expected results
all neatly packed at the top in cols G to K. Hide/minimize col F. Success?
Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"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

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