I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
.....???
--
Thanks and have a good day

Ruth

You will need to supply a bit more information if you want a specific
solution, but by concatenating A and B together (in a newly inserted
column C on the sheet you have) then you could make use of VLOOKUP. It
might be better to set this up on a new sheet, where A2 and B2 contain
the sets of criteria you are interested in, then if your original
sheet is called Sheet1 you would have something like this in C2 of the
new sheet:

=VLOOKUP($A2&$B2,Sheet1!$C$2:$Y$1000,COLUMN()-1,0)

The formula can be copied across to X2 to return the data which
matches. You can also put other criteria in A3/B3 etc down these
columns, and copy the formula down.

Hope this helps.

Pete

On Apr 27, 11:30 am, RWilliams
wrote:
I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth

Thanks - between your reply and BJ's I now have my resolution - is there any
way i can use Conditional Formatting to hide the 'not applicable' responses?
--
Thanks and have a good day

Ruth


"Pete_UK" wrote:

You will need to supply a bit more information if you want a specific
solution, but by concatenating A and B together (in a newly inserted
column C on the sheet you have) then you could make use of VLOOKUP. It
might be better to set this up on a new sheet, where A2 and B2 contain
the sets of criteria you are interested in, then if your original
sheet is called Sheet1 you would have something like this in C2 of the
new sheet:

=VLOOKUP($A2&$B2,Sheet1!$C$2:$Y$1000,COLUMN()-1,0)

The formula can be copied across to X2 to return the data which
matches. You can also put other criteria in A3/B3 etc down these
columns, and copy the formula down.

Hope this helps.

Pete

On Apr 27, 11:30 am, RWilliams
wrote:
I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth

Do you mean that you get #N/A errors showing up where there is no
match? If so, then you can trap this by modifying the formula along
the lines:

=IF(ISNA(VLOOKUP( ... ),"",VLOOKUP( ... ))

Basically, this means if there is an error then return "" (blank
cell), otherwise do the Vlookup. You could make "" into something more
meaningful if you wish, like "not present".

Hope this helps.

Pete

On Apr 30, 3:16 pm, RWilliams
wrote:
Thanks - between your reply and BJ's I now have my resolution - is there any
way i can use Conditional Formatting to hide the 'not applicable' responses?
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
You will need to supply a bit more information if you want a specific
solution, but by concatenating A and B together (in a newly inserted
column C on the sheet you have) then you could make use of VLOOKUP. It
might be better to set this up on a new sheet, where A2 and B2 contain
the sets of criteria you are interested in, then if your original
sheet is called Sheet1 you would have something like this in C2 of the
new sheet:

=VLOOKUP($A2&$B2,Sheet1!$C$2:$Y$1000,COLUMN()-1,0)

The formula can be copied across to X2 to return the data which
matches. You can also put other criteria in A3/B3 etc down these
columns, and copy the formula down.

Hope this helps.

Pete

On Apr 27, 11:30 am, RWilliams
wrote:
I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -

#N/A is exactly what i want to avoid .... your suggestion is great but does
not seem to work with my formula =VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE)

Should your version look like:
=IF(IsNA(VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE), "",(VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE))
because this returns the standard Error message and highlights the "" as the
first error

Any thoughts on what i am doing wrong?
--
Thanks and have a good day

Ruth


"Pete_UK" wrote:

Do you mean that you get #N/A errors showing up where there is no
match? If so, then you can trap this by modifying the formula along
the lines:

=IF(ISNA(VLOOKUP( ... ),"",VLOOKUP( ... ))

Basically, this means if there is an error then return "" (blank
cell), otherwise do the Vlookup. You could make "" into something more
meaningful if you wish, like "not present".

Hope this helps.

Pete

On Apr 30, 3:16 pm, RWilliams
wrote:
Thanks - between your reply and BJ's I now have my resolution - is there any
way i can use Conditional Formatting to hide the 'not applicable' responses?
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
You will need to supply a bit more information if you want a specific
solution, but by concatenating A and B together (in a newly inserted
column C on the sheet you have) then you could make use of VLOOKUP. It
might be better to set this up on a new sheet, where A2 and B2 contain
the sets of criteria you are interested in, then if your original
sheet is called Sheet1 you would have something like this in C2 of the
new sheet:

=VLOOKUP($A2&$B2,Sheet1!$C$2:$Y$1000,COLUMN()-1,0)

The formula can be copied across to X2 to return the data which
matches. You can also put other criteria in A3/B3 etc down these
columns, and copy the formula down.

Hope this helps.

Pete

On Apr 27, 11:30 am, RWilliams
wrote:
I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -

Hi Ruth,

Sorry, I missed an internal close-bracket on my last post. Your
formula should be:

=IF(ISNA(VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE)) ,"",VLOOKUP($A$38&$B
$38,$C$26:$G$34,2,FALSE))

