Divide the row count by a column in Excel

[ebruarslann]

I will parse an XML file and export it to Excel. However, for large XML files that exceed Excel's row limit, I will split them into multiple Excel files based on the row count. Here’s the issue: the "Line Number Counter" column represents each entry. If I set the row limit to 300,000, and the last entry finishes on the 300,001st row, I want the new Excel file to start without splitting the entry. For example, if there are 3 rows for an entry starting after the 299,998th row and finishing on the 300,001st row, I want this entry to be written to the second Excel file. I need to automate this process using a macro. Could you help with that?

[Qiu · Accepted solution]

@ebruarslann 
I added a few more data entries and set the limit to 6.
We first count the total number for each ""Line Number Counter" then divide them into each output group as below.

[CoG]

@Qiu 

Very smart, very elegant solution!