Formulas to for table data compile/crunching

[hcyeap]

Hi all,

Happy New Year!

I have a data in tabular format and am having difficulties in compiling them.
Refering to the attached, my data is consist of 3 parts: date, code and time in a day (48 * 15 mins inetrval).

My intended result is to display the total of each code for each interval (if any) for each date.

For example, the codes I have AUTCHI and BREAK. I would like to show the total of these codes at these intervals 1500, 1515, 1530, 1545, maybe 3,3,3,4.
Refering to the attached, the header of the code columm STATE_CODE_1 is corressponding to the time column STATE_TIME_1, from 1 to 10.
I am having problem to match each STATE_CODE_x to each STATE_TIME_x, not to mention I have another condition which is the date.

It's a bit difficult to explain, hence I have drafted my intended table on the second sheet.

Appreciate if anyone is able to assist. Please let me know if I can explain more clearly.

Thank you.

[Richard Buttrey]

Hi,

Are you able to change the raw data layout so that you have columns for

Date
State (Values State1, State2..etc)
Code (Values SCHOFF, AUTCHI...etc
Time

i.e. you'll have a table of approx 4400 rows.

If you can then the task will be simplified immensely since a straightforward pivot table could analyse it.
Even if you can't obtain the data in that format I suggest that a pre-processing macro to put the data into that layout would be the best way forward.

[hcyeap]

Hi Richard,

Thank you for your reply.

I can't change the raw data layout, so I will go ahead with your second suggestion.
Can you further elaborate on how should I change the layout? (I might have problem on the macro part)
For example. are you suggesting that to compile all states into 1 column?

Thank you!

[Richard Buttrey]

Hi,

See attached

The Create Normalised data buttoin runs a macro that reorders your data in the 4 column list. Your summary table can then use that. Or indeed you may find the Pivot table easier to use and more useful.

[hcyeap]

Hi Ricahrd,

Thank you very much!
That helps me a lot.

Thank you!!
Really appreciate.

[Richard Buttrey]

Hi,

Glad to have helped and thanks for the rep.