Data reorganize formula? Perhaps VBA?

[Maggie Barr]

Hello, and thank you for your time in advance if you can help.

I am working on a PC in excel 2013. I have a table of breeding bird data with codes that I need to create a summary table from. I am unsure of the most efficient way to do this and have spent many hours adjusting data, trying formulas, and using pivot tables to see what I can do, to no avail as of yet. I think that perhaps a formula would be the best way, but I am unable to figure out the extent of the commands needed. I have included a link to the data set below which has the blank tables to the right that I need to populate with the data summary. In short, I need to take data for each of three categories Confirmed, Probable, and Possible, and provide the data for each from 1-3 in order that they are in the column header. Below is an example of the data for just the Confirmed, but please see the link to the data set to get a better idea. So, for instance, in the Alder Flycatcher & American Bittern below, for the Confirmed breeders portion of the data, anything with a 1 needs to go in the 1 category with the codes "column headers" in order as they fall, separated by a ",". See output example below this.

DATA FORMAT
[TABLE="width: 513"]
<tbody>[TR]
[TD]Species[/TD]
[TD="colspan: 12"]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NY[/TD]
[TD]NE[/TD]
[TD]FS[/TD]
[TD]FY[/TD]
[TD]CF[/TD]
[TD]FL[/TD]
[TD]ON[/TD]
[TD]UN[/TD]
[TD]DD[/TD]
[TD]NB[/TD]
[TD]CN[/TD]
[TD]PE[/TD]
[/TR]
[TR]
[TD]Alder Flycatcher[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]American Bittern*[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

AND turn it into a summary like this for output:

[TABLE="width: 519"]
<tbody>[TR]
[TD="colspan: 3"][TABLE="width: 744"]
<tbody>[TR]
[TD][TABLE="width: 744"]
<tbody>[TR]
[TD]Species[/TD]
[TD="colspan: 3"]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Alder Flycatcher[/TD]
[TD]NY, FS, FY, CF, ON, NB, CN[/TD]
[TD]NE, FL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]American Bittern*[/TD]
[TD]ON[/TD]
[TD]NY, NE, FL[/TD]
[TD]FY, NB, CN, PE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="colspan: 3"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]https://www.dropbox.com/s/xc3avm3c02cwnri/Template for Mr. Excel question.xlsx?dl=0

I would appreciate ANY advice or assistance on this.
Thank you for taking the time to read this post and assisting if at all possible.
Sincerely,
Maggie [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]