Converting an Authorization Matrix into an Approval Sequence

Colin Herrera

This is tough to explain.

This first screenshot is a list of people who can authorize purchases. The 2nd column denotes their dollar value limit for any single purchase authorization. "Department" denotes what department in which they're allowed to make those authorizations.

I'm trying to convert this list to a second sheet, which will provide a sequence based on the above list.

Essentially, if someone requests to purchase an item that costs < $10,000 in Department 7090, Person 38 needs to authorize it. However, if someone requests to purchase an item that costs in department 7090, Person 38 first needs to authorize it, followed by Person 32 since the limit is above Person 38.

If the item is greater than Person 32's limit in 7090, then it goes to another tier above them. And so on until we reach a person who can authorize the limit. The sequencing part is built, but I can't figure out an easy way to populate the names from the above list into the sequencing.

Is there a formula that can make this happen?

I'm thinking in the Approver 1 column of the 2nd sheet, the formula would be something like:

=IF(MATCH({Department}, [Department]@row, 0)=1, INDEX({Person}, ...

But of course, I only want it to look at a range where the range of departments from the 1st sheet is limited to the rows that contain the [Department]@row on the 2nd sheet.

I feel like nesting a CONTAINS, HAS, or COLLECT formula in my MATCH is the solution but I can't figure it out.

Let's assume the Department column is cleaned up so that Person 32's 7079-7083 is revised to be 7079, 7080, 7081, 7082.

Devin Lee

Hey @Colin Herrera,

Placing the Names

Great question. I would approach the approver limit as a fixed variable so it breaks down into dollar columns rather than approval order. For example $10,000, $25,000, and $75,000. So Approver 1 would be $10,000, Approver 2 would be $25,000 and Approver 3 would be $75,000.

This would make it easier to lookup values because you already know what dollar value you are looking for and can use the formulas below.

[$10,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =10000), 1), "")

[$25,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =25000), 1), "")

[$75,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =75000), 1), "")

Order the Names

If you still want to have a list of Approver 1,2,3, etc. You can add some helper columns named Order 1,2,3 etc and use the formula below in Order 1 to join all the names across the dollar columns.

[Order 1[1

=JOIN(COLLECT([$10,000]@row:[$75,000]@row, [$10,000]@row:[$75,000]@row, NOT(ISBLANK(@cell))), "_") + "_"

In Order 2 and onward you can use the formula below. This will remove one of the names from the referenced cell. Have each column reference the previous Order column so a name is removed each time.

[Order 2]1

=IF(RIGHT([Order 1]@row, LEN([Order 1]@row) - FIND("_", [Order 1]@row)) = [Order 1]@row, "", RIGHT([Order 1]@row, LEN([Order 1]@row) - FIND("_", [Order 1]@row)))

[Order 3[1

=IF(RIGHT([Order 2]@row, LEN([Order 2]@row) - FIND("_", [Order 2]@row)) = [Order 2]@row, "", RIGHT([Order 2]@row, LEN([Order 2]@row) - FIND("_", [Order 2]@row)))

Place into Approver Order

Now we want to pull in the first name listed in each Order column so we use the formula below

[Approver 1]1

=IF([Order 1]@row = "", "", LEFT([Order 1]@row, FIND("_", [Order 1]@row) - 1))

[Approver 2]1

=IF([Order 2]@row = "", "", LEFT([Order 2]@row, FIND("_", [Order 2]@row) - 1))

[Approver 3]1

=IF([Order 3]@row = "", "", LEFT([Order 3]@row, FIND("_", [Order 3]@row) - 1))

Note

When referencing the original data check to see the cells are not number values but are strings. This might happen when there is only one set of numbers listed. Put an apostrophe at the start of the number so it will read as a string. Example: 7081 would be changed to '7081. The apostrophe will not show unless you are in the cell. Generally, you can tell by the fact that numbers are right justified while strings are left but this can be overwritten and will cause errors with some functions. In this case it's the CONTAINS function.

Devin Lee

Hey @Colin Herrera,

Placing the Names

Great question. I would approach the approver limit as a fixed variable so it breaks down into dollar columns rather than approval order. For example $10,000, $25,000, and $75,000. So Approver 1 would be $10,000, Approver 2 would be $25,000 and Approver 3 would be $75,000.

This would make it easier to lookup values because you already know what dollar value you are looking for and can use the formulas below.

[$10,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =10000), 1), "")

[$25,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =25000), 1), "")

[$75,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =75000), 1), "")

