Create an ordered list from 2d array

[MarkBrazier]

Hi,
I have a 2D array (sample below - actual array is 16 columns x 200 row
and any cell with no data will contain a zero), and I need to sort al
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,

Mar

 

[Luke M]

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!)

 

[MarkBrazier]

Hi Luke,

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

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

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

Thanks,

 

[Luke M]

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(LARGE($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.

 

[Ashish Mathur]

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, D17 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/index.php/using-access-to-combine-multiple-excel-files-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]

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]

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),ROW(A1))),"",SMALL(IF($A$2:$Z$200<>0,$A$2:$Z$200),ROW(A1)))