Ask Your Question
0

Is there a calc formula (or some other way) that searches a table for multiple conditions?

asked 2019-06-22 04:26:46 +0200

Bill Ennals gravatar image

updated 2019-06-23 02:24:38 +0200

I have a table with five columns that contains data for an Employee shift schedule.

image description

Is there a formula, script, or other method of searching that table and returning values based on the following logic (apologies if this description is a bit rough).

IF 'Shift' = 'Sales-AM' AND DATE = a Monday between 2019-07-01 and 2019-12-31 then return that (or those) Employee's name(s).

My aim is to have a 2d matrix with the 'Shift' name in the x-axis, the Dates in the y-axis and the cells within the matrix to be filled with the correct Employee names based on the x and y values, like the following table:

image description

If feels like this should not be too hard to accomplish but I'm pretty new to spreadsheets and I haven't found any examples elsewhere that are trying to accomplish this kind of thing.

Thanks for reading,

Bill.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2019-06-29 21:26:13 +0200

Hello @Bill Ennals

I have created sample spreadsheet for you, demonstrating how such timetable can be filled with array formulas.

Tip 1. Boolean True and False values are the same as 1 and 0 integers in arithmetic operations. This allows you to run True/False test with multiple conditions as the product of these test conditions; product of (Test1)*(Test2)*(Test3)*(Test4) will return 1 and thusTRUE only when all tests return 1 (True). If any of these tests returns False and thus 0, the product will also be 0 (False). So in Calc you can perform a check if all test conditions are met simply by calculating the product of them.

Tip 2. Use an array formulas to iterate over the cell range, not single cell. In you use case you need to loop though all rows in your Shift information table, find rows where all test conditions are met and join the employee's names from these rows.

If any help/commentary needed, feel free to comment!

edit flag offensive delete link more

Comments

Thanks @SM_Riga!. That appears to work perfectly, though it will take me a while to figure out how;) I'll do some research and let you know if I have any questions.

Bill Ennals gravatar imageBill Ennals ( 2019-06-30 22:59:03 +0200 )edit
0

answered 2019-06-29 14:25:49 +0200

gregors15 gravatar image

Hi, I have created a pivot table using your data, plus 2 additional columns, one for an employee number and one concatenating the employee name and the number.

The layout to produce the pivot table is , The row fields, “Date from” and the “concatenated name and number”. The column fields, “Shift” Data fields is the "employee number". I don’t know of anyway to get the name to appear as data. The “Total Results” fields are not used.

I know its not exactly as you requested, let us know if it helps.

C:\fakepath\LOQ_20190629.ods

edit flag offensive delete link more

Comments

Thanks for answering @gregors15. It's a useful suggestion to use an employee code in a pivot table.

Bill Ennals gravatar imageBill Ennals ( 2019-06-30 23:00:51 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-22 04:26:46 +0200

Seen: 57 times

Last updated: Jun 29