I am working on a template for a dashboard that is giving me some serious trouble. The format the data comes in is highly unstructured. In the image below, circled items are "Site Names", data is Month-level.
The issue is that the "Site Names" are mixed in with the Month, with an inconsistent number of months for each site. Some sites will display one year worth of data, others will contain up to 3 years. There is no field indicating Year, only Month.
I have managed to extract a list of unique Site Names from this column, so I have those on their own. My issue now is that I have no idea how to match them to their corresponding Months. I was thinking through how to do so with an HLOOKUP, however after planning it out briefly I'm not sure this would work. The site names I would be using as a lookup are not all present in the top row. I am absolutely stumped on how to achieve this.
Provided this isn't asking too much in one question, I also am faced with the challenge of getting Excel to understand that the first instance of a month for each site is an older instance of a month. I.E. "Jul" would be 2018 the first time it appears, 2019 the second time, 2020 the third time. Any help/tips would be greatly appreciated.