Horizontal unique values

QuestionEdit

I hope you are well. My question is similar to one I have previously asked but hopefully it¡¯s more straightforward.

What I need is an array formula that returns a list of values but not blanks ¡®horizontally¡¯ from a row of unique values in another sheet.

I have used in the past this formula which works perfectly 'vertically':

=IF((ROW())<=SUM(1*(LEN(TRIM(Sheet1!$A$1:$AH$1))>0)),INDEX(Sheet1!$A$1:$AH$,SMALL(IF(LEN(TRIM(Sheet1!$A$1:$AH$1))<>0,ROW(Sheet1!$A$1:$AH$1)),ROW ()),1),"")

I've been trying the obvious:

=IF((COLUMN())<=SUM(1*(LEN(TRIM(Sheet1!$A$1:$AH$1))>0)),INDEX(Sheet1!$A$1:$AH$1,SMALL(IF(LEN(TRIM(Sheet1!$A$1:$AH$1))<>0,COLUMN(Sheet1!$A$1:$AH$1)),COLUMN()),1),"")

However, this only returns the first value but puts #ref for the next.

Do you have any ideas how I can amend this to work horizontally, across columns?

Kind Stuart

AnswerEdit

The description says the data is vertical and needs to be returned horizontally, but the formula doesn't seem to reflect this - my method to get a column of data from a horizontal row would be

=IF(SMALL(IF(ISBLANK($A$1:$AH$1),99999,COLUMN($A$1:$AH$1)),ROW()-3)=99999,"",INDEX($A$1:$AH$1,1,SMALL(IF(ISBLANK($A$1:$AH$1),99999,COLUMN($A$1:$AH$1)),ROW()-3)))

as an array formula - this could easily be amended to work the other way about by swapping rows and columns about - my example formula starts at row 4 hence the deduction of 3 from the row value. My email if you want to discuss this further (and allowing sharing of files) is aidan.heritage@virgin.net