Formula to List Items Without Blanks

[Boris Aristotal]

Hello There,

I am a little stuck, so hopefully someone can help me. I have a list of alphanumeric items which do not populate every row. The items will constantly change, as they are in turn a formula to return a value based on what another cell shows. I am trying to get this list to appear in another column without the blanks in, but can't seem to find a relevant formula. The column with the information in is 999 rows, and the second list could be any combination of these 999.

So far I have the following:

An example list looks like the below (sorry, has been typed rather than an Excel link). This information is currently in DD3:DD999, and I am trying to get the second list to be in column DF without any blanks between the lines. Could someone possibly point me in the right direction please?

8D0001DB
8D0002AC

8D0003DB

8D0005ED


8D0008AC

 

[BeingYogi]

Do you want to remove the blank Or you also need unique values.

 

[northwolves]

Select the column and sort

 

[Boris Aristotal]

Each one will be a unique value and I am looking to remove the blanks and bunch them together. I need it to be an automated process as it is to be a quick reference point for anyone using the document.

 

[nuked]

Put this in DF3, enter it as an array formula (using Ctrl+Shift+Enter), and copy down:

=IF(ISERROR(SMALL(IF(DD3:DD999<>"",ROW(DD3:DD999)),ROW(DD3:DD999))),"",INDEX(DD3:DD999,MATCH(SMALL(IF(DD3:DD999<>"",ROW(DD3:DD999)),ROW(DD3:DD999)),IF(DD3:DD999<>"",ROW(DD3:DD999)),0)))

 

[Boris Aristotal]

Hi Nuked,

Thank you for your formula. I have put it in as an array formula, and although it has bunched them up it has also missed out a few of them. I now have a list that 8D0003, 7, 9, 12, 14, 19 (twice), 23, 25 etc, instead of the expected 8D0001, 2, 3, 4, 7, 8, 9, 11, 12. The list in DD is in itself the formula

=IFERROR(INDEX($A$1:$AM$999,TEXT(IF('Delivery Deviation'!$J$19=$E$3:$E$999,ROW($E$3:$E$999)-MIN(ROW($E$3:$E$999))+1,""),ROW(A3)),COLUMN($A$1)),"")

Could this have something to do with the error?