Array of cell row numbers based on first occurrences

Hello again excel community!

I manage a spreadsheet that calculates various metrics off of tasks ordered by row and pertaining to specific order numbers. Due to their being multiple tasks per order number, someone decided it was a good idea to copy the total order hours into each tasks row for the order it pertains to.

Now, I need to somehow sum up all the hours for all tasks however I cannot simply use the sum command since almost all the hours are repeated in multiple rows.

Specifically, I need a formula that will create an array of all row numbers containing the first occurrences of every order number. That a way I can use the index formula to retrieve the hours and simply sum them up.

I tried using the SMALL function however due to the large number of orders the spreadsheet became over saturated with formulas.
I also tried using ROW with UNIQUE as the target however ROW doesn't seem to be able to handle arrays. If it did champagne would rain from the heavens. Lol.

Any help would be appreciated.

Thanks,

excelhelp.jpg

Is there always an empty row above it?

That is only a recent thing I'm afraid. When orders were first recorded by management they were stacked with no spaces for about the first 100,000 lines or so. Only within the last 2,000 limes did they start doing that. (easier for them to sort mentally I guess)

if you are looking for the rows to sum then, given O365, you could use something like below:

=SUM(FILTER(C2:C1001,A2:A1001<>A1:A1000))
or
=LET(a,CHOOSE({1,2},A2:A1001,C2:C1001),SUM(INDEX(UNIQUE(a),0,2)))

same principle can be applied if you really need the actual rows, i.e.:

FILTER(ROW(A2:A1001),(A2:A1001<>A1:A1000))

all of the above assume first row of data is row 2; modify ranges per real-life requirements

Thank you- this is now solved - with a bit of tweaking!

The formula you provided was also returning rows with blanks. I added a filter to remove those results. I also replaced the ranges with the column titles since these are in tables (Makes it stupid easy for management when all they have to do when they run out of rows is drag the end of the table down)

See attached image and excel sheet.

Thanks again!

Attachment 774445

Ah, too slow. If data in F36:F46 for example I had:

=MATCH(FILTER(UNIQUE(F36:F46),UNIQUE(F36:F46)<>""),F:F,0)

Feels like there should be a way to exclude blanks built in to the UNIQUE function, it always feels messy using FILTER.