Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching pairs of Data in cell | Excel Discussion (Misc queries) | |||
Filer for unique records and return all column data for unique rec | Excel Discussion (Misc queries) | |||
Counting Unique Pairs of Cells | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Unique records of matching pairs | Excel Worksheet Functions |