Sorting

[LucySheehan]

I hope someone can help. I'm doing a spreadsheet with a list of companys down the side and a list of companys going asross. I then put a tick in the boxes of the two companys that coincide. I want to tehn sort this by the ticks. As in which companys have the most ticks. This probably makes no sense, it's hard to explain. Any ideas?

 

[Gerald Higgins]

Hi, welcome to the board.

Do you have anything that is counting the ticks ?
If yes, you can sort on that.

 

[LucySheehan]

4

 

[LucySheehan]

No, it literally just looks like this:

ABC JKL MNO PQR
DEF ✓ ✓
GHI ✓ ✓

Etc.

And then I want to sort it so the companie's with the most ticks will be at the top. Oh God, I'm not making sense.

 

[Gerald Higgins]

???

 

[Gerald Higgins]

How are you entering the ticks ? Are you using checkboxes ? Or maybe writing the letter "X" into a cell ? Or maybe entering a tick using a symbol font ? Or something else ?

 

[LucySheehan]

I'm going to Insert->Symbol->Selcting Wingdings as Font->Scrolling down to bottom and there's a tick there. I don't think there's any hope for me Gerald.

 

[Gerald Higgins]

Don't give up yet !

When I tried that, it gave me a character whose code is 252, your's is probably the same.
You can check by using

=code(a1)

and inserting the symbol into cell a1.

Then, let's say you are entering ticks into the range a1:a10

=countif(a1:a10,char(252))

will count how many cells in that range contain a single tick.

So, for example, you can use this formula to count how many ticks are in each row, OR in each column.
You can then use the results to drive your sort.

 

[Dryver14]

for ticks i have used 1's

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>company</TD><TD>a</TD><TD>h</TD><TD>c</TD><TD>h</TD><TD>e</TD><TD>h</TD><TD>g</TD><TD>g</TD><TD>h</TD><TD>a</TD><TD>a</TD><TD>b</TD><TD>h</TD><TD>b</TD><TD>total</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>a</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>b</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>c</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>d</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>e</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>f</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 12pt"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>g</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>h</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">5</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>Q3</TD><TD>=SUM(C3:P3)</TD></TR><TR><TD>Q4</TD><TD>=SUM(C4:P4)</TD></TR><TR><TD>Q5</TD><TD>=SUM(C5:P5)</TD></TR><TR><TD>Q6</TD><TD>=SUM(C6:P6)</TD></TR><TR><TD>Q7</TD><TD>=SUM(C7:P7)</TD></TR><TR><TD>Q8</TD><TD>=SUM(C8:P8)</TD></TR><TR><TD>Q9</TD><TD>=SUM(C9:P9)</TD></TR><TR><TD>Q10</TD><TD>=SUM(C10:P10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
so you would like company h to go to row 3 ?

 

[Dryver14]

if the above is the case the use Geralds formula to count on the end and then sort by that column