Macro to insert rows for missing data

[jebucher]

Hello,

I am formating hourly weather data and I need to code a macro in order to insert rows where there is missing data. I also need to delete rows where there is multiple weather observations per hour. In the end, there should be exactly one row for every hour of every day in the year, or (24*365) rows total. Any help with this would be wonderful, I have 140 files to format and without the macro it will take weeks.

I have one of the files attached for reference.


Thanks,

JB

[rylo]

Hi

Here's some of it. Should put in all the additional rows. It will take a while to run, but it does give you status in the status bar. See if it does give you all the extra bits you want, then I'll look at getting unique output.

Oh, and before you start, select columns A:D and make sure they are formatted as TEXT.

Please Login or Register  to view this content.

rylo

[rylo]

Hi

Regarding the multiple entries for the hour, which one(s) are to be deleted? Or should they all be deleted, and a blank row put in as a placeholder?

rylo

[jebucher]

Hi,

Regarding the multiple entries per hour, the one that has no temperature observation should be deleted. If both have a temperature observation, it doesn't matter which one is deleted. The blank row as a placeholder is only for missing data.

Thanks much,
JB

[rylo]

Hi

Slight problem with the sub in #2 so try this one along with the deletion of a duplicate item.

Please Login or Register  to view this content.

rylo