• 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.

how to transpose multiple rows into one column without 0 in excel [SOLVED]

Hi @abhi2611:


Absolutely... however, I had to change the approach slightly.


In my setup, I had the label "Headings" in cell L8, and the label "List" in cell M8.

Put the following formula in cell L9:

=IFERROR(IF(COUNTIF(L$8:L8, L8)<COUNTA(INDEX(List, 0, MATCH(L8,Headings,0))), L8,NA()), INDEX(Headings, MATCH(1, ISNA(MATCH(Headings, L$8:L8,0))*(TRANSPOSE(MMULT(TRANSPOSE(N(List<>"")), ROW(List)^0)>0)),0)))


enter with Ctrl + Shift + Enter, instead of Enter


Copy down to additional rows until you get an error value.


Put the following formula in cell M9:

=INDEX(List, SMALL(IF(INDEX(List, 0, MATCH(L9, Headings,FALSE))<>"", ROW(INDEX(List, 0, MATCH(L9, Headings,FALSE)))-MIN(ROW(List))+1), COUNTIF(L$8:L8, L9)+1), MATCH(L9, Headings,FALSE))


enter with Ctrl + Shift + Enter, instead of Enter


Copy down to additional rows


I got the following output for your sample data:

[pre]
Code: 
Headings	List
Jan	         A
Jan	         I
Feb	         B
Feb	         F
Feb	         J
Mar	         C
Mar	         K
Apr	         D
May	         E
May	         H
May	         M
[/pre]
I did some tests but please make sure you test these formulas further.


I am certain that the formulas can be optimized. I will look at optimizing them once you confirm that they are working for you.


Cheers,

Sajan.
 
He's not the (excel) messiah. He's just a very naughty boy. - Monty Python.


ExcelMessiah would certainly be a funny webpage name. But your code would have to be impeccable! Too much pressure for a mere mortal like me ;-)


Sajan, will post some of the challenges I put up on the EHA forum.
 
Hello @abhi2611:

I am not sure I understand your question.


You should be able to key in the above formulas as is.


-Sajan.
 
Sajan,


I am a little confused... Assume I have the heading in L8 and the list starting from L9 as shown below...Would the formula you have still work?


Jan Feb Mar Apr May

A B C D E

F G H

I J K M
 
Hi @abhi2611:


Please review the instructions in my note above...


I reproduced a portion of those instructions below.

The label "Headings" in cell L8, and the label "List" in cell M8.

Put the following formula in cell L9:

=IFERROR(IF(COUNTIF(L$8:L8, L8)<COUNTA(INDEX(List, 0, MATCH(L8,Headings,0))), L8,NA()), INDEX(Headings, MATCH(1, ISNA(MATCH(Headings, L$8:L8,0))*(TRANSPOSE(MMULT(TRANSPOSE(N(List<>"")), ROW(List)^0)>0)),0)))


enter with Ctrl + Shift + Enter, instead of Enter


Copy down to additional rows until you get an error value.


Put the following formula in cell M9:

=INDEX(List, SMALL(IF(INDEX(List, 0, MATCH(L9, Headings,FALSE))<>"", ROW(INDEX(List, 0, MATCH(L9, Headings,FALSE)))-MIN(ROW(List))+1), COUNTIF(L$8:L8, L9)+1), MATCH(L9, Headings,FALSE))


enter with Ctrl + Shift + Enter, instead of Enter


-Sajan.
 
Sajan,


I appreciate your patience on this one. But I am a little confused on the "List" being in cell M8.


My table is as follows


Jan 14 Feb 14 Mar 14 Apr 14 May 14 Jun 14 Jul 14 Aug 14 Sep 14 Oct 14 Nov 14

NV 61A NV 61C NV 61E GH 55E GH 58A GH 58C GH 58F GH 58H GH 53C GH 53E GH 53G

NV 61B NV 61D GH 55D GH 55F GH 58B GH 58D GH 58G GH 53B GH 53D GH 53F GH 53H


And I would like for it to be


NV61A - Jan

NV61B - Jan

NV61C - Feb

NV61D - Feb

...........

So on....


I really appreciate your help on this.


Thanks,

A.G.
 
Hi @abhi2611:


I think I understand your confusion... it is just the label called "List" in cell M8.


The actual list can be somewhere else in your workbook.


You can certainly upload your file to one of the filesharing sites like http://www.speedyshare.com/

and post a link to that uploaded file back here in this post.


However, I would not be able to access it for at least another 8-12 hours, since I do not have access to file sharing sites during the day.


Hopefully, the clarification about the label "List" helps you. Instead of labeling it "List", you could call it something else... it is just a header cell.


Hope that helps.


-Sajan.
 
Sajan,


Below is a link to the file that I uploaded. Please take a look at it when you get a chance. I also included a sheet which has details on what I am trying to achieve.


https://skydrive.live.com/redir?resid=3ED090AD082269C9!105&authkey=!AAtUjYEyhX94Y40


Thanks again for your time and patience.
 
Hi Abhishek ,


Is this what you are looking for ?


