0

My sample is here:

https://docs.google.com/spreadsheets/d/1j9pRECnAKtKtvAfhMvLv93-u6_kwIrFZg3RY3jSkpkU/edit?usp=sharing

I am trying to aggregate the last date that each "familyID" was contacted, based on the max "lastContact" date, between rows that have the same familyID but different personIDs. In my sample, every row in the REALlastContact column should say 4/28/21. I got so far as to aggregate the data in a pivot table that returns the correct last contact date for each familyID... but I need that information to be part of the same spreadsheet. In my real data, my pivot table would have to be on a separate page and cross-referencing is what I am trying to avoid. I am trying to use VLOOKUP referencing columns of that pivot table, which requires turning on iterative calculation, to avoid a circular dependency (the source of which I am not completely aware of, but sure), it seems to work but only in one cell at a time. I am very confused by this! What is the difference between C2 and C3?

Any advice would be appreciated!

1 Answer 1

0

You don't need the pivot table at all (i.e., you can delete it altogether).

Delete everything in Column C (including the header) and place the following formula in C1:

=ArrayFormula({"REALlastContact";IF(B2:B="",,VLOOKUP(B2:B,SORT({B2:B,D2:D},2,0),2,FALSE))})

This reads, in plain English, as follows: "Process an entire array, not just one cell. First, put the header. Under that (as indicated by the semicolon), if any row is blank in Column B, leave it null in Column C. Otherwise, look up whatever is in that row of Column B within a two-column array of the family paired with the contact date, sorted upside-down by contact date, and return the contact date (which will be the most recent, because the highest/most recent dates will be found first when sorted upside-down)."

3
  • This worked perfectly, even on my non-dummy dataset! It gave me numbers but allowed me to correct the format to dates as well. I really appreciate your writing that out in plain English so that I could adapt it to my (much more complicated) real data. Thank you so much for your help!!!
    – Leo
    Apr 30, 2021 at 21:01
  • A followup question: If I were to associated a timesContacted column with each personID, and then try to count the number of times familyID was contacted total, could I use something similar? I have been looking into it but I am getting tripped up by what SORT is doing in your answer. It seems like I only get one value back that is from the sorted list... Is there a way to, instead of doing that, to take all of the different timesContacted values associated with a specific family ID and add them up?
    – Leo
    Apr 30, 2021 at 21:25
  • Glad to know my formula worked for you and was easy to understand and apply. As for the new issue, the goal of the forum here is to be of maximum benefit not only to each poster but to future site visitors looking for answers to their own issues. So follow-up questions and solutions tagged on via comments are discouraged. I recommend that you first update your sheet to show what you're trying to do in context (I myself could not get a mental image on it from what you said here) and then start a new post. Please set the permission to "Anyone with the link..." and "Editor." I'll keep an eye out.
    – Erik Tyler
    May 1, 2021 at 0:29

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.