Adding blank rows to satisfy hourly data format

[tpark85]

I am compiling hourly data but one of my sources only collects it for the hours of 3 to 20. I need it to include the hours of 0 to 23. (Rows for the hours of 0, 1, 2, 21, 22, 23 would need to be added just with nil in the data column) How do I get it into this format without having to manually add extra rows or copy each group into a pre-existing sheet?

Here is an example:

Correct format:
DATA YEAR MONTH DAY HOUR
555 2014 1 1 0
555 2014 1 1 1
555 2014 1 1 2
555 2014 1 1 3
555 2014 1 1 4
555 2014 1 1 5
555 2014 1 1 6
555 2014 1 1 7
555 2014 1 1 8
555 2014 1 1 9
555 2014 1 1 10
555 2014 1 1 11
555 2014 1 1 12
555 2014 1 1 13
555 2014 1 1 14
555 2014 1 1 15
555 2014 1 1 16
555 2014 1 1 17
555 2014 1 1 18
555 2014 1 1 19
555 2014 1 1 20
555 2014 1 1 21
555 2014 1 1 22
555 2014 1 1 23

CURRENT FORMAT:

DATAYEAR MONTH DAY HOUR
555 2014 1 1 3
555 2014 1 1 4
555 2014 1 1 5
555 2014 1 1 6
555 2014 1 1 7
555 2014 1 1 8
555 2014 1 1 9
555 2014 1 1 10
555 2014 1 1 11
555 2014 1 1 12
555 2014 1 1 13
555 2014 1 1 14
555 2014 1 1 15
555 2014 1 1 16
555 2014 1 1 17
555 2014 1 1 18
555 2014 1 1 19
555 2014 1 1 20

[FDibbins]

Hi, welcome to the forum

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[tpark85]

There are two tabs in this attachment. The one stating 'help' is the one with only hours 3 to 20. The other tab is the version I need with all hours 0 through 23

[FDibbins]

where does this data come from, and how are you pulling it in?

[tpark85]

Environmental data. I can't change the accessible format or the way I source it.

[Beamernsw]

I think I have it sorted, but like FDibbins said "where does this data come from, and how are you pulling it in?"

The Help sheet has the rows missing for certain hours, and Correct has all hours.
But Help seems to have missing data (as in Quality and Version is the same).
Also both sheets have different columns after the hour column.

If you could supply a data sheet (only need a few lines) that shows exactly how the data is once imported into excel, that would help immensely.

[tpark85]

Excel Help.xlsx

Hello Beamernsw. (new attachment above)

Thanks so much for your help.

Yes, what I need is for the data columns in the 'help' sheet to be aligned with the hours in the 'correct' sheet. Basically, the missing hours would just be blank lines and I would have both aligned. In the end, what I want is to have a 'master sheet' with all my variables side-by-side in columns in the according hours. So, columns F, G, and H in the 'help' sheet would be in the appropriate rows of the sheet with all hours. I have attached an updated sheet as well.

The only difference between this sheet and the original file is spacing. I could, however, provide the file that reports the 'date' as one column in the format year, month, day, hour as one number e.g., 2014082305

[tpark85]

This file may help as well.

What I need is for the data in the 'limited hours' tab to be aligned with the 'date' column in the 'full hours' tab. Of course doing this manually by adding rows and then copy-paste would be quite difficult.

[tpark85]

Am I correct to assume that the vlookup function can be used to create a new table aligned like I want? It's not working for me but it seems that it would be a way to do this??

[Beamernsw]

I can do any of that.....I'm just not sure which sheet is your original data...
The Level of quality and version is different between sheets.
The Help sheet has a minutes column but only partial hours
The Correct sheet has a data point and percent column as well as all hours listed
The limited hours sheet has date/time as 2014010103 format, which is fine.

All I need to know is where this master sheet will grab all these different columns of data from.
Will they be in 2 sheet from 1 book and 1 sheet from another book like now?

Sorry to sound confusing....I just don't know which sheet I'm working from to make this master sheet.
If all your data comes in on one sheet, could you please just submit the sheet with only a few rows on it?

[tpark85]

Yes, thank you very much!

Hopefully I can clarify since I have provided two files:

The sheet above: 'excel help 2':

The tab 'limited hours' is the original file. The tab 'full hours' shows the dates I need the data from 'limited hours' to fit into.

The data are on several sheets so once I can get the 'limited hours' to fit the 'full hours' format I will be able to finish the formatting of my new file I am creating.

Thanks again, I appreciate your help!

[Beamernsw]

Still not totally clear
But this might be all you need to get you going.

All data on Beamer's sheet is coming from the LIMITED HOURS sheet except Data Point, Percent, and eor which are grabbed from the Correct sheet.

The first file was too large (2.50mb)
So I deleted the sheets I wasn't using. Didn't help a lot.
I had the formulas copied down 10,000 lines.....that's now 100 lines

[tpark85]

Amazing job! I really appreciate your help, I should be good to go now!!

Happy new year Beamer!!