0

For background, my job requires me to make up large amounts of mock data. Large like 100s of thousands of rows, and array formulas seem to be the best option to scale these activities. Onto the problem.

I have 2 arrays

see here

The first is a list of Accounts in one column and the Rep that owns the account in another. My second array is just random array of Reps. In the second array I want to choose one of the accounts that each rep owns - based on the assignments in the first array. These arrays are different sizes with the second being much larger given its just a random selection of reps with every rep being repeated multiple times.

All of the columns in both arrays are generated from other dynamic array functions using the # to reference and build the arrays.

This is the formula I originally came up with. In essence i'm trying to produce a filtered array of accounts that the lookup rep owns and then pick a random account from that filtered array.

=LET(
acctlist,$L$2#,
ownerlist,$M$2#,
replookuparray,Q2#,
filterbyowner,FILTER(acctlist,ownerlist=replookuparray),
INDEX(filterbyowner,RANDBETWEEN(1,ROWS(filterbyowner))))

I get a value error from this formula. If I remove the # from Q2# in the replookuparray it produces a correct result, but I want it to produce a result for the entire array 2 so I don't have to copy paste across the entire range (the numbers producing all of these arrays are volatile so they often change, and have one non-dynamic array makes the file much less useful).

Is there any way to get this logic to apply to the second array so I can maintain the use of dynamic arrays?

Improve this question

2 Answers 2

Reset to default
1

Use ISNUMBER(MATCH())

FILTER(acctlist,ISNUMBER(MATCH(ownerlist,replookuparray,0))

So in total:

=LET(
acctlist,$L$2#,
ownerlist,$M$2#,
replookuparray,Q2#,
filterbyowner,FILTER(acctlist,ISNUMBER(MATCH(ownerlist,replookuparray,0)),
INDEX(filterbyowner,RANDBETWEEN(1,ROWS(filterbyowner))))
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the response. It doesn't appear to be working though. It does resolve the value error but it's not producing a correct account for the rep (rep 1 in this case). It also doesn't apply the formula to the full array in the far right column.
0

This seems to work.

=BYROW(Q2#,LAMBDA(replookuparray,
LET(
acctlist,$L$2#,
ownerlist,$M$2#,
filterbyowner,FILTER(acctlist,ISNUMBER(MATCH(ownerlist,replookuparray,0))),
INDEX(filterbyowner,RANDBETWEEN(1,ROWS(filterbyowner))))))
Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.