Filter data by Columns (a,b,c etc) and mark the top 3 items for each filter.

[margetc]

Hi,
I need some help please to be able to quickly sort a table on "Date / Time" and (firstly) another column (F1). Then I need to have the T3Fn columns of F1 (for each unique Date / Time) filled with the percentage values for the Date/Time/F1 data set.
(See attached file).
It would be extremely useful if the unique "Date/Time/F1" and the new column "Date/Time/T3F1"set of data could be conditionally coloured with Red to Green scale.

After the Date/Time/F1 sort, I then need to sort table on Date/Time/F2.

Again, it would be extremely useful if the data could be conditionally coloured with Red to Green scale.

I need to repeat this for F3 and F4 also.

This takes too long to do manually and I've looked at trying Index/Match in formulas but can't get my head around it.

I tried recording macros but can't seem to get anything working since the ranges are dynamic owing to the filters.

I'm sure it just needs a VBA that loops on each filter but my VBA skills are minimal.

Any help would be gratefully received.

[dflak]

First of all, thanks for putting the data into an Excel Table.

I added a helper column to computer the Date/Time.

Then I built a Pivot table to get me a unique list of date times on sheet2. I added formulas to the right of these dates to compute the 1st, 2nd and 3rd place for that date time using MAX and LARGE in array formulas.

I overlaid the whole mess with a named dynamic range called L_Date_Time. I use this to do VLookups for 1st, 2nd and 3rd place in an if statement in the original table.

Since we are using an Excel Table, when you add or copy in data, the formula for the helper column is remembered and copied down automatically as are all the formulas. The pivot table may have to be refreshed.

I extended the formulas down to Row 102 on sheet 2 - if you have more data, extend them further.

I did find two cells in the original data where there was an error value in the cells. This put an error value in my formulas. I hunted these down and deleted their contents.

I only spot checked a few of these. To paraphrase my old math textbooks, "QA is left as an exercise for the reader."

I am assuming that you want the conditional formatting applied within each Date/Time and not across the entire column. I'll tackle that next.

[AlphaFrog]

You could use a COUNTIFS formula to rank each "F" value for a given Date-Time. Then use conditional formatting to highlight all the 1s, 2s, and 3s.

Put this formula in Cell H2 and copy down
=COUNTIFS([Date],A2,[Time],B2,[F1],">="&D2)

Change the [F1] and D2 for the other rank columns.

Apply CF rules to highlight all the 1s=green, 2s=Yellow, and 3s=red.
Alternatively, you could apply a Green-Red color scale to each of the ranked columns.

[dflak]

The conditional formatting will have to be handled with VB code. I can define an array formula or offsets for a dynamic range in the conditional formatting if the CF is based on formulas. However, I can't define a color range. Those CFs that use a color range won't allow dynamic ranges.

I don't like what I am seeing: defining one conditional format for each unique time slot times 4. These will have to be cleared (without disturbing the other CF on the sheet) and re-established whenever new data is applied to the sheet.

I can do this, but I don't like using a sledgehammer if an more elegant solution is available.

[margetc]

Hi DFLAK,
Thank you very much for the solution.
I was in the process of modifying the original requirement when your solution came through.
I never thought about creating another table with a pivot table index column - thanks, I've learned something new.

My modified requirement is a little more complex as it requires each cell in the T3Fn columns to be populated with its respective percentage value for the Date/Time/Fn range it refers to. This means the new table on sheet 2 would need variable amounts of columns.

Don't worry if the revised requirement is not achievable - I should be able to get by with what you have done so far as it is quicker than what I was doing. But if you do have any thoughts on the revised "percentage" requirement I would be grateful to receive them.
Thanks again for the solution AND for the detailed explanation.

Let me know if you don't think the percentage request is possible as I will close this thread and start a new one.

[margetc]

You could use a COUNTIFS formula to rank each "F" value for a given Date-Time.

