Consolidate values in multiple columns for Pivot Table

...it doesn't have to be a pivot table, if fact it's sort of a one time calculation... a pivot seemed to be the most automated way - really that's what I want; a more automated way to get this data. Thanks again.

Hi queasyfish,

You posed an interesting question. Especially with 30,000 records. It would be an overload for array formulae, I think.

Here is a solution utilising a query table. I gave the first data range, including headers, defined name "MD". And created another range "Times" with values 1 to 24.

Then via menu Data, Import External Data, New Database Query, Excel files, selected the file and then some fields from MD and the Times field. OK to the message about the wizard can't continue. When reaching MS Query hit the SQL icon and edit the SQL to become similar to

Enter that and then hit the "door" icon on the LHS of the SQL button to return the result to Excel. (If it hangs up, I find a quick CTRL-ALT-DELETE is enough to "wake it up".)

HTH
Fazza

Thanks Fazza, I really appreciate your help;

I'm stuck at the SQL and have a few questions:

Overall it looks like you are trying to pull in and work with the "Times" range as one unit but SQL is erroring out.

1. In the From statement it does not recognize the ".TL TL" range but will accept ".Times Times".
2. (but then) I cannot find the right language in the Where statement to make it use the whole range of columns, it gives me a "not valid field" error on ".TL TL", "Times.TL", "TL.Times", "Times.Times" or "Times.*".
3. If I use "Times.F1" (one of the named columns in the Times range) it accepts it but this will not be the right data.
4. Even when I can get it to pull the range (table) and recognize the "Times.F1" field, I still get an error message saying that the "SQL query can't be represented graphically. Continue anyway?" - I just OK it. - this shouldn't be a showstopper should it?

Additional thought: The formula seems to be comparing the start and stop times with greater than/less than, but these time fields cross the midnight hour frequently and the numbers go from 23 to 0.

Thanks again for any time you can spend on this.
John

Hi John,

Apologies, my description was inadequate for you earlier. I did not describe that the table I called "TL" had a header field "Times". When, the SQL refers to TL.Times it is referring to field Times in table TL.

I numbered my hours from 1 to 24. This could equally be 0 to 23. To be more specific, on a new worksheet, cell A1 entry "Times" and below that in successive cells from A2 to A25 numbers 1, 2, 3, 4, 5, etc through to 24. This whole range - header and data - I called TL.

If your data goes beyond midnight this creates a totally new challenge that I did not foresee in the original definition. How then will you know how many days there are? Even in your sample data, what if there is a start of 10am and an end of 2pm? It could be 4 hours later, 28 hours, 52 hours, etc, etc. I think it is important that you reconsider your whole data arrangement or provide a specification of how to interpret the times.

Your points 1 to 3 should now be fixed if your range is as I just described. Your point 4, yes click OK. Apologies again, I hope it is OK this time. Regards, Fazza

John,

FYI, some other posts related to query tables that might be helpful. Regards, Fazza

Count example using multiple criteria (4 fields)

JOIN of two tables to find common records

Summation consolidation of list to new list (not pivot table)

Combine data from multiple sources to one table, using "UNION ALL"

Parameterised query to grab two lots of records joined by UNION

Extracting records particular records using JOIN, MAX and SUM

TRANSFORM - sample of a cross tab query

Thanks Fazza;

...off to a busy start this week. I will try this and check those articles and reply later in the week.

Thanks!