Merging excel files + renaming them with folder name + restructuring them

Hello

Im struggling a bit with my PhD data, and I cant find the solution (except doing it manually):

I've got 350 excel files, each one in an individual folder (each folder contains pictures that I measured, and a 'Results.xlsx' file with the measurements).

The structure of the excel file is always the same, like in the picture attached, except that the number of rows can vary (but it's always a multiple of 3! you'll see it's important...).

What I would like =

Have one single excel file (no big surprise), with all the files merged, following each other, with at the beginning of each 'file' the name of the folder the excel file was in. Then the final outcome i want is: keeping only the last column ('length'), and changing it. My data are basically each time 3 measurements of 3 individuals (a trial) within a population. I want the 3 individuals (male 1, male 2, female) of each trial on the same line, but they dont follow each other:

-> if there are 15 raws, the first 5 are the males 1, the next 5 are the males 2, and the last 5 are the females (if 12 raws: 4 males 1 then 4 males 2 then 4 females).


So my question is: is it possible?!

Im working on Mac (using Excel with Parallel), but I also have a PC. I was thinking about using Automator on Mac but I have no idea if it can do that (and I guess it doesnt).

Could you help me please? At least by saying if it's doable or not? Im not even sure it's the appropriate place to ask this, I should probably separate my problems...

Thanks a lot,

Al

No suggestion...?

Heeeelp!!