• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ARRANGEMENT , HELP!

L

lalit232

New Member
[pre]
Code: 
T1	T2	T3	T4	T5	T6	T7	E-117	E-120	E-125	E-124
0	2	0	0	0	0	0	2	0	0	3
0	2	0	0	0	0	0	0	0	6	3
0	3	0	0	0	0	0	0	0	5	4

this data is to arranged like this:

T2 T2 T2
E-117  E-125 E-125
[/pre]
E-124 E-124 E-124
 
Hi lalit232,


Should it not be like this:

[pre]
Code: 
T2       T2       T2
E-117    E-125    -
E-125    -        -
E-124    E-124    E-124
[/pre]
?? Can you explain the rule that creates the second pattern?


Faseeh
 
it show the head name of the 1st row (with value grater than zero) in 1st coloumn


t1 t2 t3 ... are the head names


lalit
 
Hi lalit232


Do you want like this?


T2 T2 T2

E-117

E-125 E-125

E-124 E-124 E-124


Can you please post a sample workbook? since we are not aware where you want to have the output.
 
here the logic is ist row has numbers and i want to take the header name ( ie ti , E-125 , etc) from ist row which has value ( number) grater than zero in a column .


example :


T1 T2 T3 T4 T5 T6 T7 E-117 E-120 E-125 E-124

0 2 0 0 0 0 0 2 0 0 3

0 2 0 0 0 0 0 0 0 6 3

0 3 0 0 0 0 0 0 0 5 4

this data is to arranged like this:

T2 T2 T2

E-117 E-125 E-125

E-124 E-124 E-124


ist row : value is 2 , 2, 3 and their header name are t2 E-117 , E-124 and these are arranged in a column.
 
Assuming that the Data starts from B2 to H2

in B 8 enter this formula =if(b3>0,b2,"") - This will display only the header column which has data greater that 0.

In the next column, add this formula, A column is the helper column , so please keep the A column empty.

=IF(COLUMN()-COLUMN($A$2)+1<=COUNTIF($B$8:$H$8,"<>"),INDEX($B$8:$H$8,SMALL(IF($B$8:$H$8<>"",COLUMN($B$8:$H$8)-COLUMN($B$8)+1),COLUMNS(A$2:$A2))),"")


This is a array Formula (Shift+Ctrl+Enter)
 
I could not understand where (index no ) is formula is to put ?


=IF(COLUMN()-COLUMN($A$2)+1<=COUNTIF($B$8:$H$8,"<>"),INDEX($B$8:$H$8,SMALL(IF($B$8:$H$8<>"",COLUMN($B$8:$H$8)-COLUMN($B$8)+1),COLUMNS(A$2:$A2))),"")


please help!!!
 
Please check your workbook, how can t3 come in 3 (In Answer) when in the source data it is t5 has 3
 
Hi Lalit232,


Please see and comment, i am doubt full regarding your stated solution.


http://dl.dropbox.com/u/60644346/Rearrange%20a%20Matrix.xlsx


Faseeh
 
You must log in or register to reply here.
Back
Top