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