Excel rearrange data from cloumns into rows [Closed]

Report
Posts
3
Registration date
Thursday December 10, 2009
Status
Member
Last seen
December 12, 2009
-
Posts
3
Registration date
Thursday December 10, 2009
Status
Member
Last seen
December 12, 2009
-
Hello,

Sorry for the vague subject, I'm not really sure how to explain my problem in just a couple words. So here it goes.

I have a dataset that looks something like this:

Plot    Species          Cover
1           A              1
1           B              2
1           C              1
2           C              4
2           D              5
3           A              6 
3           B              6
3           C              2
3           D              1
3           E              3


With over 300 plots, over 150 species and 6 covers.

I would like to rearrange the data to look like this:

            
            A    B     C    D    E
1           1    2     0    0    0 
2           0    0     4    5    0 
3           6    6     2    1    3


With species as columns, plots as rows and covers as values.

No species occurs more than once i a single plot and not all plots have the same number of species.

I have several datasets like this, so doing it manually for all datasets would be quite tedious. This seems like it would be a common issue, but I haven't been able to find an answer. I thought pivot tables would work, but there is no way to insert raw values into the table (is there?), just descriptive statistics about the values. I've tried nested lookup's, if's, etc.

It isn't necessary that the data stay in excel, so if there is another program/ programming language that would do this task, I'd be willing to try that. Any advise would be greatly appreciated!

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
find out unique values of plots and species by
using advance filter
see help undergetting unique values under excel help
if there is porblem in getting unique plots and species post back and try google search.

in your case the unique plots are 1 2 3 and unique species are A B C D E

below the data choose a cell e.g. A13 onwards down and to right as follows

Plot A B C D E
1
2
3

in B14 copy this formula

=IF(ISNA(INDEX($C$2:$C$11,MATCH(1,($A$2:$A$11=$A14)*($B$2:$B$11=B$13),0),1)),0,INDEX($C$2:$C$11,MATCH(1,($A$2:$A$11=$A14)*($B$2:$B$11=B$13),0),1))

invoke this formla with CONTROL+SHIFT+ENTER

copy B14 down and to the right

you get this result from A13 down and right=A13 will be blank )

A B C D E
1 1 2 1 0 0
2 0 0 4 5 0
3 6 6 2 1 3

BY THE BY 1C is 1 and not 0
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
3
Registration date
Thursday December 10, 2009
Status
Member
Last seen
December 12, 2009
2
Thanks for the help! I've tried the formula on the data I had in my original post to test, but I currently get a #VALUE! result. I see where the formula is going, so I am trying to troubleshoot it now. If you have any ideas to fix the current result, I'd be glad to try them.

As a reference for anyone else using the formula, in the first call for $B$11 there is a (-) sign in the above formula that needs to be removed (I think) and, as I copied it, another somehow popped up after the last call for $A$14.

Thanks again and I will post again once I figure out how to fix the current #VALUE! result.
Posts
3
Registration date
Thursday December 10, 2009
Status
Member
Last seen
December 12, 2009
2
I got it! Sorry, the Ctrl+Shift+Enter was new to me, but after poking around on the web about #VALUE! errors, it works! Thanks so much for your help. Even with a few hours of "troubleshooting" (i.e. not knowing what I was doing), this will be a huge timesaver!

Thanks again!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!