Cleaning data with two fields mixed in the same column? 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 &quot;Site Names&quot;, data is Month-level.
The issue is that the &quot;Site Names&quot; 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. &quot;Jul&quot; would be 2018 the first time it appears, 2019 the second time, 2020 the third time. Any help/tips would be greatly appreciated.
I suggest adding a company name for each corresponding month. See the attached picture.
The formula for the first column determined if it is for a month or for the company name. Assuming that all your months are in the three-letter format and there is no company named 'May' or 'Sep', the formula for the cell B2 would be
=SUMPRODUCT(--(A2={&quot;Jan&quot;,&quot;Feb&quot;,&quot;Mar&quot;,&quot;Apr&quot;,&quot;May&quot;,&quot;Jun&quot;,&quot;Jul&quot;,&quot;Aug&quot;,&quot;Sep&quot;,&quot;Oct&quot;,&quot;Nov&quot;,&quot;Dec&quot;}))&gt;0

The formula for C2 is
=INDEX($A$2:$A$13,MATCH(2,1 /($B$2:B2=FALSE))+1)

Please refer to this page (https://www.get-digital-help.com/index-match-last-value/) for explanations for this formula.
Finally, you can filter the third column in my example to keep months for the specific company only. They will be in the order needed.