Filtering on multiple columns

I have similar data in multiple columns and I want to filter on all columsn togather to get unique values. Is there a good way to do so.

I'd create a new worksheet and copy all the data into one column. Then I'd use
data|filter|Advanced filter to extract just the unique records.

Debra Dalgleish explains the data|filter|advanced filter stuff at:
http://www.contextures.com/xladvfilter01.html#FilterUR

If your combined list is too large, you can use advanced filter on each column
and then merge those unique records together and do one more advanced filter.

wammer wrote:
>
> I have similar data in multiple columns and I want to filter on all
> columsn togather to get unique values. Is there a good way to do so.
>
> --
> wammer
> ------------------------------------------------------------------------
> wammer's Profile: http://www.excelforum.com/member.php...o&userid=12452
> View this thread: http://www.excelforum.com/showthread...hreadid=399224

--

Dave Peterson

The problem is that I have 247 columns and 5000 rows so it will take a long time to do it individually. Is there a Macro I could write?

Originally Posted by Dave Peterson

I'd create a new worksheet and copy all the data into one column. Then I'd use
data|filter|Advanced filter to extract just the unique records.

Debra Dalgleish explains the data|filter|advanced filter stuff at:
http://www.contextures.com/xladvfilter01.html#FilterUR

If your combined list is too large, you can use advanced filter on each column
and then merge those unique records together and do one more advanced filter.

wammer wrote:
>
> I have similar data in multiple columns and I want to filter on all
> columsn togather to get unique values. Is there a good way to do so.
>
> --
> wammer
> ------------------------------------------------------------------------
> wammer's Profile: http://www.excelforum.com/member.php...o&userid=12452
> View this thread: http://www.excelforum.com/showthread...hreadid=399224

--

Dave Peterson

All 247 columns in one worksheet? And 5000 rows per column???

How many unique entries do you expect (possible 247*5000=1,235,000) way too many
for this techique to work.

This seemed to work ok for me.

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FirstCol As Long
Dim LastCol As Long
Dim FirstRow As Long
Dim iCol As Long
Dim DestCell As Range

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 2 'headers in row 1
FirstCol = 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

newWks.Range("a1").Value = "Header"
Set DestCell = newWks.Range("a2")

For iCol = FirstCol To LastCol
.Range(.Cells(FirstRow, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp)).Copy _
Destination:=DestCell
With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp)
If DestCell.Row > 40000 Then
Call doAdvancedFilter(.Range("a:a"))
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp)
End If
End With
Next iCol
End With

'and once more for good measure!
With newWks
Call doAdvancedFilter(.Range("a:a"))
Set DestCell = .UsedRange 'try to reset last used cell
End With

End Sub

Sub doAdvancedFilter(rng As Range)
rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=rng(1).Offset(0, 1), Unique:=True
rng.Delete
End Sub







wammer wrote:
>
> The problem is that I have 247 columns and 5000 rows so it will take a
> long time to do it individually. Is there a Macro I could write?
>
> Dave Peterson Wrote:
> > I'd create a new worksheet and copy all the data into one column. Then
> > I'd use
> > data|filter|Advanced filter to extract just the unique records.
> >
> > Debra Dalgleish explains the data|filter|advanced filter stuff at:
> > http://www.contextures.com/xladvfilter01.html#FilterUR
> >
> > If your combined list is too large, you can use advanced filter on each
> > column
> > and then merge those unique records together and do one more advanced
> > filter.
> >
> > wammer wrote:
> > >
> > > I have similar data in multiple columns and I want to filter on all
> > > columsn togather to get unique values. Is there a good way to do
> > so.
> > >
> > > --
> > > wammer
> > >
> > ------------------------------------------------------------------------
> > > wammer's Profile:
> > http://www.excelforum.com/member.php...o&userid=12452
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=399224
> >
> > --
> >
> > Dave Peterson
>
> --
> wammer
> ------------------------------------------------------------------------
> wammer's Profile: http://www.excelforum.com/member.php...o&userid=12452
> View this thread: http://www.excelforum.com/showthread...hreadid=399224

--

Dave Peterson

Thank you so much for the reply Dave...it worked perfectly!