Sorting
 

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!

Sorting
 

What kind of values do you want to sort ? Numbers ? text ?
--
Festina Lente


"Newbeetle" wrote:

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!

Sorting
 

Hi after going through a lot of postings I found this below and it works a
treat for numbers, but I wanted to do the same with part letter, part number
as the data I wish to sort is in this sequence A1, A2 etc then on to B1, B2
etc


=SMALL($B$1:$B$51,ROW())

"PapaDos" wrote:

What kind of values do you want to sort ? Numbers ? text ?
--
Festina Lente


"Newbeetle" wrote:

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!

Sorting
 

Are the values you need to sort unique, without any repetition ?
--
Festina Lente


"Newbeetle" wrote:

Hi after going through a lot of postings I found this below and it works a
treat for numbers, but I wanted to do the same with part letter, part number
as the data I wish to sort is in this sequence A1, A2 etc then on to B1, B2
etc


=SMALL($B$1:$B$51,ROW())

"PapaDos" wrote:

What kind of values do you want to sort ? Numbers ? text ?
--
Festina Lente


"Newbeetle" wrote:

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!

Sorting
 

Yes

"PapaDos" wrote:

Are the values you need to sort unique, without any repetition ?
--
Festina Lente


"Newbeetle" wrote:

Hi after going through a lot of postings I found this below and it works a
treat for numbers, but I wanted to do the same with part letter, part number
as the data I wish to sort is in this sequence A1, A2 etc then on to B1, B2
etc


=SMALL($B$1:$B$51,ROW())

"PapaDos" wrote:

What kind of values do you want to sort ? Numbers ? text ?
--
Festina Lente


"Newbeetle" wrote:

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!

Sorting
 

Lets say your column to sort is "range_to_sort" and the first cell of the
sorted results is "sorted_table_anchor", then this array formula
(Ctrl+Shift+Enter)
should give you the sorted table:

=IF(ISNA(MATCH(ROW() - ROW(sorted_table_anchor), IF(ISBLANK(range_to_sort),
"", COUNTIF(range_to_sort, "<" & range_to_sort)), 0)),
IF(ROW(sorted_table_anchor) + COUNTA(range_to_sort) - ROW() 0, "DUPLICATE",
""), INDEX(range_to_sort, MATCH(ROW() - ROW(sorted_table_anchor),
IF(ISBLANK(range_to_sort), "", COUNTIF(range_to_sort, "<" & range_to_sort)),
0)))
--
Festina Lente


"Newbeetle" wrote:

Yes

"PapaDos" wrote:

Are the values you need to sort unique, without any repetition ?
--
Festina Lente


"Newbeetle" wrote:

Hi after going through a lot of postings I found this below and it works a
treat for numbers, but I wanted to do the same with part letter, part number
as the data I wish to sort is in this sequence A1, A2 etc then on to B1, B2
etc


=SMALL($B$1:$B$51,ROW())

"PapaDos" wrote:

What kind of values do you want to sort ? Numbers ? text ?
--
Festina Lente


"Newbeetle" wrote:

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!

Sorting
 

Thank you, I've give that a go.

"PapaDos" wrote:

Lets say your column to sort is "range_to_sort" and the first cell of the
sorted results is "sorted_table_anchor", then this array formula
(Ctrl+Shift+Enter)
should give you the sorted table:

=IF(ISNA(MATCH(ROW() - ROW(sorted_table_anchor), IF(ISBLANK(range_to_sort),
"", COUNTIF(range_to_sort, "<" & range_to_sort)), 0)),
IF(ROW(sorted_table_anchor) + COUNTA(range_to_sort) - ROW() 0, "DUPLICATE",
""), INDEX(range_to_sort, MATCH(ROW() - ROW(sorted_table_anchor),
IF(ISBLANK(range_to_sort), "", COUNTIF(range_to_sort, "<" & range_to_sort)),
0)))
--
Festina Lente


"Newbeetle" wrote:

Yes

"PapaDos" wrote:

Are the values you need to sort unique, without any repetition ?
--
Festina Lente


"Newbeetle" wrote:

Hi after going through a lot of postings I found this below and it works a
treat for numbers, but I wanted to do the same with part letter, part number
as the data I wish to sort is in this sequence A1, A2 etc then on to B1, B2
etc


=SMALL($B$1:$B$51,ROW())

"PapaDos" wrote:

What kind of values do you want to sort ? Numbers ? text ?
--
Festina Lente


"Newbeetle" wrote:

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!

Sorting
 

Bienvenue !

The present formula will only give you one sorted column.
Let me know if you need help modifying it...
--
Festina Lente


"Newbeetle" wrote:


Thank you, I've give that a go.

"PapaDos" wrote:

Lets say your column to sort is "range_to_sort" and the first cell of the
sorted results is "sorted_table_anchor", then this array formula
(Ctrl+Shift+Enter)
should give you the sorted table:

=IF(ISNA(MATCH(ROW() - ROW(sorted_table_anchor), IF(ISBLANK(range_to_sort),
"", COUNTIF(range_to_sort, "<" & range_to_sort)), 0)),
IF(ROW(sorted_table_anchor) + COUNTA(range_to_sort) - ROW() 0, "DUPLICATE",
""), INDEX(range_to_sort, MATCH(ROW() - ROW(sorted_table_anchor),
IF(ISBLANK(range_to_sort), "", COUNTIF(range_to_sort, "<" & range_to_sort)),
0)))
--
Festina Lente


"Newbeetle" wrote:

Yes

"PapaDos" wrote:

Are the values you need to sort unique, without any repetition ?
--
Festina Lente


"Newbeetle" wrote:

Hi after going through a lot of postings I found this below and it works a
treat for numbers, but I wanted to do the same with part letter, part number
as the data I wish to sort is in this sequence A1, A2 etc then on to B1, B2
etc


=SMALL($B$1:$B$51,ROW())

"PapaDos" wrote:

What kind of values do you want to sort ? Numbers ? text ?
--
Festina Lente


"Newbeetle" wrote:

I have one column with data but some rows are blank. Is is possible to set up
function like "advanced sort" where the data from the column that isnt blank
is copied to another column then sorted, but so that if any items are altered
in the first column the second column automatically updates itself without
the need to run a sort again? Hope that makes sense!