=IF(ROW($C9)-ROW($C$9)+1<=$K$6,INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF($L$9:$W$31>0, ROW($L$9:$W$31)+COLUMN($L$9:$W$31)%%%),ROW(A1)),"#.000000"), ".", "C"),FALSE),IF(ROW($C9)-ROW($C$9)+1<=$K$6+$Y$6,INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF($Z$9:$AK$31>0, ROW($Z$9:$AK$31)+COLUMN($Z$9:$AK$31)%%%),ROW($C9)-ROW($C$9)+1-$K$6),"#.000000"), ".", "C"),FALSE),IF(ROW($C9)-ROW($C$9)+1<=$K$6+$Y$6+$AM$6,INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF($AN$9:$AY$31>0, ROW($AN$9:$AY$31)+COLUMN($AN$9:$AY$31)%%%),ROW($C9)-ROW($C$9)+1-$K$6-$Y$6),"#.000000"), ".", "C"),FALSE),IF(ROW($C9)-ROW($C$9)+1<=$K$6+$Y$6+$AM$6+$BA$6,INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF($BB$9:$BM$31>0, ROW($BB$9:$BM$31)+COLUMN($BB$9:$BM$31)%%%),ROW($C9)-ROW($C$9)+1-$K$6-$Y$6-$AM$6),"#.000000"), ".", "C"),FALSE)))))


This is to be entered as an array formula , using CTRL SHIFT ENTER. Enter this in D9 and copy down.


If it is so , please acknowledge.


And in future , when you upload a file , please ensure CALCULATION is set to AUTOMATIC , otherwise , please mention this clearly in your post.


Narayan
 
Hi Abhishek,

I have uploaded a modified workbook at the following location:

http://speedy.sh/hde7P/Test.xlsx


Some comments:

1. In the "Data" tab, each data table has been given a name: List1, List2, List3, List4


2. Then helper columns are used (on the Results tab) to extract the unique items from each list. This combined list is named "List".


For the above step #2, a formula similar to the one below was used for List1, List2, List3 and List4. Formula for List1 is shown:

=IFERROR(INDEX(List1, MATCH(1,(COUNTIF(AB$3:AB3, INDEX(List1, 0, MIN(IF((COUNTIF(AB$3:AB3,List1)=0)*(List1<>""), COLUMN(List1)-MIN(COLUMN(List1))+1))))=0)*(INDEX(List1, 0, MIN(IF((COUNTIF(AB$3:AB3,List1)=0)*(List1<>""), COLUMN(List1)-MIN(COLUMN(List1))+1)))<>"" ),0), MIN(IF((COUNTIF(AB$3:AB3,List1)=0)*(List1<>""), COLUMN(List1)-MIN(COLUMN(List1))+1))),"")


entered with Ctrl + Shift + Enter


3. On the Results tab, the unique list and associated dates are created from the "List", and matching them against List1, List2, List3 and List4 to determine the associated dates.

The unique list is named "UniqueList"

The dates range is named "UniqueListDates"


For step #3, the UniqueList was created using the following formula:

=INDEX(List, MATCH(1,(COUNTIF(B$4:B4, INDEX(List, 0, MIN(IF((COUNTIF(B$4:B4,List)=0)*(List<>""), COLUMN(List)-MIN(COLUMN(List))+1))))=0)*(INDEX(List, 0, MIN(IF((COUNTIF(B$4:B4,List)=0)*(List<>""), COLUMN(List)-MIN(COLUMN(List))+1)))<>"" ),0), MIN(IF((COUNTIF(B$4:B4,List)=0)*(List<>""), COLUMN(List)-MIN(COLUMN(List))+1)))

entered with Ctrl + Shift + Enter

(You will note that this is similar to the formula from Step 2. It just references a different list.)


The UniqueListDates were determined using the following formula, for List1, List2, List3 and List4. Formula for List1 is shown:

=IFERROR(INDEX(Headings, SMALL(IF(COUNTIF(B5,List1), COLUMN(List1)-MIN(COLUMN(List1))+1),1)),"")

entered with Ctrl + Shift + Enter


4. You also asked if it would be possible to re-create the view on the "Data" tab (i.e. Pivot Table view) from the UniqueList and UniqueListDates. The only problem with that is that once the unique list and dates are extracted, we no longer know which list it came from. Also, the same row in the Pivot Table view could be from multiple lists.

So I have created the next best thing: The Pivot Table View showing all of the unique list items mapped to the dates.


The headings were determined using the following formula:

=INDEX(Headings,COLUMN(A1))


The list values were determined using the following formula:

=IFERROR(INDEX(UniqueList, SMALL(IF(MMULT(COUNTIF(J$11,UniqueListDates), TRANSPOSE(COLUMN(UniqueListDates)^0))>0, ROW(UniqueList)-MIN(ROW(UniqueList))+1), ROW(A1))),"")

entered with Ctrl + Shift + Enter


Cheers,

Sajan.
 
@ Narayan - Thank you for your input. Sajan updated the file I uploaded So did not get a chance to try your formula.


@ Sajan - That was exactly what I was looking for. Thanks for all you help. I really appreciate it.
 
@abhi2611: Thanks for the feedback. Glad to hear that the solution matched your needs. Come back anytime.


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