sorting numbers

G

gregork

On Sheet 1- Column A, I have a list of order numbers. The same order number
can be listed many times. On Sheet 2 -Column A, I want to I want to copy all
of the numbers from Sheet 1 but I do not want to list the same number
several times. So in other words on Sheet 2 I want to filter through the
numbers on Sheet 1 so that I only have one listing of each number on Sheet
2.

gregorK
 
W

William

Hi gregork

I'd be tempted to copy the complete list in Column A of Sheet1 to the end of
Column A on sheet 2. Then I would use the advanced filter on Column A of
Sheet2 to extract a list of unique numbers to Column B of Sheet2. Then I
would delete Column A of Sheet2.

--
XL2002
Regards

William

[email protected]

| On Sheet 1- Column A, I have a list of order numbers. The same order
number
| can be listed many times. On Sheet 2 -Column A, I want to I want to copy
all
| of the numbers from Sheet 1 but I do not want to list the same number
| several times. So in other words on Sheet 2 I want to filter through the
| numbers on Sheet 1 so that I only have one listing of each number on Sheet
| 2.
|
| gregorK
|
|
|
 
D

Domenic

Hi,

You can use Advanced Filter for this. Try the following:

1) Start your filter from Sheet 2
2) Date > Filter > Advanced Filter
3) Check "Copy to another location"
4) Select your "List Range" on Sheet 1, making sure you include your
header
5) Select A1 on Sheet 2 as your "Copy to" location
6) Check "Unique records only", and click OK

Hope this helps!
 
G

gregork

Thanks for your replies guys. Sorry I was hoping to be able to do this
without using advanced filters. My list of numbers is always expanding so I
don't want to "manually" filter the list every time I have a new number
added.....I would prefer to have a formula take care of it automatically.

Thanks
gregorK
 
P

pgeraf

Hi Gregork,

What you need is an Array formula.

Suppose you have a list of order numbers starting at cell A2 in Sheet
going all the way down to, say, cell A50. Go to cell A2 in Sheet 2 an
type the following

formula:

=IF(SUM((Sheet1!A2=Sheet2!$A$1:A1)*1)=0,Sheet1!A2,"")
Very important: DO NOT HIT "Enter". Instead hit "Ctrl" + "Shift"
"Enter" and you should end up with this:

{=IF(SUM((Sheet1!A2=Sheet2!$A$1:A1)*1)=0,Sheet1!A2,"")}

Now all you've got to do is copying the formula all the way down t
cell A50 in

Sheet 2 (or whatever it is the last cell in your list of orde
numbers).

Enjoy
 
W

William

Hi

You could use a macro.....

Sub test()
Dim r As Range, ws As Worksheet
Dim ws1 As Worksheet
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Sheets("Sheet1")
Set ws1 = ThisWorkbook.Sheets("Sheet2")
With ws
Set r = .Range(.Range("A1"), _
..Range("A" & Rows.Count).End(xlUp))
End With
With ws1
r.Copy .Range("A65000").End(xlUp).Offset(1, 0)
Set r = .Range(.Range("A1"), _
..Range("A" & Rows.Count).End(xlUp))
r.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("B1"), Unique:=True
..Range("B:B").Copy .Range("A1")
..Range("B:B").Delete
End With
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William

[email protected]

| Thanks for your replies guys. Sorry I was hoping to be able to do this
| without using advanced filters. My list of numbers is always expanding so
I
| don't want to "manually" filter the list every time I have a new number
| added.....I would prefer to have a formula take care of it automatically.
|
| Thanks
| gregorK
|
| | > On Sheet 1- Column A, I have a list of order numbers. The same order
| number
| > can be listed many times. On Sheet 2 -Column A, I want to I want to copy
| all
| > of the numbers from Sheet 1 but I do not want to list the same number
| > several times. So in other words on Sheet 2 I want to filter through the
| > numbers on Sheet 1 so that I only have one listing of each number on
Sheet
| > 2.
| >
| > gregorK
| >
| >
| >
|
|
 
G

gregork

Thanks for the replies. I like pgeraf's formula only trouble is I end up
with lots of blank rows.

gregorK
 
A

AlfD

Hi!

Let's look at the logic of this.

You have a list with duplicates in it.
You can take out duplicates once and for all using Advanced Filter.
You don't want any more duplicates to be put in thereafter.

If that is the scenario, you already have the Filter bit available.
So you only want to stop new duplicates which you can do using, fo
example, Data Validation.

Is this what you are seeking?

Al
 
Top