Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting two columns of data
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. |
#2
|
|||
|
|||
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 "Max" wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote: 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 "Max" wrote in message ... 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. |
#4
|
|||
|
|||
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 "Max" wrote in message ... 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" wrote: 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 "Max" wrote in message ... 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. |
#5
|
|||
|
|||
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Max" wrote in message ... 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" wrote: 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 "Max" wrote in message ... 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. |
#6
|
|||
|
|||
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*ISBLAN K($A$1:$A$21)),COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&" ")+COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISB LANK($A$1:$A$21),0)) Biff "Ragdyer" wrote in message ... 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Max" wrote in message ... 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" wrote: 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 "Max" wrote in message ... 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. |
#7
|
|||
|
|||
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 "Max" wrote in message ... 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" wrote: 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 "Max" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo | Excel Discussion (Misc queries) | |||
sort column data with hidden columns - excel 2003 | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
Filtering Text Data from Multiple columns | Excel Worksheet Functions |