Consolidation of 3 Lists in excel driven by custodian name

Okay I have these huge files that has three columns- one has the start # , second the end of that series #, and then the custodian- who it belongs to. there are 60,000 entries..
-------------------------------------------------------------------------------
Column A: Begin Number
Column B: End Number
Column C: Custodian
Column D: Page Count
-----------------------------------
Each Custodian in Column C (it is not unique - only the range will be (Column A-B #))
is associated with a range of values found in Column A to Column B (it stops where the custodian name changes and so forth...) The page count sum is the value sum of page count associated with the custodian for that range. I need to summarize the entire sheet to look like the results- where the range is specificied from Column A - to Column B - for that specific custodian - and the page range (this is also the numerical difference of value in Col A- Col B) +1.

Any suggestions would help.

I attempted to write a macro *Being unfamilar with VBA - but it was not successful since the # in Column A is the beginning of a series (with a custodian) & # in Column B is only where the custodian name before it changes. Please see the attached files -as they illustrate the idea better than I can explain it.


The attached file has the manual done-results and the second tab the data I am trying to make to look like the results.

My recommendation. Insert a helping column and label each item into a group. You first might want to extract Beginning Number and Ending Number from your page number columns using TextToColumns, then use the macro as an example to group them, then summarize use Pivot Table with Column Group, Custodian and Total Page #, and joint other columns using vlookup.

The code below is an example of how to group the

Oh sh*t. I didn't realize you have more than 1,000 items. Never mind what I said.

Kool. Yeah sorry I am not very helpful. Anyway, for the macro

the LR statement is locating the last row # of column 2

For i = 2 to LR is loop from Row2 to last row. What the loop does is it is looking at Location B & i (B1, B2, B3, B4, etc) and F & i (F1, F2, F3, F4), and if the item in the range meet the criteria or condition insert 1 in A & i (A1, A2, A3, A4, etc). The same goes to the next few IF statements

hi eeanil, please check attachment, press "Run" button, the data is supposed to be located on the sheet named "Sheet1" and be sorted as in the example.

Hi watersev,
Thanks for your time & file,but... I can see your code works for the example, but my original file has more than 60,000 rows of data- how do I get that macro to be implemented in such a situation ? Any ideas ? Thanks,...
Thanks for trying.

@watersev


I attempted to run the macro on the original data, but it says type mismatch error.

When I stepped into the code- it was these following lines the code was having trouble executing it seems:


result(j, 4) = CLng(myobj.Replace(result(j, 2), "")) - CLng(myobj.Replace(result(j, 1), "")) + 1

-- It seems to run when I cut up the file into two pieces and run separately, but otherwise the error comes when I run the macro on the entire file.
-- Because the file is too big I cannot upload it here. Anyway I can send you a copy- via email ?

Thank You.