0

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.

2 Answers 2

0

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().

2
  • 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
    – Blindspots
    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
0

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.
  • Optional SORT (asc) added



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.