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!

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!

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!

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!

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!

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!

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!

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!