Extracting data from one row across multiple columns

[Sarah]

I've got a worksheet containing the data extracted from
an electronic questionnaire, each set of answers are
contained in one column with each quesion answer on a
separate row. I need to find a way of looking up all the
answers from one particular question (i.e. the data from
the same row of multiple columns) and then extracting
these in the form of a list without duplicates. Can
anyone help?

 

[ojv]

Maybe the code below helps
Loops through each cell in the range rngAns and store
unique values in the vector Answers(). You can f.i. use
Application.InputBox to get the range you need.

Dim Answers(), cl as range, i as integer, j as integer
Dim inlist as boolean
i=1
set rngAns=range(cells(row,colStart),cells(row,colEnd))
redim answers(1 to rngAns.columns.count)
for each cl in rngAns
inlist=false
if i>1
for j=1 to i
if answers(j)="" then exit for
if answers(j)=cl.value then inlist=true:exit for
next
endif
if not inlist then answers(i)=cl.value:i=i+1
next
redim preserve answers(1 to i-1)