Dynamic formula

[Matt_Chelmsford]

Hi all,

I have an ever growing list of orders with the order numberbeing in column D. Sometimes an order will have 1 line, sometimes it may have10!

What I need on a second sheet is a dynamic formula whichwill pick up all the order numbers and just display them once on this secondarysheet

Unfortunately, pivot tables will not suffice in thisinstance

Appreciate any Help given

Regards

Matthew

 

[James006]

Hi,

Take a look at the step-by-step explanation for the Array Formula you need ... :wink:

[h=2]How To Dynamically Extract A List Of Unique Values From A Column Range[/h]https://www.extendoffice.com/documents/excel/4032-excel-dynamic-list-of-unique-values.html

Hope this will help

 

[Aladin Akyurek]

In A2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$D$2:$D$1000,SMALL(IF(FREQUENCY(IF(Sheet1!$D$2:$D$1000<>"",MATCH(Sheet1!$D$2:$D$1000,Sheet1!$D$2:$D$1000,0)),ROW(Sheet1!$D$2:$D$1000)-ROW(INDEX(Sheet1!$D$2:$D$1000,1,1))+1),ROW(Sheet1!$D$2:$D$1000)-ROW(INDEX(Sheet1!$D$2:$D$1000,1,1))+1),ROWS($A$2:A2))),"")