Hello i am looking for a solution in Excel that will auto create rows based on info within certain cells in the row. So i have en ecological dataset with waterbird individuals but i need to assign 8 different rows for waterbird presence based on info from 1 row. I have attached a sample spreadsheet that shows how my original dataset looks like and the end product of what i need my spreadsheet to look like.
Basically i have a Raw_Data sheet which i need to be formatted into an Intended_Output sheet. I have data from 10 individual birds (Bird 1 - Bird 10) and i have noted their presence or absence during times of the day, tidal patterns etc. What i need is to build include presence absence rows for each individual for each calendar day regardless if they were presence or absent. You can see how the output would look like in the Intended_Output sheet. So for example if BIRD 1 is present during the night on the 17th of June and BIRD 3 is present both in the day and night then BIRD 1 would have a presence of 1 in the Night and 0 in the Day whilst BIRD 3 would have presence of 1 during both day and night and all other BIRD would have presence of 0 in both Day and Night. You can see from the Raw_Data sheet that only presence or absence is recorded for an individual when there has been a presence. What i need is to show also the absence for each individual on each day. I guess a formula that builds additional rows based on information in certain cells is the way forward.
I am wondering if you could build a formula to automate this as i have 20,000 rows to format.