Create an ordered list from 2d array
 

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: http://www.thecodecage.com/forumz/member.php?userid=610
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121942

Create an ordered list from 2d array
 

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: http://www.thecodecage.com/forumz/member.php?userid=610
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121942

Create an ordered list from 2d array
 

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' (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

Create an ordered list from 2d array
 

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' (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

Create an ordered list from 2d array
 

Hi,

First of all, replace all 0's with blanks. Give a heading to the 4 columns.
Now select B1:B7, and insert a name to it (Insert Name Define), say
Heading_1. Do this for the 3 other columns (C1:C7, D1:D7 and E1:E7) with
names as Heading_2, Heading_3, Heading_4. Now select range B1:B7 again and
press Ctrl+L (to convert it to a range). Do this for the 3 other ranges as
well. Now save and close the Excel file

Now use the Access procedure outlined at the following link -
http://datapigtechnologies.com/blog/...iles-method-1/.
When writing the union all statements, replace the North, East and West with
Heading_1, Heading_2 and Heading_3. Also write another union all statement
for heading_4 (So there will be will be 4 Select All statements and 3 Union
All's).

After you save and run the query, you will see one list of all the 4 ranges.
Close and save the query and exit MS Access

Now open the Excel file and under go to Data From Other Sources From
Microsoft Query. Select Microsoft Access and click on OK You will now see
the Query which you ran on the left had side. Get that to the right by
clicking on the arrow. When you come to the filter screen, apply a filter
saying 0. Click on OK and now you will see the Access list imported into
Excel.

Post back to let us know how this worked.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MarkBrazier" wrote in message
...

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:
http://www.thecodecage.com/forumz/member.php?userid=610
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=121942

Create an ordered list from 2d array
 

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

Create an ordered list from 2d array
 

To discount the 0's using SMALL, our simple formula becomes the array formula:

=SMALL(IF($A$2:$Z$200<0,$A$2:$Z$200),ROW(A1))

Note that array formulas need to be confirmed using Ctrl+Shift+Enter, not
just Enter.

With error checking:

=IF(ISERROR(SMALL(IF($A$2:$Z$200<0,$A$2:$Z$200),R OW(A1))),"",SMALL(IF($A$2:$Z$200<0,$A$2:$Z$200),R OW(A1)))



--
Best Regards,

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


"MarkBrazier" wrote:


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