Converting a long line of monthly data in years.

So I numerous list of data that are dated monthly in Col A and then Col B would the be associated data for that info in Col B. There would sometimes be more data in C,D,E,F and so on but they are always going to be on the same line. for that month in Col A.

Some data is monthly for 5 years. Some for 10, and even so for 50 years.

What i WANT to be able to do is have some kind of auto converter that takes that data and breaks it down into months by YEAR. So if there are 10 years... there would be 10 lines from Jan through December.

Ideally it would be cool to say just show me the last 2, or 3, or 4 years and then do an average of whatever is left. that would be really cool.

I have looked online to see how to do something like this and cant find a thing.

Any ideas ???

So this would be a mock up of what I would like to be able to do.
You dump your data in A:B
F1 is some calculation that say how many years of data years of data You have in A:B

You can see the flow in I5, I6, and I7 as to what I had to do to force the correct info there. I assume that would be some kind of macro?
F2 is a USER INPUT where you would say how many individual years you want to show. In this case I said 2. That would mean that it would show the LAST two years of data as a line and then do an average by month for the remaining years (in this case 3 years). There might be a situation where I have 20 years of data and I would say 5 in F2 where is would show 6 lines on a graph, 2019, 2018, 2017, 2016, and the average of the rest.
Does this help??

Sure that could work if i was the person doing the entry but i will not.

The program will be used by people who barley know how to drop in the data let alone create a pivot table from it. It would need to be a macro.

If anyone could help with that, it would be great.

Perhaps i should move this post to the Macro VBA section.

Does anyone know how to do that?

You could put this formula in cell I6:

=IFERROR(INDEX($B:$B,MATCH(DATE($H6,COLUMNS($I:I),1),$A:$A,0)),"")

then copy across and down as required.

Hope this helps.

Pete

FlameRetired.... can you please move this to the VBA forum for me.....

I know this thread got a little long as I had placed it in the wrong forum. But could anyone give me any assistance now that it has been moved to the correct one? I have included an attachment and explained what i am trying to accomplish if anyone could please help me.

Thank you

This is amazing!!!!!!!