Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
how do I filter for 1 variable in multiple columns | Excel Worksheet Functions | |||
Connect multiple columns in 1 row to another? | New Users to Excel | |||
Convert multiple columns to rows | Excel Worksheet Functions | |||
Filtering Text Data from Multiple columns | Excel Worksheet Functions |