Performing a specific function repeatedly but with variable values each time

[injineers]

Hi. I am a summer student and looking for help on managing a large spreadsheet. The spreadsheet has over 200K rows and two dozen columns. My job is to:
1. There are two columns, one is called OCR code where we have repeating ID's and other column is called Minutes Count which is basically a number. I have to filter the OCR column for similar OCR codes(values) and then add their time(Minute Count) so that I get total minutes for a specific OCR code. After adding the min I will copy them into a new sheet with the code in one column, total minutes in another and number of repeatitions for that specific OCR code or count number in a separate column. Example:

OCR_Code; Minutes
54xg; 456
45fk; 65
23IS; 18
54xg; 971
45fk; 265
.
.
.

Now I've to filter 54xg so that I can only see it and its corresponding minutes. Then I will add up all min for 54xg. Then I will count the number of instances a 54xg has been repeated in spreadsheet(here its 2 but it can be in hundreds in my case). Last thing is to transfer all this data to a new sheet. And automatically repeat the process for other OCR codes like 45fk, 23IS etc...

My First Post

[FDibbins]

Hi and welcome to the forum

You dont need filters for this. Assuming your data looks like this...

Please Login or Register  to view this content.

On sheet2 A2, copied down, use this...
=IFERROR(INDEX(Sheet1!$A$2:$A$20,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$20),0,0),0)),"")
Then in sheet2 B2, copied down, use this...
=IF(A2="","",SUMIF(Sheet1!$A$2:$A$20,A2,Sheet1!$B$2:$B$20))

[injineers]

Thanks Ford for your quick response. You're right about how my data looks but I don't get the formulas you used specifically the Index one. All I need is to filter similar OCR Codes and add their time and also count their number of occurances. For example if 54xg occurs 7 times in the spreadsheet. Count should be 7, the minutes should be added to represent cumulative minutes Like

Essentially this is a repetitive process in which every ocr code is filtered and minutes and count is calculated and then we move to next ocr and next and next. Untill we fill the new sheet in the above fashion. Hopefully its concise yet elaborate this time! Thanks

[injineers]

Hi and welcome to the forum

You dont need filters for this. Assuming your data looks like this...

Please Login or Register  to view this content.

On sheet2 A2, copied down, use this...
=IFERROR(INDEX(Sheet1!$A$2:$A$20,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$20),0,0),0)),"")
Then in sheet2 B2, copied down, use this...
=IF(A2="","",SUMIF(Sheet1!$A$2:$A$20,A2,Sheet1!$B$2:$B$20))

Thanks Ford. I didn't tried your approach as I am able to do it via PivotTables. I'll play with it and let you know whether it works or not.

Thanks again
Abi Ilyas