I have an Excel (Well, technically Google Sheets) workbook containing product inventory warehouse storage information. I figured out how to construct queries so it grabs the needed information from one sheet with multiple entries (think "stream of consciousness") and puts it into another sheet where it only lists the latest item for a particular product position.
The issue I was having was that if no item was found with that description, it would give me an #N/A entry instead of leaving the cell blank. Research led me to wrap the query in a isna() call so I could manually blank out any #N/A cells, but now the query is called twice for every item, noticeably slowing down the computation.
A = Timestamp B = Aisle C = Section D = Row E = Position F = Quantity G = Description
=if(isna(QUERY('Form Responses 1'!$A$2:$G$10011, "select A where (B = "&B2&" AND C = "&C2&" AND D = '"&D2&"' AND E = '"&E2&"') ORDER BY A DESC LIMIT 1")), " ", QUERY('Form Responses 1'!$A$2:$G$10011, "select A where (B = "&B2&" AND C = "&C2&" AND D = '"&D2&"' AND E = '"&E2&"') ORDER BY A DESC LIMIT 1"))
My questions are:
1) Is there a more efficient way to blank out #N/A cells?
2) If not, is there a way I can avoid calling the same query twice (e.g. with something like a temporary variable within the formula)?
Any suggestions to "beautify" my formulae are welcome and appreciated.