How to automatically relist values in separate column

[ExcelStudentSG]

Good day dear members of ExcelForum,

ALBUM LINK:http://imgur.com/a/uXsa3#0

I have a question where I have a the table in columns AT and AU where values in AT are static and the values in AU column vary.

I would like to relist the values from AT and AU into AV and AW respectively by removing all entries that have a #NA value. What I need help with is the formula for the relisting in columns AV and AW starting from the value '10'.

Put in another way, the formula will run through the columns AT and AU and look for the non-#NA entries, pick them out and relist them in ascending order in columns AV and AW.

I have included 3 different scenarios where it should look like if successful.Thanks in advance for the help!

Please ignore rows 6 and 22.

[mahmud9935]

=if(AT>10,AU,0)

[ExcelStudentSG]

=if(AT>10,AU,0)

Hi Mahmud, thanks for your reply. I don't quite understand, where do I put your formula? What does it do?

Does AT refer to an entire column or am I supposed to type in the corresponding row numbers for it? e.g. AT1, AT2, etc.

Thanks!

[ExcelStudentSG]

Anyone else?