# by row formula

Brass Contributor

# 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 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

8 Replies

# Re: by row formula

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.

# Re: by row formula

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.

# Re: by row formula

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.

# Re: by row formula

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

# Re: by row formula

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

# Re: by row formula

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" ) ) )``````

# Re: by row formula

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