Building a Dashboard from a Report

Ok. I have the parsing solution. I was curious about the listings in case that too could be streamlined. I will attach a screenshot of everything in action, but here is a basic breakdown of everything as well.

I am using 4 Helper columns.

Note: I kept the column names condensed because I have a habit of missing a square bracket somewhere. You can use whatever you want for column names.

.

MaterialHelper:

=LOWER(SUBSTITUTE(Material@row, " ", ""))

This first one removes any spaces and converts everything to lower case. The reason for removing spaces, is that the parsing formulas use specific numbers (+1 or -1).

Removing the spaces turns all of the delimiters into "/". That way it won't matter if there is a space before, a space after, or spaces on both sides. As long as the slash is there, you're good to go. This helps with consistency of data which always makes things easier in the long run.

I used the LOWER function for the same reason. Consistency in data.

.

HelperA:

IF(FIND("/", MaterialHelper@row) = 0, MaterialHelper@row, LEFT(MaterialHelper@row, FIND("/", MaterialHelper@row) - 1))

This uses the LEFT function to pull that first selection. I had to include the IF portion to say that if there was no "/" (meaning there is only one type), then just duplicate what is in the MaterialHelper column.

.

HelperB:

IF(FIND("/", MaterialHelper@row) = 0, "", IF(FIND("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 1) > 0, MID(MaterialHelper@row, FIND("/", MaterialHelper@row) + 1, FIND("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 1) - FIND("/", MaterialHelper@row) - 1), IF(FIND("/", MaterialHelper@row) > 0, RIGHT(MaterialHelper@row, LEN(MaterialHelper@row) - FIND("/", MaterialHelper@row)))))

This was the trickier one. Fortunately there will only be 3 options, so it's the only one.

Basically it says that if there isn't a "/" (one selection), then leave the cell blank.

If there are 2 of them (which means 3 selections), then we used a MID function to pull what was between them.

If there wasn't 0 and there weren't 2, then the only option left was 1 "/" which denotes two selections. Since there is only one "/", we were able to use the RIGHT function to pull whatever comes after the "/".

.

HelperC:

IF(FIND("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 1) > 0, RIGHT(MaterialHelper@row, LEN(MaterialHelper@row) - FIND("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 1)))

Since we know that 3 is the max number of selections, we know that there will never be anything to the right of this one. That means we can just say that if there is a second "/", then pull the data from the right side of the cell starting after that second "/".

If there isn't a second "/", then we don't have a third selection, so the cell will remain blank.

.

To use this setup in your overall metrics, you would use the previously established COUNTIFS three times, adding them together.

=COUNTIFS({Supplier}, 1, {HelperA}, "organic") + COUNTIFS({Supplier}, 1, {HelperB}, "organic") + COUNTIFS({Supplier}, 1, {HelperC}, "organic")

.

The reason for having three separate functions added together as opposed to using one range for the supplier column and a second range for the three helpers is that the ranges have to all be the same size. Since the Supplier range is only one column wide, then whatever other ranges are used within that function must also be only one column wide.

.

NOTE: Because we used the LOWER function on the master sheet to convert the data before calculations, you don't nee the LOWER(@cell) function on your metrics sheet when referencing the type, but you would still need it for the Supplier Range criteria.

EDIT:

As an afterthought...

Depending on how your metrics sheet is set up and how you are wanting to display things, you may still need to use the LOWER function when referencing the Material range. If you are referencing a cell instead of entering specific text and that cell will be displayed on the chart, you would use something along the lines of...

=COUNTIFS({Supplier}, [criteria], {Material}, @cell = LOWER([column name]@row)) + ...................

.

When typing up the above solution I was focused on the parsing aspect. When I was giving an example of what to use on the Metrics Sheet, I forgot to scroll to the top of this thread to make it more specific to your use.

To make all of this work together, you would mesh the two solutions together.