How to re-format this data?

[Mark53]

Hello, people, and Happy 2023 !
In Excel 2007, I'm trying to make a calendar, with Special days.
From the source:
http://holidayinsights.com/moreholidays/index.htm
I can get the data formatted like in column A.
How can I (not manually) convert that to what's shown in columns C and D?
A search did find some more easily formatted data at
https://blankcalendarpages.com/holidays/fun
but there are only 425 days mentioned for 2023 there,
while the old source has just over 1,000 days.
?
Thank you.
Mark53

[ORoos]

Hi Mark53,

I have added a quick macro into your file.
Just click the blue button and it should format the data as per your sample.

Notes:
Source Data is in column A, no header rows. Output will be in columns C & D.


PS: I noticed that your day/month dates in column A are for 2022, not 2023.

Please Login or Register  to view this content.

[thomglea]

Another method with one fewer loop:

Please Login or Register  to view this content.

[Pete_UK]

@Mark,

the dates that you have in column A all refer to 2022, except for the date in cell A15 which refers to 2023.

Pete

[Mark53]

MAGNIFICENT ! It works like a charm.
I used the ThomGlea macro, because...
if I hit the "Go" button more than once using the Oroos macro,
it added more (incorrect) rows to the output data.

And yes, the test file had dates from a year ago. It's now fixed.

Thank you very much, people.