Merge Rows with Connecting Dates by Account Number

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
73
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 BeginDate End
AA2021030120210331
AA2021040120210426
AB2021031820210329
AA2021050120210508
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
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

Board Regular
Joined
Feb 15, 2013
Messages
73
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!
 

Forum statistics

Threads
1,136,803
Messages
5,677,823
Members
419,722
Latest member
Rizzol

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top