Formula to generate Dynamic Unique List from another worksheet

Hey all,

I need to generate a unique list of values for a table I'm creating based on another worksheet's table. This seems so straightforward, yet for some reason it's giving me some trouble.

Attached is an example workbook, where "Order Details" is the table that I am looking to base the list off of (the Carton # column) and generate a dynamic unique list in the "Carton Info" worksheet, which will list those unique values in the Carton # column.

Here's what I've tried thus far:

COUNTIF to get a value of 1 for the first unique carton # in my table and a value of 0 for all repeated values:

=(COUNTIF('Order Details'!A$8:A8, 'Order Details'!A8)=1)*1

From there, I can count my number of unique values through a SUM function. From here, I tried the following and can only generate the first value before getting "#NUM!" errors:

=IF(ROWS(K$8:K8)<=$J$8, INDEX('Order Details'!$A$8:$A$27, SMALL(IF('Carton Info'!$H$8:$H$27=1, ROW('Carton Info'!$H$8:$H$27)-ROW('Carton Info'!$H$8)+1), ROWS(K$8:K8))), "")

I know my error comes in the bolded section of my formula, but I cannot figure out how to fix it at this point (been looking at it for too long). I'm sure there is a simple way to have Excel simply count up from 1 to a certain number in increments of 1 until it gives me the value of the SUM function (of unique values), however I would also like to see if there is a cleaner formula out there to do this.

Thanks for the push!

Have you confirmed the formula by holding CTRL and SHIFT keys down and then pressing ENTER, then copying down?

My apologies for forgetting to add in the attachment initially..

Ok, so for some reason when originally using CRTL+SHIFT+ENTER for an array formula it did not fix this, yet now it does..

So I guess this is solved, however, would someone take the time to explain to me how the part in bold works? I'm confused with the multiple ROW functions and how they work out..that was the part that I took from another example but couldn't quite grasp. The rest of the function I can understand.

Thanks again!

SMALL(IF('Carton Info'!$H$8:$H$27=1, ROW('Carton Info'!$H$8:$H$27)-ROW('Carton Info'!$H$8)+1), ROWS(K$8:K8))

This looks for the next available match in sequential order from top of list....

If there is a match to 1 in H8:H27, it returns the next smallest row number according to the K factor determined by ROWS(K$8:K8). ROWS(K$8:K8) equates to 1, as you drag down, it changes to ROWS(K$8:K9) which equates to 2 (for second smallest), etc....

This part: ROW('Carton Info'!$H$8:$H$27) just lists the row numbers in H8:H27, so {8;9;10;11;12;....;27}, but we need to offset this to start at one, so subtracting ROW('Carton Info'!$H$8) from each results in array {0;1;2;3;4.....19} and adding 1 initiates the range at 1 like: {1;2;3;4;5;....20}

so when the condition of 'Carton Info'!$H$8:$H$27=1 results in TRUE, then the ROW('Carton Info'!$H$8:$H$27)-ROW('Carton Info'!$H$8)+1 results in a mix of ROW numbers and FALSEs, and the SMALL chooses next smallest number based on the K factor.