You should be able to do that with a variation of the formula I suggested if
you sort the data appropriately.
--
David Biddulph

"Marie Bayes" wrote in message
...
Hi David

I love what you've done there, however, i'm sorry but it's not what I
requi
I have, in column G, used filter special to create a list of unique
reference numbers from column A, I have then done the same with column B,
but
transposed these so that the unique 'questions' now appear in columns H to
AI, what I need is to lookup the 'answers' from Column C, that relate to
the
reference number G1 and the question H1, then I1, then J1 etc
Here's a simpler example of what I have in the spreadsheet A:C
A B C
Ref1 Q1 AnswerABC
Ref2 Q1 AnswerDEF
Ref1 Q2 AnswerGHI
Ref3 Q1 AnswerJKL
Ref1 Q3 AnswerMNO
Ref2 Q2 AnswerPQR
Ref3 Q3 AnswerSTU

Then I want to see this like so:

H I J K
REFER Q1 Q2 Q3
Ref1 AnswerABC AnswerGHI AnswerMNO
Ref2 AnswerDEF AnswerPQR
Ref3 AnswerJKL AnswerSTU

I've produced Column H, my unique reference nos, I've produced I1:AI1, my
questions, now I need to match up the references and questions to the
answers, phew, I think I might have just managed to explain it! I hope
that
makes sense and thanks for listening!
--
Marie Bayes


"David Biddulph" wrote:

If I understand correctly what you want, sort your 3 columns (ABC)
together
by column A, so that you have your 17 sets of identical column A values
in
consecutive rows, with the 17 sets of column B questions and column C
answers alongside them.
If what you want is to see the column A reference (A1) in D1 then the
first
column B question (B1) in E1, followed by its answer (C1) in F1, then the
second question (B2) in G1, and its answer (C2) in H1, and so on, use the
following formulae.
In D1
=OFFSET($A$1,(ROW(A1)-1)*17,0)
In E1
=OFFSET($B$1,(COLUMN(E1)-COLUMN($E$1))/2+(ROW(A1)-1)*17,MOD(COLUMN(E1)-COLUMN($E1),2))
Copy E1 across as far as AL1, which should be the column for answer C17
Then copy D1:AL1 down for as many rows as there are sets of questions.
--
David Biddulph

"Marie Bayes" wrote in message
...
Hi David

That's almost what I require, except I think I need it to do a lookup.
Let
me explain in a bit more detail! Column A has a reference number,
column
B
has a question and column C has the answer to the question in Column B.
Column A reference number refers to a set of data, so for each
reference
number in Column A there are 17 Questions in Column B, all with a
unique
answer in Column C. I'm trying to transpose the data so that I get one
set
of data in one row. I'm hoping you can help as this spreadsheet is
huge!!!

Here's an example of the data:
COLUMN A
COLUMN B COLUMN C
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Accommodation type
required Twin
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Any special
dietary
requirements No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Any special
dietary
requirements No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 County Midlothian
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 County Midlothian
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Date of Birth
DATE1
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Date of Birth
Date2
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you have any
disabilities or medical conditions that we should be aware of No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you have any
disabilities or medical conditions that we should be aware of No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you require any
special access No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you require any
special access No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Email email
address 1
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Email email
address 2
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Emergency contact
details Cont 123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Emergency contact
details Cont 124
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First Name
Patricia
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First Name
Rosaleen
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First name as in
passport Patricia Anne
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First name as in
passport Rosaleen
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home Address
Address
1
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home Address
Address
2
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home contact
number Number 123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home contact
number Number 124
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Known as Patti
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Known as Rosie
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Mobile Number
Another
Number 123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Mobile Number
Another
Number 124
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Nationality
British
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Nationality
British
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Expiry
date Oct 2016
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Expiry
date May 2014
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Number
Pass
No
123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Number
Pass
No
124



--
Marie Bayes


"David Biddulph" wrote:

If you are just looking at an individual row, and not producing sums,
then
it sounds as if you just want
=IF(AND(A1=42,B1=99),C1,"") or
=IF(AND(A1="value1",B1="value2"),C1,"") or
=IF(AND(A1=D$1,B1=E$1),C1,"")

In each case I have assumed that you want a blank result if the
conditions
aren't satisfied. If you want a different response, put it instead of
the
"" towards the end of the IF(...) formula.
--
David Biddulph

"Marie Bayes" wrote in message
...
Hi David

I'd like to jump in on this answer if I may? I want something
similar
to
Edinburgh except the value I want to return from column C is a text
value.
Is there a formula that I can use that will do this for me, similar
to
sumproduct but obviously not producing sums...???
--
Marie Bayes


"David Biddulph" wrote:

With that formula, you would need to have defined names for value1
and
value2. What John presumably intended you to do was to replace
value1
and
value2 in the formula either by numbers which you were looking for,
or
by
text strings, or by a reference to the cells in which the values
you
are
looking for would be found.

So perhaps
=SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or
=SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100))
or
=SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100))
--
David Biddulph

"Edinburgh" wrote in message
...
Hi David,

I realise it is unsatisfactory and I apologise. I'm not a
technically
minded person so this is all very mind-boggling to me!

I have a sheet of data which details all the hours recorded by
all
employees
who each have a 'grade' attached to them and their time is also
recorded
against the projects they are working on in that particular
period.
What
I
wanted was a formua that allowed me to return the total of hours
worked
by
project but also by grade.

I tried the exact formula John suggested
=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned, but it only
returned
#N/A.

I seem to have found a suitable solution that should allow me to
get
the
results that I want, but thank you for your time anyway.

Edinburgh

"David Biddulph" wrote:

With all due respect, "it didnt work" isn't a desperately useful
description
of your problem, and (short of clairvoyance) it doesn't give
contributors
to
the group a great deal of hope of helping you to identify what
you've
done
wrong.

Exactly what formula did you use? What data values were in the
cells
leading into that formula? What result did you get? What result
did
you
expect?
--
David Biddulph

"Edinburgh" wrote in
message
...
Thank you John C. Unfortunately it didnt work? I'm not sure
why
not?
Do
you have any other ideas??

"John C" wrote:

Try sumproduct.

=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned.
--
John C


"Edinburgh" wrote:

Hello!

I need to write a formula that allows me to look up two
values
in
a
series
of vertical lists and then returns a total value (sumif)
from
the
same
table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether
if
there
is
such
a beast?

Thanks