Return Unique Value 2

[CandyS]

I have not been able to find a solution to this question in the Tek-Tips post archives:

I can extract single, unique or common values from two lists (=IF(COUNTIF...); however I need to extract unique row combinations of values from two lists.

For example:

Sheet2 contains my data range:
A B
1 CODE LOCATION
2 12345 1 Main Street
3 12345 1 Main Street
4 12345 2 Dalton Road
5 12345 2 Dalton Road

Sheet1 (Columns A & B) will contain the formulas to return the unique row combinations of Sheet2 A2:B5
A B
1 CODE LOCATION
2 12345 1 Main Street
3 12345 2 Dalton Road

Thanks,

Candy

 

[dyarwood]

You can use something similar to


=IF(SUMPRODUCT(a6,b6)<>SUMPRODUCT(a5,b5),a6,&quot;&quot

Do you know how to use VBA code as this is probably the easiest way I can think of doing it?

dyarwood

 

[xlbo]

Sounds like advanced filter will do the trick - no formulae and a bit of code needed but......

Create a named range in Sheet2, cell A1 - call it uRecords
Then attach this code to a button:

sub GetUnique()
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(&quot;uRecords&quot, Unique:=True
end sub


Select columns A:B in Sheet2 and run it

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[CandyS]

Thanks, dyarwood and xlbo.

While the VBA solution looks great and I have in mind using it in in several reports (I already have it copied to a module and can't wait to try it!), this is a tool that I'd like to be able to just dump the raw data into and have it auto-process, so the formula solution would work best in this circumstance.

When I use the SUMPRODUCT formula I get a #VALUE error. While I use SUMPRODUCT to count the occurences of unique combinations of values, I am unable to get it to return the actual values. Any thoughts? Am I not applying the formula correctly according to the example I have above?

Thanks for your help.

Candy

 

[CandyS]

xlbo:

How can I tweak the VBA code so that the unique values paste to or in a designated cell or range

I'd like to paste the unique values from Sheet2 to Sheet1 (beginning in cell A2).

Thanks,

Candy

 

[xlbo]

As per my previous post ;-)
&quot;Create a named range in Sheet2, cell A1 - call it uRecords&quot;

slight tweak to make it more efficient:

sub GetUnique()
dim lRow as long
lRow = sheets(&quot;Sheet1&quot.cells(65536,1).end(xlup).row
with sheets(&quot;Sheet1&quot.range(&quot;A1:B&quot; & lRow)
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(&quot;uRecords&quot, Unique:=True
end with
end sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[CandyS]

xlbo: It works! I love it and it's now in my Personal.xls &quot;toolbox&quot;.

Last question: Can the range (A1:B) be changed to two non-contiguous ranges? For example, if the data I want to filter is in columns A and C, instead of A and B.

Many thanks,

Candy

 

[xlbo]

Nope - sorry - advanced filter needs a contiguous range. You need to either include B (ie use cols A:C) or move your data about a little....what's in B ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[CandyS]

I forgot about that rule, thanks.

I have a bunch of crap in B that would make every line unique.

Thanks for your help. Your script works great.

Regards,

Candy

 

[KenWright]

Geoff, what about having your code insert a helper column at the end, concatenate Cols A & C (or whatever is wanted) in it, use that to filter on, and then just clean it up at the end.

Regards
Ken.............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Very true Ken - could well do that for a specific instance

sub GetUnique()
dim lRow as long
lRow = sheets(&quot;Sheet1&quot.cells(65536,1).end(xlup).row
with sheets(&quot;Sheet1&quot
.range(&quot;A1:A&quot; & LRow).copy destination:=.range(&quot;D1&quot
with .range(&quot;C1&quot; & lRow)
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(&quot;uRecords&quot, Unique:=True
end with
end sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244