# Unique across columns

I have a spreadsheet that looks like this:

Company Fund Date Value
A X 30/09/2022 12
B X 30/09/2022 15
E X 30/09/2022 31
A X 31/12/2022 10
B X 31/12/2022 20
C X 31/12/2022 15
D Y 31/12/2022 22

I need to output, using formulas (not macros, filters, or manual data manipulation), all of the combinations of `Company`, `Fund`, and `Value`, for a pre-determined date. The `Value` should be zero, if there is no entry for the specified date.

For a chosen date of `31/12/2022`, for example, the output should be

Company Fund Value
E X 0
A X 10
B X 20
C X 15
D Y 22

I've tried to use a unique combination of `Company` and `Fund`, which didn't work as expected.

Use `filter()`, `match()`, `choosecols()` and `vstack()`, like this:

``````=let(
matches, filter({ A2:B, D2:D }, to_text(C2:C) = "31/12/2022"),
nonmatches, filter({ A2:B, iferror(D2:D / 0, 0) }, len(C2:C), isna(match(A2:A, choosecols(matches, 1), 0))),
sort(vstack(matches, nonmatches), 3, true)
)
``````

See filter(), match(), choosecols() and vstack().

• Learned a couple of new techniques such as forcing an error vs. a SEQUENCE of zeros. I was trying to figure out how you addressed the complication posed by `nonmatches` based on a combination of `Company` and `Fund`. Correct me if I'm wrong but I think your formula returns `nonmatches` based solely on `Company` which wasn't exposed with the sample data, which has no `Company` with an additional fund. see example image May 31 at 17:06
• That can be addressed with `unique()`, but I am not sure if that requirement is specified in the question — it uses the phrase unique across columns but the sample data only shows one fund per company. May 31 at 19:14

### Formula

``````=LET(
rng,A2:D8,date,F2,
vals,FILTER(CHOOSECOLS(rng,1,2,4),INDEX(rng,,3)=date),
zrows,FILTER({CHOOSECOLS(rng,1,2),SEQUENCE(ROWS(rng),1,,0)},NOT(COUNTIF(
{ INDEX(vals,,1) & INDEX(vals,,2) },
{ INDEX(rng,,1) & INDEX(rng,,2)   }))),
SORT(IFERROR({vals;zrows},vals)))
``````
• `rng` stores the range that contains your data
• `vals` stores the array of rows that match the FILTER condition, which is a matching date in column `C`. CHOOSECOLS is used to drop the date column from `rng`, to match your requirements.
• An array of `rng` columns 1 and 2 as well as a column of zeros created using SEQUENCE are filtered with a NOT(COUNTIF) condition to check if any combination of `Company` and `Fund` from `arr` does NOT exist in `vals`
• The `Company` and `Fund` values for each row were concatenated in `arr` and `vals` and those concatenated values are what is compared, this ensures all combinations of `Company` and `Fund` are checked.
• IFERROR is used for cases where the NOT(COUNTIF) returns no matches.