# Edit Macro *SELF SOLVED*

#### scrupul0us

##### Well-known Member
How would i chagne this:

Code:
Sub Unique_DEPT()
Dim Sh1 As Worksheet
Dim Rng As Range
Dim Sh2 As Worksheet
Set Sh1 = Worksheets("DATA")
Set Rng = Sh1.Range("L2:L" & Sh1.Range("A65536").End(xlUp).Row)
Set Sh2 = Worksheets("RESULTS")
Rng.Cells(1, 1).Copy Sh2.Cells(1, 2)
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sh2.Range("B1"), Unique:=True
End Sub

to list my values across row1 instead of down column b?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Edited

#### Sharkie21

##### Active Member
Two ideas come to mind. 1) create a sql query to find unique entries given the list.

2) Sort the list and find the unique entries yourself and paste them into the next worksheet.

So something like
'Assume it's sorted
private sub blah()
currRow as int, prevRow as int
currRow = 2
prevRow = 1
while prevRow.value <> ""
'no duplicate
if prevRow.value <> currRow.value then
'put prevRow.value into sheet 2 or where ever
end if
prevRow + 1
currRow + 1
end sub

I think that should get all the unique entries in linear time.

NM got it

Replies
3
Views
101
Replies
13
Views
220
Replies
3
Views
177
Replies
1
Views
177
Replies
5
Views
69