Hi Luke,

Thanks for this, I now have a formula that I not only understand but
can work with. SMALL doesn't work (yet!), purely because the number of
zeros outweighs the non zeros.

Thanks again.

Mark

Luke M;439626 Wrote:
Yes, lets go with the text-number swap. If you've got a table of
numbers, you
can use this setup. I'll assume your table is in A2:Z200

Simple formula:
=LARGE($A$2:$Z$200,ROW(A1))

To hide 0's and errors:
=IF(ISERROR(LARGE($A$2:$Z$200,ROW(A1))),"",IF(LARG E($A$2:$Z$200,ROW(A1))=0,"",LARGE($A$2:$Z$200,ROW( A1))))

You can then copy this formula down, and it'll "sort" the data. If you
need
it sorted the other way, replace LARGE with SMALL. Then, as you said,
you
could use the lookup table to convert the numbers back into text.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MarkBrazier" wrote:


Hi Luke,

Output in a single column, read top-to-bottom.

Background: Multilpe lists of countries, defined by client
designated
marketing areas. Ability for client to select more than one area
(using
tick boxes), I need to generate a list of all countries from each of
the
marketing areas selected.

Would it help if instead of a country name, I replaced this with a
number? I could then use one final lookup to convert the number back
to
a country name? Would this also help Excel to process quicker?

Thanks,

Mark
Luke M;439541 Wrote:
a few clarification points:
Do you want the output in a single column/row, same size read
left-to-right,
or same size read top-to-bottom?

Also, your example says "text" but as you also have zeroes, are
you
dealling
with actual text, or is it numbers? (numbers would be much
easier!)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MarkBrazier" wrote:


Hi,
I have a 2D array (sample below - actual array is 16 columns x
200
rows
and any cell with no data will contain a zero), and I need to
sort
all
entries into an ordered list, ignoring the zeros.

Text3 Text4 Text1 0
Text6 Text8 Text2 0
0 0 Text5 0
0 0 Text7 0
0 0 Text9 0
0 0 0 0

I have found a few different formula that can take & sort a 1D
array,
but yet to find one that helps me with this 2D array problem.

Thanks,

Mark


--
MarkBrazier


------------------------------------------------------------------------
MarkBrazier's Profile: 'The Code Cage Forums - View Profile:
MarkBrazier' ('The Code Cage Forums - View Profile: MarkBrazier'
(http://www.thecodecage.com/forumz/me...hp?userid=610))
View this thread: 'Create an ordered list from 2d array - The
Code
Cage Forums'
('Create an ordered list from 2d array - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=121942))




--
MarkBrazier

------------------------------------------------------------------------
MarkBrazier's Profile: 'The Code Cage Forums - View Profile:
MarkBrazier' (http://www.thecodecage.com/forumz/member.php?userid=610)
View this thread: 'Create an ordered list from 2d array - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=121942)




--
MarkBrazier
------------------------------------------------------------------------
MarkBrazier's Profile: http://www.thecodecage.com/forumz/member.php?userid=610
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121942