Order the Names

If you still want to have a list of Approver 1,2,3, etc. You can add some helper columns named Order 1,2,3 etc and use the formula below in Order 1 to join all the names across the dollar columns.

[Order 1[1

=JOIN(COLLECT([$10,000]@row:[$75,000]@row, [$10,000]@row:[$75,000]@row, NOT(ISBLANK(@cell))), "_") + "_"

In Order 2 and onward you can use the formula below. This will remove one of the names from the referenced cell. Have each column reference the previous Order column so a name is removed each time.

[Order 2]1

=IF(RIGHT([Order 1]@row, LEN([Order 1]@row) - FIND("_", [Order 1]@row)) = [Order 1]@row, "", RIGHT([Order 1]@row, LEN([Order 1]@row) - FIND("_", [Order 1]@row)))

[Order 3[1

=IF(RIGHT([Order 2]@row, LEN([Order 2]@row) - FIND("_", [Order 2]@row)) = [Order 2]@row, "", RIGHT([Order 2]@row, LEN([Order 2]@row) - FIND("_", [Order 2]@row)))

Place into Approver Order

Now we want to pull in the first name listed in each Order column so we use the formula below

[Approver 1]1

=IF([Order 1]@row = "", "", LEFT([Order 1]@row, FIND("_", [Order 1]@row) - 1))

[Approver 2]1

=IF([Order 2]@row = "", "", LEFT([Order 2]@row, FIND("_", [Order 2]@row) - 1))

[Approver 3]1

=IF([Order 3]@row = "", "", LEFT([Order 3]@row, FIND("_", [Order 3]@row) - 1))

Note

When referencing the original data check to see the cells are not number values but are strings. This might happen when there is only one set of numbers listed. Put an apostrophe at the start of the number so it will read as a string. Example: 7081 would be changed to '7081. The apostrophe will not show unless you are in the cell. Generally, you can tell by the fact that numbers are right justified while strings are left but this can be overwritten and will cause errors with some functions. In this case it's the CONTAINS function.

Colin Herrera

Devin, I appreciate the quick reply.

I'm getting an #INVALID REF error with the first formula, but I can't figure out why. Here are my cross-sheet references:

{Name} is the Person column on the first sheet.

{Fleet Budgeted} is the Max Limit column on the first sheet.

{BUs} is the Department column on the first sheet.

BU@row is essentially [Department1]@row.

Here's the formula I adopted from yours:

=INDEX(COLLECT({Name}, {BUs}, CONTAINS(BU@row, @cell), {Fleet Budgeted}, =10000), 1)

I can't figure out what I'm messing up here. Does @cell work with cross-sheet ranges or is there something I'm missing here?

Colin Herrera

@Devin Lee nevermind... that was bizarre, but the sheet deleted the references? Must have been a bug. As soon as I re-made them, the formula worked. Thank you! You've put me on the path to success. I'm confident I can take it from here.

Colin Herrera

@Devin Lee I'm working on building this very closely to the way you suggested, but one thing I'm realizing is that the dollar values are variable.

i.e. for some departments, the tier amounts are $5k, $10k, $20k, etc. and for other departments, the tier amounts are $1k, $2k, $3k, etc. (The differences aren't this extreme, but just giving an example.)

I've been wracking my brain on trying to write the formula so that we can treat the dollar values as another variable before pulling in names, but I'm having a hard time coming up with something elegant. Almost wonder if there needs to be a "helper" column to do a MATCH to find out the dollar value tiers in a given department, and then INDEXing against that? But I can't get it to work.

Any suggestions?

Devin Lee

@Colin Herrera Have you tried using LARGE? It can be used to pull the numbers based off the ranking in a range.

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], LARGE([Max Limit]:[Max Limit], 1)), 1), "")

skarkhoff

@Colin Herrera I'm in the process of building out something similar to what you noted. Can you tell me if it's working well for you? Are you willing to share a sample at all of how your sheet is set up?

Colin Herrera

@skarkhoff Unfortunately due to our schedule for getting this project off the ground, we never implemented this part of the build. We are simply updating our lookup table once a quarter for the foreseeable future.

Here's an idea of what our lookup table looks like. As people get promoted or retire, or as their limits change, we have to update this lookup table manually. We are then DataMeshing information from this table to another sheet which manages the approval routing, based on the type of purchase and the department. Hope this helps.

skarkhoff

@Colin Herrera Thanks for the screenshot! I'll keep you posted if I'm able to create something similar!