Help creating a list that ignores NIL values

[CXsjr]

Hello all,

I have a series of values with narratives (columns A & B) which I want to be able transpose into another sheet but ignoring NIL values as follows:

John	235.78
Mike	0.00
Fred	0.00
Andy	167.98
Paul	0.00
Will	54.12

I'd like transpose the data to another sheet so it will appear on consecutive rows as follows:

John	235.78
Andy	167.98
Will	54.12

I'd like to use formulae rather than code if possible but I'm not sure if this is possible ....

As always, any help given would be greatly appreciated, thank you!

[ELeGault]

add a helper column into a leading column.

Formula would be something like this If you insert a new column into A

Please Login or Register  to view this content.

Then drag the formula down as far as your list.

You can now perform Lookups based on 1 2 3 4 5 and get the list without anyone with a "NIL" value in the C column.

Be sure to add a header to your A column when you insert it in "Helper" or something meaningful like Lookup Value. Otherwise that formula will start your count at -1

Cheers

[micope21]

Hi

You got 2 choice to choose

Large array formula

Sheet2
B2

Please Login or Register  to view this content.

This is array formula, Press same time Ctrl+Shift+Enter, NOT ENTER! then copy down
A2

Please Login or Register  to view this content.

Again array formula, Press same time Ctrl+Shift+Enter, NOT ENTER! then copy down

Index, Small and Row
B2

Please Login or Register  to view this content.

Array formula Ctrl+Shift+Enter then copy down and cross

See the file

Regard

[CXsjr]

add a helper column into a leading column.

I wanted a more 'one stop' solution; this has however fortuitously assisted me to improve the functionality of another workbook, thank you!

Hi

You got 2 choice to choose

Large array formula

Excellent solution thank you; I guessed it would involve array formulae but they're a little beyond my level. I've implemented this and adapted it to incorporate more information and it seems to be working a treat - thanks again!