Converting Checklists to Line-by-Line Records

[ursus_arctos]

Can someone suggest a method of converting checklists into line-by-line records, using either MS Excel or LibreOffice? I have many years of checklists for one location which I would like to reformat. To illustrate: the data I have is formatted like this:

The format I want is like this:

The situation is, it is far more efficient for me to record all this data in a checklist format, because it all relates to the same location. Otherwise, the (identical) column data - like latitude, longitude, location description, etc. - has to be recopied over and over again. The resulting table is massive. However, the data must be in a one-record-per-line format to upload to any of the software or platforms I use, including iNaturalist and Scythebill.

I suspect there is no non-labor-intensive method of converting data from a one-to-many relationship to a one-to-one relationship, or I would have found it by now. I would really like to avoid re-keying over a decade’s worth of checklists from scratch if I can.

Appreciate your feedback, folks, thank you!

[bouteloua]

There are probably more streamlined or better ways to do this that I’d like to hear about, but this is how I do it in Excel.

Given this data table:

Type alt+d+p
and this should pop up:

Select Multiple consolidation ranges, Next

Create a single page field for me, Next

Select the range as shown, Next

New worksheet, Finish

Uncheck Row and Column in the pivot table fields

then double click on the number under Count of Value.

Should end up looking like this:

From there you can copy over your data, use Data>Text to Columns to separate back out your month and date, and add any other fields you wanted to include:

[ursus_arctos]

Thank you very much, @bouteloua! That’s genius. I’ve been experimenting with PivotTables for ages, trying to find a way to rearrange the data, but I never worked out the method you’ve just shown.

[ahospers]

I have only seen it, never tried myself https://radacad.com/pivot-and-unpivot-with-power-bi