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>
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,755
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:

anee_4285

New Member
Joined
Jul 9, 2015
Messages
27
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,755
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.
 

Forum statistics

Threads
1,084,753
Messages
5,379,663
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

Top