by row formula

[jaolvera]

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 background, column C is patient ID, there are at least 2 or more of each and within those I need to see if the dates that correspond to those Patient IDs are within 30 days of each other. 

Thank you

[#2]

@jaolvera 

Isn't this the same question as in this discussion?

Formula for excel - Microsoft Community Hub

If it's different can you add the expected results in your screenshot? Thank you.

[#3]

Similar, when I went back to look at the formula after I thought it was fixed, I Realized the previous formula was copied in all the cells, so I still had the spill error, wasnt sure once they closed out the discussion if I could go back and ask the question again so I posted it as a new question. and I will have to remove the previous one.

[#4]

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.

[#5]

it still didnt work now some cells say CALC error on the cells

[#6]

Are you able to share an anonmyized copy of the workbook?

[#7]

@Patrick2788 

[#8]

@jaolvera 

As variant

=LET(dates, SORT(FILTER([DtCreate],[PatientId]=[@PatientId])),
    IF( ROWS(dates) = 1, "One date",
    IF( MIN( DROP(dates,1) - DROP(dates, -1) ) < 30,
     "Yes", "No" ) ) )

[#9]

@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)
)