Excel - unique combination

Question

I have a spreadsheet that looks like this:

Comp  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, nor filters, nor manual data manipulation), all of the combination of Companies and Funds and value, for a pre-determined date. THE VALUE SHOULD BE ZERO if there is no entry for such a date.

For a chosen date of 31/12/2022 (for example, in cell F1), for example, the output should be

Comp  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 Comp and Fund, which didn't work as expected.

Answer 1

You could try this using a combination of HSTACK() and BYROW() functions as shown in below:

---

• Formula used in cell G4

=LET(α,SORT(UNIQUE(A2:B8)),σ,G2,
HSTACK(α,BYROW(α,LAMBDA(x,XLOOKUP(1,(TAKE(x,,1)=A2:A8)*(TAKE(x,,-1)=B2:B8)*(σ=C2:C8),D2:D8,0)))))

---

---

=LET(
α,A2:A8,
ß,B2:B8,
Δ,C2:C8,
Θ,D2:D8,
Φ,G2,
ε,SORT(UNIQUE(HSTACK(α,ß))),
VSTACK({"Comp","Fund","Value"},
HSTACK(ε,BYROW(ε,LAMBDA(x,
XLOOKUP(1,(TAKE(x,,1)=α)*(TAKE(x,,-1)=ß)*(Φ=Δ),Θ,0))))))

---

The idea of using Greek Letters has been taken from Jos Woolley Sir, I found it looks cool while assigning names to calculation results and variables.

---