Merge Rows with Connecting Dates by Account Number

[breynolds0431]

Hello. I have a data file that splits out account details on separate rows if there is a date range that crosses a month end. For example, if Account A had a date range from 03/01/2021 to 04/26/21, then the file will list that account detail out on two rows: one with the date range of 03/01/2021-03/31/2021 and the next on 04/01/2021-04/26/2021. These files come from a separate entity and I'm sure they have a reason for doing this, but I would like to find something that will go through the listing and identify duplicated account numbers and merge them on a new row while connecting the dates. This should only be done if the dates are true broken connections (i.e.; a range of 03/01/2021-03/31/2021 would not be connected with the same account's 04/05/2021-04/16/2021 date range). Example data is provided below. Thanks in advance!

Acct #	Date Begin	Date End
AA	20210301	20210331
AA	20210401	20210426
AB	20210318	20210329
AA	20210501	20210508

 

[vw412]

I assume what you want with the above data is to get a result with three rows, 2 rows for AA and 1 for AB. The first AA row would have Begin 20210301 and End 20210426. The second AA row would have Begin 20210501 and End 20210508. AB row would be unchanged.

what other data would need to be "merged"?

 

[breynolds0431]

Hi @vw412. Correct with the 2 AA rows (one merged and the one original with May dates) and 1 AB.

I'm so glad you asked this question. There would be one more field that would need to be merged or comma-separated concatenated. It is a code consisting of 1-2 digits (sometimes as only a number, sometimes alphanumeric in a letter, number format like "F1"). The codes may change for each applicable period break That is, if account # AC had three consecutive, broken date ranges, there may be a possibility of 3 different codes. I guess I'm not too concerned with merging these together if it's something that would prove difficult to do. It could be kept as a manual process.

Speaking of my manual process, I would copy the first row of the account needing merged and insert that in the above row. I would then update the Date End of that new row with the last occurrence of that account's Date End. And then look at the codes to make sure they are listed on that new row as well. Then the original rows are deleted. Hopefully that paints a better picture than my original post.

Thanks for your time!