0

I'm trying to find if someone has been scheduled in the AM and PM in a table like this:

example

So I want to write something like this: =QUERY(A13:B1002,"select A,B where A matches 'AM' and B contains 'Grace'") and if true, have it output as an x in another table like:

second table

The caveat is that the AM and PM's are in merged cells, and my present query will only work for the first row of names.

How can I get the value of a merged cell to plug into my query function?

2
  • Hi and welcome. There was a similar question on Doc Editors Help Result of query or filter functions in the merged cells and I think the answer is worth repeating: What you are trying to do is not possible. It is not recommended to use merged cells in any table where data is to be manipulated. Merged cells are intended for cosmetic purposes only and must be kept outside the data table range."
    – Tedinoz
    Commented Nov 24, 2021 at 22:54
  • A thought about the output: (I'm not trying to avoid answering, just reflecting on challenges). This is really a list on names of people who are scheduled to work double shifts, so it seems redundant to devote two lines to each name. Simply getting their name on the list would seem sufficient. There's also an aspect around consistency of names: you would want to dynamically query every staff member but your query focuses only on "Grace" and somehow it returns the name "Andrea" even though it has queried "Grace". How does that work?
    – Tedinoz
    Commented Nov 24, 2021 at 23:18

1 Answer 1

0

You want to generate a display for any employee who is rostered to work both an AM and PM shift on the same day. Your raw data includes merged cells, and this is a barrier to developing formula that can make use of the values in those cells. As noted in the comments, "Merged cells are intended for cosmetic purposes only".

This answer does not use the data layouts that are described in your questions. However, it shows an alternative to storing the raw data and building display layouts from that data.

It is possibly/likely that there are aspects of this answer which could be made more efficient. That might be something for the OP to explore. Also, this is simplified (on the basis of the question) to address generic columns for generic dates; an obvious improvement would be to create a "week-starting" date that is built into formulas to create dynamic data ranges from day-to-day and week-to-week.

NB: I have also made the names of staff consistent so that formula can dynamically process ALL employees in a single pass. Data Validation might be a useful addition to the OPs processes.


Raw Data

rawdata

This image shows an approach to storing raw data for shift allocations.

  • Dates are headers. The date in cell O2 is entered manually; the date in cell Q2 is a formula(=O2+1), and this is copied across as many columns as necessary.
  • Shift data for each date uses two columns:
    • Shift Time
    • Employee name
  • Shift details are entered manually (though Data Validation could be used for simplification); details can also be entered in any order (i.e. it's not necessary to list the AM shifts, then the PM shifts).

Roster Display

roster display

This is the equivalent of the first image shown in your question.

  • the dates in Row2 are formula linking the dates shown in the Raw Data
  • note that there is a blank column in every second column; this simplifies copying formula from one column to another. The formula for AM shifts in Cell B3 is:

=ifna(query({O4:P8},"select Col2 where Col1='AM'"),)

The formula for PM shifts in Cell B7 is:

=ifna(query({O4:P8},"select Col2 where Col1='PM'"),)

  • Both formula should be copied across as many columns as necessary.
  • ifna is used to stop an error when there are no values to be displayed. This could be changed to something meaningful.

The query formula is straightforward: it list the Employee name depending on whether the "Shift" column equals "AM" or "PM"


Double shifts

double shifts

This is the equivalent of the second image shown in your question.

  • the dates in Row2 are formula linking the dates shown in the Raw Data
  • note that there is a blank column in every second column; this simplifies copying formula from one column to another.

The formula in Cell I3 is:

=ifna(query({query({query({O4:P8},"select Col2 where Col1='AM'");query({O4:P8},"select Col2 where Col1 = 'PM'")},"select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1) '' " )}," select Col1 where Col2 >1 "),)

  • Copy the formula across as many columns as necessary.

The query may seem complicated but it is straightforward:

  • it takes the respective queries for the "AM" and "PM" shifts as input, stacking the output by using curly brackets {} and separating the queries with a semi-colon ;. This generates a list of all the employee names working on a given day.
  • the select clause lists each employee name and number of times each name is included in the list ("Col1,count(Col1))
  • then a new query takes all this and lists only those names with a count greater than 1 (select Col1 where Col2 >1). These are the names that appear as working double shifts.
  • ifna is used in case there are no double shifts.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.