how to transpose multiple rows into one column without 0 in excel [SOLVED]
- Thread starter abhi2611
- Start date
SirJB7
Excel Rōnin
Hi, abhi2611!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about questions in general...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
And about this question in particular...
Would you mind telling us how did you achieved your actual partial solution?
Regards!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about questions in general...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
And about this question in particular...
Would you mind telling us how did you achieved your actual partial solution?
Regards!
I thought this looked familiar. =)
Reviewing the formula I came up with, and Chip Pearsons site:
http://www.cpearson.com/excel/MatrixToVector.aspx
Both methods involve a simple translation of data. The trick with this setup is not wanting the 0's.
@abhi,
Would a macro solution be possible? Or are you wanting strictly a formula one?
Reviewing the formula I came up with, and Chip Pearsons site:
http://www.cpearson.com/excel/MatrixToVector.aspx
Both methods involve a simple translation of data. The trick with this setup is not wanting the 0's.
@abhi,
Would a macro solution be possible? Or are you wanting strictly a formula one?
Sajan
Excel Ninja
Hi,
Here is one approach to get rid of the zeros:
=INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(List>0, ROW(List)+COLUMN(List)%), ROW(A1)),"#.00"), ".", "C"),FALSE)
enter with Ctrl + Shift + Enter instead of Enter
Copy down until you get an error. To get rid of errors, wrap in IFERROR()
Here List refers to your data range. The above would ignore zeros and blank cells.
Cheers,
Sajan.
Here is one approach to get rid of the zeros:
=INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(List>0, ROW(List)+COLUMN(List)%), ROW(A1)),"#.00"), ".", "C"),FALSE)
enter with Ctrl + Shift + Enter instead of Enter
Copy down until you get an error. To get rid of errors, wrap in IFERROR()
Here List refers to your data range. The above would ignore zeros and blank cells.
Cheers,
Sajan.
jeffreyweir
Active Member
Sajan, very very clever. May I ask how you came up with that? I've never seen that R1C1 hack anywhere before.
Sam Mathai Chacko
Active Member
Excellent one Sajan. Just for the purists, I'd say it's safer to have a larger divisor to get the column index.
=INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(List>0, ROW(List)+COLUMN(List)%%%), ROW(DY1)),"#.000000"), ".", "C"),FALSE)
By the way, can someone tell me how to wrap codes in this forum?
=INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(List>0, ROW(List)+COLUMN(List)%%%), ROW(DY1)),"#.000000"), ".", "C"),FALSE)
By the way, can someone tell me how to wrap codes in this forum?
NARAYANK991
Excel Ninja
Hi Chacko ,
Use the backticks , the symbol which is on the tilde key , at the beginning and at the end , as in the following example :
[pre]
[/pre]
Enclosing the code within these backticks preserves the indenting.
Narayan
Use the backticks , the symbol which is on the tilde key , at the beginning and at the end , as in the following example :
[pre]
Code:
Sub test_case()
Dim i as Integer
End SubEnclosing the code within these backticks preserves the indenting.
Narayan
Sajan
Excel Ninja
Thanks Jeffrey and Sam. I appreciate the feedback.
This is a technique I have used a couple of times on this forum already. In terms of how I came with it... it seemed to be the shortest possible route to get both row and column handled in a single operation!
Thanks Sam for the tweaks. Since most of the time folks don't have more than 99 columns, I thought it would be sufficient to use one divisor. However, the additional divisors make it more generic, along with using DY1 instead of A1.
Since both of you are celebrities in the Excel formula forums, I look forward to learning from both of you!
Regards,
Sajan.
This is a technique I have used a couple of times on this forum already. In terms of how I came with it... it seemed to be the shortest possible route to get both row and column handled in a single operation!
Thanks Sam for the tweaks. Since most of the time folks don't have more than 99 columns, I thought it would be sufficient to use one divisor. However, the additional divisors make it more generic, along with using DY1 instead of A1.
Since both of you are celebrities in the Excel formula forums, I look forward to learning from both of you!
Regards,
Sajan.
Sam Mathai Chacko
Active Member
I must confess, the DY1 was just part of me testing the tweaked formula. To be blunt, if I had paid more attention while posting, I'd have used A1. I really don't see much advantage in using DY1 over A1. In fact, considering that we are looking at making the formula work even if there were 99999 columns, it makes the formula less generic than using A1.
If one had to use DY though (or any other column other than A, the very first column), I'd think making the column absolute would be better, as in $DY.
By the way, that celebrity tag sounded a bit of an overwhelming one. I'd say I just get by. Thank you though. I look forward to learning from the likes of you, rather.
If one had to use DY though (or any other column other than A, the very first column), I'd think making the column absolute would be better, as in $DY.
By the way, that celebrity tag sounded a bit of an overwhelming one. I'd say I just get by. Thank you though. I look forward to learning from the likes of you, rather.
jeffreyweir
Active Member
Formula celebrities...teheeheehee.
Sajan - did you happen to do any of the Excel Hero Academy courses? There were some good challenges posted in the student forums, where I learned as much as (if not more than) I did on the academy itself.
This forum reminds me of that, because again you learn a lot by trying to problem-solve issues that you wouldn't otherwise be exposed to, plus get to see other people's approaches. Free university.
Sajan - did you happen to do any of the Excel Hero Academy courses? There were some good challenges posted in the student forums, where I learned as much as (if not more than) I did on the academy itself.
This forum reminds me of that, because again you learn a lot by trying to problem-solve issues that you wouldn't otherwise be exposed to, plus get to see other people's approaches. Free university.
Sajan
Excel Ninja
Sam, I thought you changed the A1 reference to avoid the error caused by deletion of column A, while column DY or ZZ would be "so out of the way" that it might generally be ignored by the typical user. Most of the time, however, I throw caution to the wind and ignore that risk! 
Jeffrey, No, I have not taken any of the Excel Hero Academy courses, even though I have learned from a few of the writeups from Daniel Ferry.
I agree with your approach of learning by solving problems posed by others. Quite a few others on this forum share that sentiment. One "guiding principle" on this forum is to attempt a solution even if it is not the best. For one thing, that might reveal a unique way of solving something since we come with different backgrounds and perspectives!
By the way, if you care to point to some of those challenges from Excel Hero Academy student forums, I am sure quite a few here would enjoy them. (Assuming they are open to non-students.)
Jeffrey, No, I have not taken any of the Excel Hero Academy courses, even though I have learned from a few of the writeups from Daniel Ferry.
I agree with your approach of learning by solving problems posed by others. Quite a few others on this forum share that sentiment. One "guiding principle" on this forum is to attempt a solution even if it is not the best. For one thing, that might reveal a unique way of solving something since we come with different backgrounds and perspectives!
By the way, if you care to point to some of those challenges from Excel Hero Academy student forums, I am sure quite a few here would enjoy them. (Assuming they are open to non-students.)
Sam Mathai Chacko
Active Member
That was a nice thought. I probably would have used Row(1:1) to counter that, except, I fear it may be an overkill of resources.
But overall, I guess it's good for posterity. Readers can get different views for the same objective, and the reasons behind it. Thanks again Sajan.
But overall, I guess it's good for posterity. Readers can get different views for the same objective, and the reasons behind it. Thanks again Sajan.
I really appreciate all you guys input. Thank you Sajan, the formula worked like a charm. The other issue i am running into is using he offset.
My current scenario:
Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec
A B C D E F H
I J k L M N
O p Q R S
Required Out Put:
A Jan
O Jan
B Feb
C Mar
K Apr
R Apr
The order doesn't matter but that is what I am trying to do.
Any help would be greatly appreciated.
Thank you.
My current scenario:
Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec
A B C D E F H
I J k L M N
O p Q R S
Required Out Put:
A Jan
O Jan
B Feb
C Mar
K Apr
R Apr
The order doesn't matter but that is what I am trying to do.
Any help would be greatly appreciated.
Thank you.
Sajan
Excel Ninja
Hello,
Glad the solution worked for you.
I am assuming your latest post is a new, unrelated question.
Can you describe your question further? Please explain why in your required output you have some letters skipped. Also, why is "K" associated with April when the original data shows it in March?
If you made up the data, please make up data that is representative of your problem.
Cheers,
Sajan.
Glad the solution worked for you.
I am assuming your latest post is a new, unrelated question.
Can you describe your question further? Please explain why in your required output you have some letters skipped. Also, why is "K" associated with April when the original data shows it in March?
If you made up the data, please make up data that is representative of your problem.
Cheers,
Sajan.
Sajan,
Sorry about the post. The formatting got messed up when I posted it on here. This actually related to the same post. What I have here is more like a schedule with a list of things happening in each month.
Ist row has the heading with Months.
2nd 3rd and 4th are just layered on top of each other in the order of happening.
[pre]
[/pre]
Here A and I happens in Jan. BFJ happens in Feb and so on.
What I am trying to get to is put them in columns as mentioned in my previous post.
Thank you for your time.
Formatting fixed by Luke M
Sorry about the post. The formatting got messed up when I posted it on here. This actually related to the same post. What I have here is more like a schedule with a list of things happening in each month.
Ist row has the heading with Months.
2nd 3rd and 4th are just layered on top of each other in the order of happening.
[pre]
Code:
Jan Feb Mar Apr May
A B C D E
F G H
I J K MHere A and I happens in Jan. BFJ happens in Feb and so on.
What I am trying to get to is put them in columns as mentioned in my previous post.
Thank you for your time.
Formatting fixed by Luke M
Sajan
Excel Ninja
Thanks Luke.
@abhi2611:
In your sample output, do the skipped letters have any significance? (i.e. D, E, F, G, H, etc.)
Also, why is the letter "I" associated with Jan and Feb, when in the data it is only in the Jan column?
Or, is your sample output not showing the full results for your sample data? That is fine too... but just wanted to confirm that you were not assuming some rules for creating the output that is not obvious from your post.
Cheers,
Sajan.
@abhi2611:
In your sample output, do the skipped letters have any significance? (i.e. D, E, F, G, H, etc.)
Also, why is the letter "I" associated with Jan and Feb, when in the data it is only in the Jan column?
Or, is your sample output not showing the full results for your sample data? That is fine too... but just wanted to confirm that you were not assuming some rules for creating the output that is not obvious from your post.
Cheers,
Sajan.
Sajan
Excel Ninja
Hello,
Keeping with the same approach as before, try the following formulas to get the letters and date headings.
For convenience, I have assumed that "List" refers to the alphabet data above, and "Headings" refers to the date headings.
To get the alphabets, use the following formula:
=INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(List<>"", ROW(List) + COLUMN(List)%), ROW(A1)), "#.00"), ".", "C"), FALSE)
enter with Ctrl + Shift + Enter, instead of just Enter
Copy down to additional rows until you get an error value.
Assuming that the alphabet list is starting in cell B9, put the following formula in the adjacent cell (C9):
=INDEX(Headings, MIN(IF(COUNTIF(B9, List)>0, COLUMN(List)))-MIN(COLUMN(Headings))+1)
enter with Ctrl + Shift + Enter, instead of Enter
Copy down to additional rows.
(Feel free to trap the errors using IFERROR() if desired.)
I got the following output for your sample data:
[pre]
[/pre]
Cheers,
Sajan.
Keeping with the same approach as before, try the following formulas to get the letters and date headings.
For convenience, I have assumed that "List" refers to the alphabet data above, and "Headings" refers to the date headings.
To get the alphabets, use the following formula:
=INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(List<>"", ROW(List) + COLUMN(List)%), ROW(A1)), "#.00"), ".", "C"), FALSE)
enter with Ctrl + Shift + Enter, instead of just Enter
Copy down to additional rows until you get an error value.
Assuming that the alphabet list is starting in cell B9, put the following formula in the adjacent cell (C9):
=INDEX(Headings, MIN(IF(COUNTIF(B9, List)>0, COLUMN(List)))-MIN(COLUMN(Headings))+1)
enter with Ctrl + Shift + Enter, instead of Enter
Copy down to additional rows.
(Feel free to trap the errors using IFERROR() if desired.)
I got the following output for your sample data:
[pre]
Code:
List Headings
A Jan
B Feb
C Mar
D Apr
E May
F Feb
H May
I Jan
J Feb
K Mar
M MayCheers,
Sajan.