Produce a specific list

[deanblew]

Hi all,

How do i produce a list of the referances in a specific column, under box 1 i need a list with no "-" and the same for the other columns, i cant use a filter as i loose some other entries.

I want to end up with Table B on the list atatchment

Thanks for any help.

Dean

[oeldere]

M5 =IFERROR(SMALL(F5:F31,ROW()-4),"") and drag across.

[AliGW]

This in M5 copied across and down:

=IFERROR(INDEX(F:F,SMALL(IF(F$5:F$31<>"-",ROW(F$5:F$31)),ROWS($A$1:A1))),"")

confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[deanblew]

Great works with the other formulas i have

[deanblew]

Hi,

thanks for the last, do you know how to combine all values from a number of columns in one column without including duplicates?

[Tony Valko]

Post some sample data and show us what results you expect.

[oeldere]

With the code below it can be done.

See the attached file.

It is appreciated if you comment on this solution.

Please Login or Register  to view this content.

[deanblew]

Hi, it took me a while but works, thanks lots

[oeldere]

@deanblew

To who are you replying since you get answers from several members.

[deanblew]

oeldere your Macro and i used the formula from AliGW

[oeldere]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.