Unique records of matching pairs
 

Hello!
I need to filter unique records acording to two columms (10xy and
error).
I want the unique ocurrences of this pairs of columms independently of
the other columms.

I have tried with advanced filter but if the columm ID is diferent and
the pair (10xy and error) is the same, it will give me another record
that i don't want.

I have an example attached. In yellow the result that i want.

Thank´s :)


+-------------------------------------------------------------------+
|Filename: tests.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3946 |
+-------------------------------------------------------------------+

--
pmarques
------------------------------------------------------------------------
pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110
View this thread: http://www.excelforum.com/showthread...hreadid=479143

Unique records of matching pairs
 

The easiest way is to concatenate the two fields together in a new column and
then filter that for unique items.
--
HTH...

Jim Thomlinson


"pmarques" wrote:


Hello!
I need to filter unique records acording to two columms (10xy and
error).
I want the unique ocurrences of this pairs of columms independently of
the other columms.

I have tried with advanced filter but if the columm ID is diferent and
the pair (10xy and error) is the same, it will give me another record
that i don't want.

I have an example attached. In yellow the result that i want.

Thank´s :)


+-------------------------------------------------------------------+
|Filename: tests.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3946 |
+-------------------------------------------------------------------+

--
pmarques
------------------------------------------------------------------------
pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110
View this thread: http://www.excelforum.com/showthread...hreadid=479143

Unique records of matching pairs
 

Hi,
These few lines of code worked OK on your test file. As per Jim's
reply, I concatenated columns B & E in column F. Then just execute module.

HTH

Sub GetUnique()

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set ConRng = Range("F6:F" & lastrow) ' Concatenation of "10XY" and "Error"

j = 6 ' Output row
r = 6 ' First input row

Do While r < lastrow
Range(Cells(r, "A"), Cells(r, "E")).Copy Cells(j, "G") 'Output first
record
j = j + 1 ' Increment count of output row
n = Application.CountIf(ConRng, Cells(r, "B") & Cells(r, "E")) ' Count
of records with this key
r = r + n ' set to next concatenated key
Loop

End Sub

"Jim Thomlinson" wrote:

The easiest way is to concatenate the two fields together in a new column and
then filter that for unique items.
--
HTH...

Jim Thomlinson


"pmarques" wrote:


Hello!
I need to filter unique records acording to two columms (10xy and
error).
I want the unique ocurrences of this pairs of columms independently of
the other columms.

I have tried with advanced filter but if the columm ID is diferent and
the pair (10xy and error) is the same, it will give me another record
that i don't want.

I have an example attached. In yellow the result that i want.

Thank´s :)


+-------------------------------------------------------------------+
|Filename: tests.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3946 |
+-------------------------------------------------------------------+

--
pmarques
------------------------------------------------------------------------
pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110
View this thread: http://www.excelforum.com/showthread...hreadid=479143