"Complex" Sorting Request

[Kickingkangaroos]

Hi All,

I have been trying to do this for a while, and cannot for the life of me figure it out quickly, easily - would a macro be best?

I have a spreadsheet displaying a list of approx 300 names in Column A, then various information regarding each person in Columns B, C and D.

Columns E - N inclusive represent different products (there is a maximum of 10 products available: Ball; Boat; Car; Doll; House; Kite; Pencil; Plane; Train; Truck). If a person owns a particular product, the name of that product appears (as text) in one of the columns.

However, rather than the headings for the Columns E - N being the name of a specific product (meaning if a person owns that product it would only appear in a single Column) - they are all jumbled up - for example:

Columns = E | F | G| H | I | J | K | L | M | N
Row 1 = Car | House | Boat | [Blank] | Train | [Blank] | Plane | [Blank] | [Blank] | [Blank]
Row 2 = House | [Blank] | Car | Plane | Train | [Blank] | Ball | Boat | [Blank] | [Blank]

What I need is to sort this out so the products are listed by row in alphabetical order, BUT - if a particular row doesn't have a product which would appear next alphabetically, that it leave a gap, and only add a product that they do own, in the appropriate (alphabetical) Column.

i.e. for the above 2 examples, the Rows should appear:
Row 1 = [Blank] | Boat | Car | [Blank] | House | [Blank] | [Blank] | Plane | Train | [Blank]
Row 2 = Ball | Boat | Car | [Blank] | House | [Blank] | [Blank] | Plane | Train | [Blank]

I hope this makes sense - please let me know if you need more explanation.

Many thanks,

Andy

 

[wigi]

This should be feasible with a simple COUNTIF function (for each of the columns).

Create again a table with the same 300 rows. In row 1, in the column for the first product:

=IF(COUNTIF($E1:$N1,"Ball")>0,"Ball","")

By having titles in these new columns, and make the correct relative cell references, Ball in the example above could be taken out, and you formula holds true for the columns as well (by simply dragging the formula to the right). Also, the formula could be the same for all 300 rows.

If you want, use na() instead of "" in the above formula. It will allow you to quickly trace the cells that should be empty: effectively those cells containing a formula and an error value. Hit F5 > Special... > Formulas > Errors > OK and delete the returned cells.

 

[Kickingkangaroos]

Hi Wigi,

That's great - thank you; worked really well. I should have taken a moment longer, and I could have figured it out.

I actually found another way, which worked as well.

I used the CONCATENATE function to squeeze together all possible Products for each row, then created a heading for each of the possible Products, each in a separate new Column.

Then using the SEARCH function, just looked for the heading in each column, within the concatenated string, and returned the heading name if the search was TRUE, and returned "" if the search was FALSE.

But thanks again for your reply - perfect.

Cheers,

Andy

 

[markmzz]

Another way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Col05</td><td style=";">Col06</td><td style=";">Col07</td><td style=";">Col08</td><td style=";">Col09</td><td style=";">Col10</td><td style=";">Col11</td><td style=";">Col12</td><td style=";">Col13</td><td style=";">Col14</td><td style="background-color: #FFFF00;;">Ball</td><td style="background-color: #FFFF00;;">Boat</td><td style="background-color: #FFFF00;;">Car</td><td style="background-color: #FFFF00;;">Doll</td><td style="background-color: #FFFF00;;">House</td><td style="background-color: #FFFF00;;">Kite</td><td style="background-color: #FFFF00;;">Pencil</td><td style="background-color: #FFFF00;;">Plane</td><td style="background-color: #FFFF00;;">Train</td><td style="background-color: #FFFF00;;">Truck</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Car</td><td style="text-align: right;;"></td><td style=";">Ball</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Doll</td><td style="text-align: right;;"></td><td style=";">Ball</td><td style=";"></td><td style=";">Car</td><td style=";">Doll</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Pencil</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Truck</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">Pencil</td><td style=";"></td><td style=";"></td><td style=";">Truck</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">LOOKUP(<font color="Green">1,FIND(<font color="Purple">O$1,$E2:$N2</font>)</font>)</font>),"",O$1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz