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