Unique Records by Field
 

I have a list with 9 fields. There are three fields --
Last, Date, and Code -- that are important. Many of these
fields repeat -- e.g. Peters, 04/05/2003, 2 -- but when
they are unique I would like to place a 1 in a fourth
field called Unique. Thus if the above data is repeated
over 10 rows -- there would be one entry which has a 1 and
the others would have a blank or 0. If the 11th record was
Hobbs, 04/05/2003, 2 then there would be a 1 in the Unique
field as at least one field has changed.

I have done this with Advanced filter. But does someone
know a way to do it through code?

Thanks for any help.

Indu

Unique Records by Field
 

Suppose the three fields are columns B-D (2-4) starting
in row2. Then the following code will put a 1 in col A
when the next row doesn't match. (It assumes duplicates
are contiguous, not scattered.)

HTH,
Merjet

Sub macro1()
Dim iRow As Integer
Dim iCt As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iRow = ws.Range("B65536").End(xlUp).Row
ws.Range("A2:A" & iRow).Clear
ws.Range("A2") = 1
For iCt = 3 To iRow
If ws.Cells(iCt, 2) < ws.Cells(iCt - 1, 2) Or _
ws.Cells(iCt, 3) < ws.Cells(iCt - 1, 3) Or _
ws.Cells(iCt, 4) < ws.Cells(iCt - 1, 4) Then _
ws.Cells(iCt, 1) = 1
Next iCt
End Sub

Unique Records by Field
 

Another possibility. If you want to add an intermediate calculation you can
accomplish the desired result with a formula. Cell E1 will calculate = 1,
cell E2 will calculate = 0. If you have errors in any of the cells, then a
more robust formula would be needed.

Troy

A1: Peters
B1: 04/05/2003
C1: 2
D1: =A1&B1&C1
E1: =IF(COUNTIF(D$1:D1,D1)=1,1,0)

A2: Peters
B2: 04/05/2003
C2: 2
D2: =A2&B2&C2
E2: =IF(COUNTIF(D$1:D2,D2)=1,1,0)


"Indu Aronson" wrote in message
...
I have a list with 9 fields. There are three fields --
Last, Date, and Code -- that are important. Many of these
fields repeat -- e.g. Peters, 04/05/2003, 2 -- but when
they are unique I would like to place a 1 in a fourth
field called Unique. Thus if the above data is repeated
over 10 rows -- there would be one entry which has a 1 and
the others would have a blank or 0. If the 11th record was
Hobbs, 04/05/2003, 2 then there would be a 1 in the Unique
field as at least one field has changed.

I have done this with Advanced filter. But does someone
know a way to do it through code?

Thanks for any help.

Indu

Unique Records by Field
 

Here is a formula that doesn't require the intermediate calculation.

You will need to adjust:
$A$1, $B$1, $C$1 values in the formula to reflect the top row of your
range.
$A$9, $B$9, $C$9 values in the formula to reflect the bottom row of your
range.

Your choice on which formula is easier to maintain.

Troy

(watch for word wrap)

D1:
=IF(SUMPRODUCT((A1:$A$9=A1)*(B1:$B$9=B1)*(C1:$C$9= C1))=SUMPRODUCT(($A$1:$A$9
=A1)*($B$1:$B$9=B1)*($C$1:$C$9=C1)),1,0)


"TroyW" wrote in message
...
Another possibility. If you want to add an intermediate calculation you
can
accomplish the desired result with a formula. Cell E1 will calculate = 1,
cell E2 will calculate = 0. If you have errors in any of the cells, then a
more robust formula would be needed.

Troy

A1: Peters
B1: 04/05/2003
C1: 2
D1: =A1&B1&C1
E1: =IF(COUNTIF(D$1:D1,D1)=1,1,0)

A2: Peters
B2: 04/05/2003
C2: 2
D2: =A2&B2&C2
E2: =IF(COUNTIF(D$1:D2,D2)=1,1,0)


"Indu Aronson" wrote in message
...
I have a list with 9 fields. There are three fields --
Last, Date, and Code -- that are important. Many of these
fields repeat -- e.g. Peters, 04/05/2003, 2 -- but when
they are unique I would like to place a 1 in a fourth
field called Unique. Thus if the above data is repeated
over 10 rows -- there would be one entry which has a 1 and
the others would have a blank or 0. If the 11th record was
Hobbs, 04/05/2003, 2 then there would be a 1 in the Unique
field as at least one field has changed.

I have done this with Advanced filter. But does someone
know a way to do it through code?

Thanks for any help.

Indu

Unique Records by Field
 

Merjet and Troy, thank you for your help. I really
appreciate your solutions.

-----Original Message-----
Suppose the three fields are columns B-D (2-4) starting
in row2. Then the following code will put a 1 in col A
when the next row doesn't match. (It assumes duplicates
are contiguous, not scattered.)

HTH,
Merjet

Sub macro1()
Dim iRow As Integer
Dim iCt As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iRow = ws.Range("B65536").End(xlUp).Row
ws.Range("A2:A" & iRow).Clear
ws.Range("A2") = 1
For iCt = 3 To iRow
If ws.Cells(iCt, 2) < ws.Cells(iCt - 1, 2) Or _
ws.Cells(iCt, 3) < ws.Cells(iCt - 1, 3) Or _
ws.Cells(iCt, 4) < ws.Cells(iCt - 1, 4) Then
_
ws.Cells(iCt, 1) = 1
Next iCt
End Sub


.