Hi AlphaFrog,
Thanks for the suggestion. It looks like a good place to start. I've not come across COUNTIFS before. I like the idea and it will give me something to play around with.
I'm thinking there may be a way to adapt your suggestion to work out the relative percentages of the F1/F2/F3/F4 columns and insert the percent value in the T3F1/2/3/4 columns rather than the RANKs.
Thanks again.

[AlphaFrog]

Hi AlphaFrog,
Thanks for the suggestion. It looks like a good place to start. I've not come across COUNTIFS before. I like the idea and it will give me something to play around with.
I'm thinking there may be a way to adapt your suggestion to work out the relative percentages of the F1/F2/F3/F4 columns and insert the percent value in the T3F1/2/3/4 columns rather than the RANKs.
Thanks again.

Use SUMIFS instead...

=D2/SUMIFS([F1],[Date],A2,[Time],B2)

[margetc]

Super. Thanks.

[margetc]

Hi AlphaFrog,
Not sure if it is correct to continue here or to start a new thread.
I need to be able to quickly ConditionColour the percentages in columns T3F1/2/3/4 based on the unique Date/Time set of data.

I managed to make a macro but its not very neat as it relies on me selecting the first cell of the required range (eg H2 for the 28/3/18 + 14:00 range)
The table needs to be initially sorted by Date/Time.

Problems:
1. This does not colour code all the Date/Time set of data.
2. This will include part of the next data set if there are less than 10 rows in the data set.

It would be better for a macro to find the first unique Date/Time then colour the T3F1/2/3/4 columns.
Then find the next unique Date/Time and then colour the T3F1/2/3/4 columns. etc.

I tried to find the COUNTIIF for Date/Time and use this to select the rows to colourcode but I couldn't get it to work.
Any help would be appreciated.

[AlphaFrog]

Please Login or Register  to view this content.

[margetc]

Hi AlphaFrog,
Thanks for the code.
I think I understand the operation (finding each unique Date/Time plus T3Fn column and looping to format each array of unique Date/Time/T3Fn data).
However, I've hit a wall because I get an error 429 "active x cant create component With CreateObject Scripting.Dictionary".
I've googled a bit and it would seem that my Mac Office 356 Excel does not work the same as Win Excel.

Copied from internet.
" Mac OS does not have the Scripting Runtime library, so none of those things will be available to you -- Dictionary, FileSystemObject, etc.
You'll need to use a Collection or other data type in lieu of Dictionary type,"

I've no idea what this means.
Do you have any suggestions?

Thanks for your help.

[margetc]

Please Login or Register  to view this content.

[AlphaFrog]

Yeah. The method I used (the Scripting Dictionary) is not available on a Mac. I'll take another stab at it tonight.

1.) Change your profile to indicate you're using Office 365 Mac

2.) In future postings, also indicate that you have a Mac in your question. It may prevent redundant coding.

3.) Your Date and Time values in your example workbook are wonkie. The dates are serial dates, but the time values are just Text (not serial time values). That has an effect on how the macro is coded. If you don't know how Excel stores Date-Time values, read the link. Before re-coding the macro, I need to know is this the way your actual data will be or is this an anomaly in the example file? It would be better if the time values were serial-time values and not text, but I can work around it. Also, It would be a little easier to group the data if the date and time values were combined in one column. So explain how the data is entered or imported. What is the source? Can you combine the two in one column?

[margetc]

Hi AlphaFrog,
My apologies for not being explicit about my Excel version - I have added it to my profile now.

The thread I read about the Dictionary is... stackoverflow.com/questions/43738332/activex-component-can-t-create-object-excel-for-mac. Not sure if it's of use for my problem.

Regarding the Date and Time values: The date arrives like this. Unfortunately I have no control of the formats.
However, the file is not used elsewhere so you can modify the formats and/or add a new column to combine the Date/Time whichever way makes it easier.
Thanks again, and apologies for the Mac oversight.

[margetc]

Hi AlphaFrog,
Not sure if this is of use but after a bit more googling I amended some of the code and got past some of the error messages but still getting errors.
I'm just stabbing in the dark though as I don't understand vba other than the simple stuff.

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

[margetc]

Super. Thank you so much. It works like a dream.