All one formula - be wary of line breaks.

Hope this helps.

Pete

On May 1, 10:01 am, RWilliams
wrote:
#N/A is exactly what i want to avoid .... your suggestion is great but does
not seem to work with my formula =VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE)

Should your version look like:
=IF(IsNA(VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE), "",(VLOOKUP($A$38&$B$38,$*C$26:$G$34,2,FALSE))
because this returns the standard Error message and highlights the "" as the
first error

Any thoughts on what i am doing wrong?
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
Do you mean that you get #N/A errors showing up where there is no
match? If so, then you can trap this by modifying the formula along
the lines:

=IF(ISNA(VLOOKUP( ... ),"",VLOOKUP( ... ))

Basically, this means if there is an error then return "" (blank
cell), otherwise do the Vlookup. You could make "" into something more
meaningful if you wish, like "not present".

Hope this helps.

Pete

On Apr 30, 3:16 pm, RWilliams
wrote:
Thanks - between your reply and BJ's I now have my resolution - is there any
way i can use Conditional Formatting to hide the 'not applicable' responses?
--
Thanks and have a good day

Ruth

"Pete_UK" wrote:
You will need to supply a bit more information if you want a specific
solution, but by concatenating A and B together (in a newly inserted
column C on the sheet you have) then you could make use of VLOOKUP. It
might be better to set this up on a new sheet, where A2 and B2 contain
the sets of criteria you are interested in, then if your original
sheet is called Sheet1 you would have something like this in C2 of the
new sheet:

=VLOOKUP($A2&$B2,Sheet1!$C$2:$Y$1000,COLUMN()-1,0)

The formula can be copied across to X2 to return the data which
matches. You can also put other criteria in A3/B3 etc down these
columns, and copy the formula down.

Hope this helps.

Pete

On Apr 27, 11:30 am, RWilliams
wrote:
I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

one way would be to enter your pair criteria in columns A abd B in a new
sheet (Sheet2)
In C1 enter
=SUMPRODUCT(--(Sheet1!A$1:A$64000=A1),--(Sheet1!B$1:B$64000=B1)*ROW(A$1:A$64000))
and copy down as far as you need
In D1 enter
=OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4)
copy over to AC1 and down as far as you need.

"RWilliams" wrote:

I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth

Thanks - between your reply and Pete's I now have my resolution - is there
any way i can use Conditional Formatting to hide the 'not applicable'
responses?
--
Thanks and have a good day

Ruth


"bj" wrote:

one way would be to enter your pair criteria in columns A abd B in a new
sheet (Sheet2)
In C1 enter
=SUMPRODUCT(--(Sheet1!A$1:A$64000=A1),--(Sheet1!B$1:B$64000=B1)*ROW(A$1:A$64000))
and copy down as far as you need
In D1 enter
=OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4)
copy over to AC1 and down as far as you need.

"RWilliams" wrote:

I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth

where does the "Not applicable" come in. Do you want to hide the data on
Sheet1?
or is "not applicable" one of the possible responses in sheet 2

you could use conditional format with text color white if equals "Non
applicable or you could change the formula to
=if(OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4)="Non
applicable","",OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4))

"RWilliams" wrote:

Thanks - between your reply and Pete's I now have my resolution - is there
any way i can use Conditional Formatting to hide the 'not applicable'
responses?
--
Thanks and have a good day

Ruth


"bj" wrote:

one way would be to enter your pair criteria in columns A abd B in a new
sheet (Sheet2)
In C1 enter
=SUMPRODUCT(--(Sheet1!A$1:A$64000=A1),--(Sheet1!B$1:B$64000=B1)*ROW(A$1:A$64000))
and copy down as far as you need
In D1 enter
=OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4)
copy over to AC1 and down as far as you need.

"RWilliams" wrote:

I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
....???
--
Thanks and have a good day

Ruth