Sorting two columns of data

[Guest]

Hi,
I have two columns of numbers, that I need to sort into one column.
They are not sequential and some of the same numbers appear in each column.
In the resulting column I need all the numbers listed in ascending order
without duplication. The first column will range from 1 to 120 but not with
all the numbers in the list from 1 to 120. (some numbers will be missing).
The second column may have duplications of the first column, but with some
unique numbers. eg (short list)

1 1
5 5
13 16
16 19
19 34
21 37
24 49
29 50
33 51
36 54
37 60
39 72
40 85
42 102
50
51
54
60
61
65
66
69
70
How can I sort these using functions.

 

[CLR]

Hi Max..........

There are various ways to do what you want, but personally I prefer to just
cut and paste the second column onto the bottom ot the first column and then
use "ASAP Utilities" to delete all the duplicates in one fell-swoop. This
is just one of the features of the ASAP Utilities add-in which may be
obtained free at www.ASAP-utilities.com

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Hello Chuck,
I have downloaded the Utilities pack you suggested. Thank you.
Would I be able to automate this process using these utilities?
My intent was to be able to import data from a database and have the sort
function automatic. Not having to do any manual tasks.

Thank you for your help and advise.

 

[CLR]

I suppose it's possible, I've not tried to do it yet......I do know that
drop-down commands won't come through on the macro-recorder........perhaps
someone from the Programming Group could suggest a bit of code.......or
maybe the folks at ASAP could help.

Vaya con Dios,
Chuck, CABGx3

 

[Ragdyer]

To do this *automatically*, using functions, as your OP requested, you could
set up 3 columns with formulas, where the third column will return your
sorted numbers with the duplicates eliminated.

Columns A & B will contain your numbers.

Try these in say, E1, F1, and G1.

E1:
=IF(ISERR(SMALL($A$1:$B$50,ROW())),"",SMALL($A$1:$B$50,ROW()))

F1:
=IF(COUNTIF($E$1:E1,E1)=1,E1,"")

G1:
=SMALL($F$1:$F$38,ROW())

Then select all 3 columns and drag down to copy.

I didn't remove the error message for the third column, just to remind you
that it's there, instead of having 3 empty columns in case you hadn't yet
imported your numbers.
You could remove it if you wish by just following the error trap in the
first formula, which BTW is *necessary* for the other formulas to work
properly in the event of missing data.

These worked well for me using the data you posted.

What scares me most of the time is when data is *imported*, which means it
could contain any number of assorted, invisible characters, and be in any
form, other then actual numbers.

 

[Biff]

Hi!

If you can cut/paste and create a single column.......

This amazing formula by the late Frank Kabel will sort uniques in ascending
order: It will handle text, numerics or both.

Entered as an array:

=INDEX($A$1:$A$21,MATCH(MIN(COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&"")+
COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISBLANK($A$1:$A$21)),COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&"")+COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISBLANK($A$1:$A$21),0))

Biff

 

[CLR]

Hi Max..........

If you're into VBA, you can record a small macro to cut the second column
and paste it to the bottom of the first column, then select it and sort,
ascending, then call the nifty macro that Chip Pearson has on his site
www.cpearson.com called "Public Sub DeleteDuplicateRows()"..........I just
tried it on your sample data and it works really good........no extra
columns or formulas needed.........

That way you could import your data, then select column A and then just run
the macro(s)......that's pretty automatic......

Vaya con Dios,
Chuck, CABGx3