Finding missing and extra values from table

[jb]

there are 2 sheets in my excel file - data and report.
Data sheet contains 2 tables - work alloted (k2:s8) and work done (a2:i8).

Both tables has same format.

Work alloted table contains worker id, worker initial and 7 slots. Each row mentions id, short name of worker and different tasks alloted to him/her in different slots.

Work done table contains worker id, worker initial and 7 slots. Each row mentions id, short name of worker and different tasks done by him/her in different slots. Same task will not be done twice.

Now, it is possible that
1. A worker can complete alloted task in any slot.
2. A worker can omit some task.
3. A worker can perform extra task.

It it required to generate a report at the end of the day as per report sheet.

There are two table in report sheet - work not done and work extra done.

For example, worker id 1 was alloted A1 task in slot-2, B5 task in slot-4 and C7 task in slot-6.
But he did B5 task in slot-1, C7 in slot-4 and D1 task in slot-6.

So in work not done table - there will be entry of A1 task in slot-2.
And in work extra done table - there will be entry of d1 task in slot-6.

 

[Peter Bartholomew]

Hi
Does this get things started. Each table tries to look up the entries in the corresponding row of the other table. Non-blanks that are missing in the other table are selected.

 

[jb]

Well your excel file worked perfectly. When I tried to replace named range with actual cell address range then it is not giving accurate anser for "work not done" table. I think I have done some silly mistake but not able to find it out. I am attaching my sheet with array formula given by you but with actual cell range. Can you tell me where I have done mistake?

 

[Peter Bartholomew]

Difficult to compare as I never use direct cell referencing. However, I believe the main issue is that each of the rows in my formula is a multicell array formula. That would allow you to use TEXTJION to aggregated the deviant tasks for each worker into a comma separated list (see sample). TEXTJOIN is a new function in Excel 2016, so is not available to me on 2010.

Otherwise you could modify the formulas to test cell-by-cell; something else I avoid where possible!

 

[bosco_yip]

there are 2 sheets in my excel file - data and report.
Data sheet contains 2 tables - work alloted (k2:s8) and work done (a2:i8).

Both tables has same format.

Work alloted table contains worker id, worker initial and 7 slots. Each row mentions id, short name of worker and different tasks alloted to him/her in different slots.

Work done table contains worker id, worker initial and 7 slots. Each row mentions id, short name of worker and different tasks done by him/her in different slots. Same task will not be done twice.

Now, it is possible that
1. A worker can complete alloted task in any slot.
2. A worker can omit some task.
3. A worker can perform extra task.

It it required to generate a report at the end of the day as per report sheet.

There are two table in report sheet - work not done and work extra done.

For example, worker id 1 was alloted A1 task in slot-2, B5 task in slot-4 and C7 task in slot-6.
But he did B5 task in slot-1, C7 in slot-4 and D1 task in slot-6.

So in work not done table - there will be entry of A1 task in slot-2.
And in work extra done table - there will be entry of d1 task in slot-6.

I put a new report table on "report" sheet row 12:17 for comparison purpose.

1] In "Work Not Done" table C12, copied across and down :

=IF((data!M3<>"")*ISNA(MATCH(data!M3,data!$C3:$I3,0)),data!M3,"")

2] In "Work Extra Done" table M12, copied across and down :

=IF((data!C3<>"")*ISNA(MATCH(data!C3,data!$M3:$S3,0)),data!C3,"")

3] See attachment

Regards
Bosco

 

[Peter Bartholomew]

You could modify the formulas to test cell-by-cell; something else I avoid where possible!

… but it works, as Bosco has shown and it will most likely suit you better. The ISNA function is more appropriate than my IFERROR.

The attached is a copy of a file that worked under Excel 365 but not on the Excel 2010 version I am using on this machine. It uses Named functions to hold a row of each assessment table and then TEXTJOIN to concatenate the (potential) list to a single cell. Then the helper ranges were deleted.

 

[jb]

OWESOME solution helpers.
last thing, if a particular task is alloted n times and done < or > n times then what to do?
For example, for a particular worker task B5 is alloted say 2 times and done 1 or 3 or 5 times, then how to get them in not done table if done lesser number of times and
how to get them in extra done table if done more number of times?

 

[jb]

Solution worked perfectly for normal situation.
But if a particular task is alloted n times and done < or > n times then what to do?
For example, for a particular worker task B5 is alloted say 2 times in slot-1 and slot-4.

Situation-1:
task b5 done by the worker say 1 time in slot-2 so either slot-1 or slot-4 in not done table should display task B5.

Situation-2:
task b5 done by the worker 4 times in slot-1, slot-3, slot-4 and slot-5, then in extra done table it should display task b5 in any two slots as extra done.

I am attaching sample file for reference.

 

[Peter Bartholomew]

That is a very different situation. Instead of simply using MATCH to determine whether the task occurs in the corresponding row of the other table, one needs to assign instance numbers to each occurrence of a task and compare that with the total number of occurrences in the corresponding row of the other table.

If you use Excel 2016 this would still display better by using TEXTJOIN to show the tasks as comma-separated lists.

 

[jb]

thanks a ton.

 

[bines53]

=IF(COUNTIF(data!$C3:$I3,"<>"&data!M3)=7,data!M3,"")

=IF(COUNTIF(data!$M3:$S3,"<>"&data!C3)=7,data!C3,"")

Copy the formulas, right and down

David