Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
wammer
 
Posts: n/a
Default 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.


--
wammer
------------------------------------------------------------------------
wammer's Profile: http://www.excelforum.com/member.php...o&userid=12452
View this thread: http://www.excelforum.com/showthread...hreadid=399224

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
wammer
 
Posts: n/a
Default


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

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
wammer
 
Posts: n/a
Default


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


--
wammer
------------------------------------------------------------------------
wammer's Profile: http://www.excelforum.com/member.php...o&userid=12452
View this thread: http://www.excelforum.com/showthread...hreadid=399224

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns Sam via OfficeKB.com Excel Worksheet Functions 4 July 14th 05 09:15 PM
how do I filter for 1 variable in multiple columns California Excel Worksheet Functions 1 March 18th 05 11:36 PM
Connect multiple columns in 1 row to another? tb New Users to Excel 1 March 4th 05 10:57 PM
Convert multiple columns to rows Lois Lane Excel Worksheet Functions 8 January 10th 05 01:47 AM
Filtering Text Data from Multiple columns Brad Excel Worksheet Functions 6 January 1st 05 04:32 PM


All times are GMT +1. The time now is 09:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"