Help with complex what-if formula to reduce multiple variables to one and filter data?

Hi all,

I received great help from GlennKennedy, thank you very much, in this thread on a similar theme.

Basically, I have a large set of data composed of separate days (rows) and multiple columns (metrics/variables). Filtering/sorting this data can be time consuming and complex , so I got the idea that I could summarize several conditions in one variable/metric.

Thanks to the great help from GlennKennedy, I was able to create a 'pattern' metric based on three of my current variables. This has made it so much easier to sort through my data and I can even quickly visualize that day based on what that metric alone tells me.

To summarize.

I have variable 1 = LH, variable 2 (time data) = 10:15 and variable 3 (time data) = 15:55. Now, I chose to summarize this into one variable/metric which simply reads: "LH_10-16:00"

Obviously much faster to filter and also faster for my brain to process.

I'm now interested in possibly taking this a step further and including even more variables...

So, my question is basically if this is possible using the same type of formula? Or are there limits to the amount of variables one may include?

Any other suggestions for how to quickly filter/sort through large datasets? Basically, I'm interested in finding similar occurences/patterns in my data set. The trick above helps as a preliminary divide-and-conquer filter.

I'm attaching a picture of what Glenn helped me with and also the associated Excel sheet.

Thanks in advance for any help.

Best regards,

Elijah

One modification I could see myself making would for example be to include if the Close (as seen on picture and in spreadsheet) is positive/negative.

If so, the pattern could read LH_10-1600 - or LH-10-16:00+.

This basically let's me sort 4 variables in one go.

Any advice...?

Hi,

No, I haven't. I would if I could, but the formula Glenn used is a bit above my current capabilities.

Appreciate the effort, FDibbins.

I will see if I can make sense of it tomorrow. This week has been crazy at work, so no time to look into this yet.

But I do know that the most likely modification I'd like to do is to add the Close value to the Pattern, i.e., if positive Close: LH + (or some similar notation to differentiate); if negative Close; LH -.

Best.

Elijah