i came across this formula, but i dunno how to change it. to display the Query value in single cell by category. there are 5 categories, 1st cell show 1 category, 2nd cell show 2nd category and so on..
if dont have 1st category, then starts from 2nd category and so on..
i enclosed the link here to edit..thank you
changing query formula to display as required, but failed, please help
On
Here's one approach you may test out:
=reduce(tocol(,1),unique(tocol(A2:A,1)),lambda(a,c,ifna(vstack(a,hstack(c, let(Λ,torow(,1), Γ,char(10),
reduce(Λ,tocol(F2:F,1),lambda(f,q,let(Σ,filter(B:B&Γ&D:D,C:C=q,A:A=c),hstack(f,if(iserror(Σ),Λ,q),if(iserror(Σ),Λ,textjoin(Γ&Γ,1,Σ))))))))))))
Take a look at outcomeCS. Here is the formula:
and I'll provide some explanation.
Essentially, we need to get a list of categories and then fetch the values that match those. We want to get the values from Column B and Column D by category and date, so we want to use something like FILTER, and let's say that we had a category and a date, then we'd do it like this:
and
To get the output you want:
we want to take each pair of values and combine theme like this: ColB line break ColD. To do so, let's use MAP. MAP takes one or more arrays and then applies a lambda formula to each value or values. The MAP syntax is: MAP(input, lambda(value, formula)), and it breaks the input array into each value, and then passes that value to the lambda, where the formula is applied for each value. Multiple inputs can be used. In this case, I will pass the two filtered columns in as the first two arguments to map, and then in the lambda I will use y and z for the value placeholders, finally just joining y and z with a line break (char(10)):
The lambda is what is actually returned, and it will be an array of the same size as the input array. In this case, we are just concatenating the filtered values from B and D. Since you want to combine all these individual values like this:
we can pass that array to TEXTJOIN, which takes an array of values and then concatenates them together using the provided separator.
FILTER will throw an error if there are no matches in the filtered set, so let's wrap the entire thing in IFERROR:
And this will give you an array of values for a specific category or date. The only trick is getting the category and date to pass into the filter. Now, if you didn't need them sorted or you just needed them alphabetically then we could use UNIQUE on Column C, but since you need a specific order we'll create the array manually using literal notation. {} is the literal notation for arrays in googleSheets, and the separator (either , or ;) determines if the array is horizontal or vertical.:
and to save some typing we'll use LET. LET allows you to define some reusable values, and it takes name/value pairs in this format:
LET(name1, value1, name2, value2, .... expr)
where expr can be any valid formula, anything you could normally put after = in a formala. The first thing we are going to do is to take that array of categories and give it a name.
Second we'll use those categories to MAP a new output array, and we'll perform the same operation we discussed earlier on the categories We'll assign this new array to the name "results" (we're just continuing the LET statement here):
This looks a little complicated, but let's give the operation we discussed earlier, the entire IFERROR/TEXTJOIN/FILTER block, a name: joinedRemarks, and use this as a placeholder to make the function more readable:
And then let's take out some of the whitespace:
So all we're doing is giving a name (results) and assigning the output of that MAP formula to it. In this map, I am passing in categories, the array we created before, and defining a lambda: lambda(x, {x, joinedRemarks}). MAP takes the category array and then passes each value from it to the lambda, where x is the placeholder for the passed value, and the formula to apply is {x, joinedRemarks}, where joinedRemarks is the TEXTJOIN/FILTER operation from before. Remember that {} is the literal notation array, so we are taking x (the value passed in from the input array) and combining it with the joinedRemarks for that value into a new array.
One thing we have to do is define the actual category and date in the FILTER formula. Remember I defined them like this:
but we never actually provided a real category and date, I just said "if you had a category and date, you could filter with them". Now in this MAP formula where the input array is categories, the placeholder for the lambda is x, so we'd want to replace category with x, and since the date is in Col A we can replace that, too. So instead of "category" and "date" from the original explanation, we are using x (the placeholder value from the lambda) and the value from column A:
so that the entire thing so far is:
We want to screen out any elements of the array that produced errors (remember, FILTER returns an error if there are no matches), so we'll use filter on the results array we just created to remove those and assign it to the name filteredResults:
and then since you want these horizontally we'll REDUCE these into a new array, buffer:
We're doing this because filteredResults is going to contain something like this:
in a two-dimensional array, so we can't just transpose it, or we'd just end up with a two-dimensional array still, only turned sideways, and you want one row. So what we'll do is pass the filteredResults to REDUCE, which is almost like MAP, except instead of taking an array and producing a new array, it takes an array and reduces it to a single value: REDUCE(initial, input, lambda(accumulator, value, formula)). The accumulator is the variable we're adding new values to, and initial is the starting value of the accumulator.
So here REDUCE will take each element of the array (itself an array: categoryA, resultsA) and then use the literal notation {a,x} to combine them into a horizontal array. So first we'll have this: categoryA, resultsA. Then on the next iteration we'll have: categoryA, resultsA, categoryB, resultsB and so on.
And finally, since we started with an initial empty value, and then kept extending the array, we just want to remove that empty value from the array:
Here is the entire thing:
I know this is long and complicated. It's a lot simpler than it seems once you get the hang of LET, MAP and REDUCE. Hope this all makes sense.