I am trying to rack my brain around adding another formula to the below combination of formulas.
I am getting a unique list that has a matching value in a second column and I am grabbing this list with an offset:
=IFERROR(UNIQUE(XLOOKUP(OFFSET(I1,0,0,COUNTA(I:I),1),Ratings_data[rating],Ratings_data[Aggregated Rating])),"")
With this formula, I am retrieving a unique list from [Aggregated Ratings] if the [Rating] matches the list in Column I.
There is now a blank value in the data set, so I would like to introduce a filter to this set of formulas to <>"" however, I can't figure out where in the formula to introduce this.
Would be greatly appreciated if someone could assist me with this.
Edit:
If it helps here is an example of the data in the referenced columns:
| Ratings_data[rating] | Ratings_data[Aggregated Rating] |
|---|---|
| Service | Service |
| Drink | |
| Drink | |
| Value | Value |
| Service Speed | Service |
Column I:I = a unique list of Ratings_data[rating]
In the above example Drink would come up as a blank value with the above formula and I am trying to filter out these blank cells.
CodePudding user response：
Not sure why you're using a volatile OFFSET function here: even if the column I values cannot be placed in a Table, a dynamic range reference to those values can be created using a non-volatile INDEX set-up.
Still, this is not relevant if we use:
=UNIQUE(FILTER(Ratings_data[Aggregated Rating],ISNUMBER(MATCH(Ratings_data[rating],I:I,0))*(Ratings_data[Aggregated Rating]<>"")))