Arranging in order

anee_4285

New Member
Joined
Jul 9, 2015
Messages
27
HI Team,

I want to arrange below table in order. If you see the 1st column have all the number. Now in C and G have common number with making of 1. Now i need both them in side to side. Same for others. All the columns should be arranged in order so i can know what are the alphabets have same numbers. For my reference i just added all the numbers in end. So i thought 1st we will arrange has per total then later arrange by number. You can ignore it if no need. My final output should be all the columns should be in order with have same numbers marked has "1"


Row LabelsABCDEFGHIJKLMNOP
194
197
200 1111111111 1111
204 1
206 11 111111 111
207 1
2111 1 111 1 11
212 1 1
213 1 1111111111111
221 1 1 1
22311 111 1 1 1111
227111 111111 1111
22811111111111 1111
229 11 1111111 1111
230 11 111111 1111
232 1 1
Grand Total4875687108991981011

<colgroup><col><col><col><col><col><col span="2"><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi

Not sure I understand correctly. It would be clearer if you had posted the result you expect from your example.

What I understood is that
- you first want the columns ordered by total
- and then ordered inside each total in such a way that identical columns (with 1's in the same rows) stand side by side.

For ex. the columns with total 8 have the headers B, F, I, N. In this case, for total 8 you would order them B, F, N, I. The columns with the headers B, F, N all have the 1's in the same rows and so would be side by side and only then the I column that has 1's in other rows.

Is this correct? If not, please explain.
 
Last edited:
Upvote 0
Hi

Not sure I understand correctly. It would be clearer if you had posted the result you expect from your example.

What I understood is that
- you first want the columns ordered by total
- and then ordered inside each total in such a way that identical columns (with 1's in the same rows) stand side by side.

For ex. the columns with total 8 have the headers B, F, I, N. In this case, for total 8 you would order them B, F, N, I. The columns with the headers B, F, N all have the 1's in the same rows and so would be side by side and only then the I column that has 1's in other rows.

Is this correct? If not, please explain.



I need this
ordered inside each total in such a way that identical columns (with 1's in the same rows) stand side by side.
 
Upvote 0
The usual question with sorting is to build a good key.

In this case I'd use an auxiliary row, for ex. just below the Total, where I'd build a key for each column.
Since you want identical columns side by side a simple concatenation will do with 0's for the empty cells and 1's for cells with 1's

For ex., for the first 3 columns that you posted I got
0000001000111000
0010100010111110
0010110000011110

This is a very simple procedure. You'll get the row with the keys below the total row and just select the table and sort, first by total and then for this key.


Remark: I guess you can concatenate using the new CONCAT() function, with something like for the first column

=CONCAT(IF(B2:B17="",0,1))
confirmed with cse

I don't have the function and so I wrote a small vba snippet

Code:
Function myConcat(r As Range) As String
Dim rc As Range
Dim s As String

For Each rc In r
    s = s & IIf(rc.Value = "", 0, 1)
Next rc
myConcat = s
End Function

and used in B19: =myConcat(B2:B17)

Anyway, after building the keys you can use Sort directly.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top