Extracting data from one row across multiple columns to produce a list

[Guest]

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?

 

[Paul Lautman]

Are you saying that you want to transpose the results from columns to rows?

If so then use Copy and Paste Transpose, or use the TRANSPOSE function.

 

[Guest]

Transpose doens't work. I've got the answers to one
particular question all in the same row but each answer
is in a separate column, I want to extract all of the
answers and put them into one column without duplicates.

 

[Paul Lautman]

I'm afradi that you are still not being very clear.

What is it about transpose that doesn't work?
Where are the duplicates that you wish to remove?

 

[Guest]

Cut and Paste Transpose works, but I cant use this as I
need the extracted data to update as and when I add
further sets of questionnaire answers to the original
data. I tried to use the Transpose function in the cell
I want the list to appear and then selected the row of
data I want to copy but it comes back as #VALUE!

A small example of what I'm working with:

A B C D
OH Mgr Site Mgr OH Mgr HR Supervisor

The entry in each column represents an individuals answer
for the same question, they are all on the same row just
in different columns. I want to look up all the answers
for that particular question, remove the duplicates and
then put them in a list in one cell on another worksheet.
By removing the duplicates I mean I don't want the list
to just be a copy of the whole row, for example in the
above example I want the list to read as:

OH Mgr, Site Mgr, HR Supervisor

Not: OH Mgr, Site Mgr, OH Mgr, HR Supervisor

 

[Paul Lautman]

Well, I guess we're getting there slowly!

You would do better to give a fuller explanation of the circumstances up
front.

Now, are you saying that columns A & D always contain the same data, or is
this just the case for a particular row?

The TRANSPOSE function works fine for me, so I can't see why you would have
an error. Maybe you could post an example somewhere on a web site.

If you are after a function that will show you what you seem to be asking
for, then I think you will be out of luck.

You will have to process the data somehow maybe using Filters and/or a
macro.

This MAY require use of pasting Transpose (if filters are to be used) or may
not (if a macro will examine all the entries).