Forum Discussion

jaolvera's avatar
jaolvera
Brass Contributor
Sep 08, 2023

by row formula

Hello,  I needed a formula that would return a yes or no result by row in column A, if the dates in column that correspond with the patient IDs in column C are within 30 days of each other. some bac...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 08, 2023
It looks like your data is tabled. BYROW will Spill and Tables are scalar-based. If you convert the table to a range the error may go away.
jaolvera's avatar
jaolvera
Brass Contributor
Sep 08, 2023
it still didnt work now some cells say CALC error on the cells
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 08, 2023
    Are you able to share an anonmyized copy of the workbook?
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Sep 08, 2023

        jaolvera 

        My rendition:

        =LET(
    GetDates, LAMBDA(row,
        LET(
            dates, FILTER(DtCreate, PatientID = row, 0),
            diff, DROP(dates, 1) - DROP(dates, -1),
            IFERROR(IF(MIN(diff) <= 28, "Yes", ""), "")
        )
    ),
    BYROW(PatientID, GetDates)
)

Resources