Dublicate Row and Removing

[esengul]

HI
I have a list in MS Excel looks like this
ID Fname LName ContactRoleID
1 A B 1
1 A B 2
2 C D 1
3 E F 1
3 E F 2
4 G H 1
5 I J 1
5 I J 2


What i want to do is, remove the rows where ContactRoleID is = 1 and if i have duplicate records
I dont know i i make sense or myself clear. If not, please let me know. I try to explain it in better way
Thanks in advance

 

[Bong]

let me see if I understand the problem...

You have a list where the records can be in any order. That is, the duplicates may be separated.

If there are 2 records where ID, Fname, and LName are the same, and in one of them ContactRoleID is "1", and in the other it's something other than "1", then delete the record where ContactRoleID is "1". Is that right?

If so, then I think you have loop through each record, and when you encounter a "1" in the ContactRoleID column, then you have to loop through the whole list again and look for a duplicate record. If you don't find one, continue back in the first loop. If you do, you delete the "1" record and start over (because the row numbers will change when you delete the record). Does that sound about right?

Alternatively, if you can assure that the data are pre-sorted in some way, you can considerably reduce the loops.

_________________
Bob Rashkin

 

[Gavona]

With the example as shown you could achieve this with a pivot table.
ID, FName, LName are row fields
ContactRoleID in the data area using the Min function
Edit each row field to set subtotals to "none"
In table options untick grand totals for columns

When finished copy and pastespecial values....Your new database.

Easier and quicker than looping.


Gavin