Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaspreadsheettextjoin

Sheets Formula Works with Iterative Calculations but data gets very messy very fast (with GIF)

Picture: Sheet Document

OVERALL EXPLANATION OF DOCUMENT AND WHAT IS WHAT:

I have a Google Sheet document (Picture ↑) with

  • Products (Product Category and Name) - Columns B:D
  • Prices (Weight, Prices with VAT and without, Packaging) - F:Q
  • Additional Info (Manufacturer, Supplier, et.c) - Column R, S and next ones

From time to time for example as it is visible in the Row 7, I need MAX PRICE for all the same products. I achieved that with formulas: Picture: F7 Picture: N7

These two formulas work and does everything I need. So no problem here.

THE PROBLEM

The Problem sits in R7 and S7 (I highlighted them in picture named "Sheet Document").

I found formula that works: =SUBSTITUTE(textjoin(", ",true,unique(filter(R4:R,$C$4:$C=$C7))),", 0","") It combines all unique Column R values that mathces all the C7 values.

This is how formula should look like. Picture How it should be

Problema is - it works only with Iterative Calculation - ON.

Why it is a problem?

Each time I want to add this formula to another cell (I'm planning to add it manually, because there are specific instances where I need to know combined info about the specific product), it duplicates everything (See GIF below) ↓

GIF: GIF - Problem - Duplicating

I think it is because of Iterative Calculation, I have never seen any formula act this way before.

What can be done here?

  • Maybe use other formula for R7, S7? I have tried a lot of - Vlookup, Index-Match, Query, the only one that worked exactly as I wanted was Substitude-Textjoin-Filter, but yeah, Iterative Calculation meses it up.

  • Maybe I need to change all the other formulas? I know that with the Circular Dependency Error, you should look for overlapping formulas. In the OVERALL EXPLANATION section you can see how other formulas in the same row are made. I couldn't figure out the one that was overlapping.

  • Maybe some App Script can solve this? I'm very new to scripts, have used in some documents, including this one, I am open to using them, just need little bit more explanation.

I tried a lot of different formulas, tried to solve Iterative Calculation glitch, changing formulas in other cells so they are not overlapping.

Solution

  • Use a mix of absolute and relative references, like this:

    =textjoin(", ", true, unique( filter(R$4:R6, C$4:C6 = C7) ) )

    The formula should go into cell R7. You should turn off iterative calculation if you can.