If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is it possible to automatically sort cells easily? (I'm going mad!)
Hi all
My problem is this... 8o0 I need to sort the information contained within a hidden table (4 rows by 8 columns) and display the results in a second table that is visible. The sort is based on numerical values (descending) in the 8th column of the hidden table - the results of the sort need to be refined further by values in the 7th column if those in the 8th column are equal. But... I need the display table to rearrange itself automatically as the values in the hidden table change. In short, I need it to work exactly as the sort button on the toolbar - but without my intervention! In case you're wondering, it's to sort a football league table as the results of the matches come in. Thanks in advance Andy |
#2
|
|||
|
|||
Is it possible to automatically sort cells easily? (I'm going mad!)
Andy,
If you want to do that using formulas, then you need to add 2 helper columns to your original table - the first will return the rank of the value of the second, which must contain some formula that returns a number or other value that you can sort on. An example will help. Suppose you have 1 10 3 6 1 5 2 11 and want to sort based on the first column and tie-break on the second. Add two columns - let's say A and B, so that your data is now in C and D. In B1, use the formula = C1 + .00001*D1 +row()*.000000001 This formula must be designed so that the resulting values are always properly scaled, and will sort the same as if you used multiple column sorting. In this example, I multiply the second column by .00001, so that the resulting number is always less than the next larger value in column C. The row() is used as a tie-breaker. In A1, use the formula =RANK(B1,$B$1:$B$4) and copy down to A4. Now to make your auto-sorting table. In cell F1, use the formula =VLOOKUP(ROW(),$A$1:$D$4,3,FALSE) and in G1, =VLOOKUP(ROW(),$A$1:$D$4,4,FALSE) and copy down for a total of 4 rows. If your table doesn't start on row 1, then you need to use something like this, where your table starts in cell F11: =VLOOKUP(ROW()-ROW($F10),$A$1:$D$4,3,FALSE) And as the values in your data table change, your resulting table will re-sort automatically. HTH, Bernie MS Excel MVP "Andy Sandford" wrote in message ... Hi all My problem is this... 8o0 I need to sort the information contained within a hidden table (4 rows by 8 columns) and display the results in a second table that is visible. The sort is based on numerical values (descending) in the 8th column of the hidden table - the results of the sort need to be refined further by values in the 7th column if those in the 8th column are equal. But... I need the display table to rearrange itself automatically as the values in the hidden table change. In short, I need it to work exactly as the sort button on the toolbar - but without my intervention! In case you're wondering, it's to sort a football league table as the results of the matches come in. Thanks in advance Andy |
#3
|
|||
|
|||
Is it possible to automatically sort cells easily? (I'm going mad!)
Bernie
Thanks for your help - that was right on the money! It took a little figuring out to apply to my table. But once I could see what you were doing, it was a piece of cake! Thanks again Andy "Bernie Deitrick" wrote in message ... Andy, If you want to do that using formulas, then you need to add 2 helper columns to your original table - the first will return the rank of the value of the second, which must contain some formula that returns a number or other value that you can sort on. An example will help. Suppose you have 1 10 3 6 1 5 2 11 and want to sort based on the first column and tie-break on the second. Add two columns - let's say A and B, so that your data is now in C and D. In B1, use the formula = C1 + .00001*D1 +row()*.000000001 This formula must be designed so that the resulting values are always properly scaled, and will sort the same as if you used multiple column sorting. In this example, I multiply the second column by .00001, so that the resulting number is always less than the next larger value in column C. The row() is used as a tie-breaker. In A1, use the formula =RANK(B1,$B$1:$B$4) and copy down to A4. Now to make your auto-sorting table. In cell F1, use the formula =VLOOKUP(ROW(),$A$1:$D$4,3,FALSE) and in G1, =VLOOKUP(ROW(),$A$1:$D$4,4,FALSE) and copy down for a total of 4 rows. If your table doesn't start on row 1, then you need to use something like this, where your table starts in cell F11: =VLOOKUP(ROW()-ROW($F10),$A$1:$D$4,3,FALSE) And as the values in your data table change, your resulting table will re-sort automatically. HTH, Bernie MS Excel MVP "Andy Sandford" wrote in message ... Hi all My problem is this... 8o0 I need to sort the information contained within a hidden table (4 rows by 8 columns) and display the results in a second table that is visible. The sort is based on numerical values (descending) in the 8th column of the hidden table - the results of the sort need to be refined further by values in the 7th column if those in the 8th column are equal. But... I need the display table to rearrange itself automatically as the values in the hidden table change. In short, I need it to work exactly as the sort button on the toolbar - but without my intervention! In case you're wondering, it's to sort a football league table as the results of the matches come in. Thanks in advance Andy |
Thread Tools | |
Display Modes | |
|
|