Unique records from 5 different Ranges

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Jul 11, 2007

• #2

Yes this is possible.
What are the names of the worksheets?
Where do you want the results to be copied to?

 

Upvote 0

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Jul 11, 2007

• #3

You can use Advanced Filter to do this.
Select your column of data.
Data | Filter...
Choose Advanced Filter...
check "Filter to another location"
In "Copy to" Field, choose cell you want Unique data to be copied to.
check Unique records only
Click OK

 

Upvote 0

gheyman

Well-known Member

Joined

Nov 14, 2005

Messages

2,342

Office Version

1. 365

Platform

1. Windows

• Jul 11, 2007

• #4

John

The sheet names are Sheets("ENG Labor Summary).Range("A22:A200) and Sheets("OPS Labor Summary").Range("A22:A400")

It can be copied to: Sheets("WBS Dictionary").Range("A1")

If you can figure this one out that would really help.

 

Upvote 0

gheyman

Well-known Member

Joined

Nov 14, 2005

Messages

2,342

Office Version

1. 365

Platform

1. Windows

• Jul 11, 2007

• #5

Uniques Records

Thanks

Unfortionately, I was hoping to do this in either a macro or VBA. The problem with using the advanced Filter is you can only do one range at a time. Plus I was trying to automate the task. This excel program has many users. Some have very basic excel skills and Filters are beyond their capability.

Thanks for your time. Much appreciated.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Jul 11, 2007

• #6

You can record a macro to do this then edit the code. We can help with the latter.

 

Upvote 0

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Jul 11, 2007

• #7

Try this code:

Code:

Sub AFTest1()
EndRow = Sheets("WBS Dictionary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Set TargCell = Sheets("WBS Dictionary").Range("A" & EndRow)
Set FilterRng = Sheets("ENG Labor Summary").Range("A22:A200")
    FilterRng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=TargCell, Unique:=True
    
EndRow = Sheets("WBS Dictionary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Set TargCell = Sheets("WBS Dictionary").Range("A" & EndRow)
Set FilterRng = Sheets("OPS Labor Summary").Range("A22:A200")
    FilterRng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=TargCell, Unique:=True
End Sub

 

Upvote 0

gheyman

Well-known Member

Joined

Nov 14, 2005

Messages

2,342

Office Version

1. 365

Platform

1. Windows

• Jul 11, 2007

• #8

Thanks!!!!

That worked perfectly. I tried to record running the Advance filter and got nothing.

What you have is pefect - Datsmart!

 

Upvote 0

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Jul 11, 2007

• #9

You are correct, recording Advanced Filtering seems to be worthless.
I have tried to collect as much code as possible for advanced filtering from posts on this forum. It pays off later.

 

Upvote 0