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

I'm pretty desperate, because usually after many hours of research I'm able to find something that works, but this time (after several days), I could not. I will try to explain as understandable as possible. There I made overall explanation of my document (it is in my native language), in case you need to understand more of whats going on, if not, you can jump right to THE PROBLEM section.

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.

If you have any qauestions feel free to ask :) I will be happy for any kind of comment. Thank you!

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

CodePudding user response：

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.