JOIN(COLLECT) or HAS() Issue

Question

Good afternoon,

I am having a hard time figuring out the correct formula.

Sheet 1 - Information to assign Person X to review Cases (1 or more). Review Request Number is Case Number-Person X ID number.

Sheet 2 - Timesheet for Person X . Need to enter their Person ID and the Case Number. These are combined and should match a Review Request Number

Sheet 3 and 4 - Error Report.  I need to make sure that all Timesheets submitted by Person X correspond to  a matching assignment. I have two error reports. One starts with the case number and sees if there are inconsistencies with the assigned Person ID on Sheet 1 and the Person ID on Sheet 2  based on the case number. I have this one working (I think).

The second is based on the Person ID. For each Person ID, I collect the assigned cases from Sheet 1  (1 or more) and the Case Numbers from Sheet 2 (one or more). I want to check to make sure that any Case Numbers pulled over from Sheet 2 appear in the assigned list from Sheet 1.  I only need to know when a Case Number from Sheet 2 does not appear on the list from Sheet 1.

Any suggestions?

L. Barrett · Answer

Here are the 4 sheets - I had to mask/modify some of the info

Sheet 1 - Assignments

Sheet 2 - Timesheets

Sheet 3 - Errors based on Case Number

Column B formula -

=JOIN(COLLECT({Assigned ELN}, {Request Case Number}, [Case Number]@row, {First}, 1), CHAR(10))

Column C formula -

=IF(ISBLANK(JOIN(DISTINCT(COLLECT({ELN TS}, {TS Case Number}, [Case Number]@row), CHAR(10)))), "", JOIN(DISTINCT(COLLECT({ELN TS}, {TS Case Number}, [Case Number]@row), CHAR(10))))

Column D formula -

=IF(ISBLANK([ELN Entered on Timesheet for this Case Number]@row), "", IF([ELN Entered on Timesheet for this Case Number]@row = [Assigned ELN]@row, "No Error", "Error - Review Timesheet Report, Entity License Number does not match the Entity License Number of the assigned expert on the Request for Review"))

Sheet 4 - Errors based on ELN

Column B formula -

=JOIN(COLLECT({Request Case}, {Assigned ELN}, [ELN]@row), CHAR(10))

Current Column C formula (NOT WORKING) -

=IF(ISBLANK(JOIN(COLLECT(DISTINCT({TS Case Number}, {ELN TS}, [ELN]@row), CHAR(10)), "")), JOIN(COLLECT(DISTINCT({TS Case Number}, {ELN TS}, [ELN]@row), CHAR(10))))

Current Column D formula

=IF(ISBLANK([CN Entered on Timesheet for this ELN]@row), "", IF([CN Entered on Timesheet for this ELN]@row = [Assigned Cases]@row, "No Error", "Error - Review Timesheet Report, Case Number on Timesheet does not match the Case Number on the Request for Review for this expert"))

Paul Newcome · Answer

Are you able to provide screenshots of the sheets you are working with for context?

hello_aj · Answer

Would you mind sharing a screenshot of your sheet and current formula?