selecting a row element based on a condition

  • Thread starter Thread starter Agrawal
  • Start date Start date
A

Agrawal

Hi,
I want to get some values from sheet1 to sheet2.
The conditions are:
Get the value from sheet1 to 'column A in sheet2' (or any other column).
If Column B in sheet1 has this element say "elm1", then get the value (which
is a numerical value, say "200") from the column "C" in the same row in the
same sheet1.
How to do it? I am really new as I have never done any VBA programming
before. But as I came across this problem, I realized there is no escape
from it.

I hope I am clear in stating the problem here. If not please let me know.

Thanks,
Srikant.
 
try this. put this formula where you want the value from
sheet 2 column C.

= Vlookup("elm1",$B$1:$C$50,2,0)

below is a bit of explanation on the formula. someone
posted it here earlier. i am sorry i dont remember the
name of the person.

VLOOKUP(arg1,arg2,arg3,[arg4]) has 4 arguments (last one
is optional).

arg1 = cell to be matched.
arg2 = Matrix where the first column has the cell to be
matched.
arg3 = Column to be returned.
arg4 = true or false (0 or 1) if the list is ordered or
not (default=1).

Now if you have a wider matrix, you just tell wich column
number to be
returned in arg3. If description2 is in column 10 of the
matrix, replace
the 2 in the previous formula by a ten. Each formula will
return just
one field, so if you want more than one description you'll
need to copy
the formula and change it to suit your needs.
 
One way:

Assume you have
in Sheet1, in B2:C4,
the sample data:

Code Val
hop1 80
elm1 200
skip1 70

In Sheet2
-----------
Suppose you have in col A,
data in A2 down as follows:

Code
elm1
hop1
skip1
etc

Put in B2:

=IF(ISNA(MATCH(TRIM(A2),Sheet1!B:B,0)),"",OFFSET(Sheet1!$C$1,MATCH(TRIM(A2),
Sheet1!B:B,0)-1,0))

Copy B2 down col B

Col B will extract the corresponding values from col C of Sheet1
for matching items listed in col A

Non-matching items listed in col A will return blanks [""] in col B
 
Thankyou all you guys for quick help.
I am now facing a strange problem.
The values in the sheet1 which I have named as 'parameters' are below (I am
not showing entire sheet here. It is pretty long :)):

/NOPR
*SET BX1 -1.292927501624E-07
*SET BX2 -1.141633954439E-07
*SET BX3 -1.127943903568E-07
*SET BY1 4.467111249673E-08
*SET BY2 1.242211009761E-08
*SET BY3 2.058738127965E-09
*SET BZ1 9.958343153752E-08
*SET BZ2 6.274505098194E-09
*SET BZ3 -3.132556459805E-08
*SET CRACK 2.663850000000E-03
*SET CX1 -2.641880672886E-07
*SET CX2 -2.313901629704E-07
*SET CX3 -2.299511040808E-07
*SET CY1 8.692051321624E-08
*SET CY2 2.452136873548E-08
*SET CY3 3.358494300625E-09
*SET CZ1 1.881823140769E-07
*SET CZ2 1.150936825102E-08
*SET CZ3 -6.567685052698E-08
*SET DISCR 1.400000000000E-02
*SET DX1 -2.319996384970E-07
*SET DX2 -2.305784363904E-07
DX3 -2.275705711076E-07
*SET DX4 -2.311028533460E-07
*SET DX5 -2.284933228005E-07
*SET DY1 4.440036180011E-08
*SET DY2 2.057977463140E-08
*SET DY3 -5.875278474995E-09
*SET DY4 2.851123741177E-08
*SET DY5 1.191354952814E-08
*SET DZ1 8.919140074728E-08
*SET DZ2 -5.337422745664E-09
*SET DZ3 -9.782311158106E-08
*SET DZ4 2.975426185995E-08
*SET DZ5 -3.997589395777E-08


This is the value I am getting on sheet2 using the formula
=IF(ISNA(MATCH(A1,parameters!B:B,1)),"",OFFSET(parameters!$C$1,MATCH(A1,para
meters!B:B,1),0))
When I drag this formula down in column I get all the values correctly but
the first value doesn't show!
I have used the same formula in other sheets to extract the value from
"parameters" sheet. But strangely I am unable to get BX1.
BX1
BY1 4.467111249673E-08
BZ1 9.958343153752E-08
CX1 -2.641880672886E-07
CY1 8.692051321624E-08
CZ1 1.881823140769E-07
DX1 -2.319996384970E-07
DY1 4.440036180011E-08
DZ1 8.919140074728E-08


Any idea why am I getting blank value even if there is a match available!

thanks
Agrawal.

Max said:
One way:

Assume you have
in Sheet1, in B2:C4,
the sample data:

Code Val
hop1 80
elm1 200
skip1 70

In Sheet2
-----------
Suppose you have in col A,
data in A2 down as follows:

Code
elm1
hop1
skip1
etc

Put in B2:

=IF(ISNA(MATCH(TRIM(A2),Sheet1!B:B,0)),"",OFFSET(Sheet1!$C$1,MATCH(TRIM(A2),
Sheet1!B:B,0)-1,0))

Copy B2 down col B

Col B will extract the corresponding values from col C of Sheet1
for matching items listed in col A

Non-matching items listed in col A will return blanks [""] in col B

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Agrawal said:
Hi,
I want to get some values from sheet1 to sheet2.
The conditions are:
Get the value from sheet1 to 'column A in sheet2' (or any other column).
If Column B in sheet1 has this element say "elm1", then get the value (which
is a numerical value, say "200") from the column "C" in the same row in the
same sheet1.
How to do it? I am really new as I have never done any VBA programming
before. But as I came across this problem, I realized there is no escape
from it.

I hope I am clear in stating the problem here. If not please let me know.

Thanks,
Srikant.
Click to expand...
Click to expand...
 
Hi,
It seems that BX1 on the two places were not the same. When I copied and
pasted BX1 from sheet1 (sheet1 is called 'parameters) to sheet2, the
incorrect value of BX1 showed. It seems BX1 was having spaces to the right,
which when I deleted worked fine. I don't know what to say but other values
in the column 'B' of sheet1 is having the same kind of spaces but they are
working absolutely fine.
Now the value of BX1 is incorrect because it is taking value of BX2 after
using the formula =OFFSET(parameters!$C$1,MATCH(A1,parameters!B:B,1),0)
where as this formula works fine for other values.

when I use =OFFSET(parameters!$C$1,MATCH(A1,parameters!B:B,1)-1,0), it works
for BX1 but not for others!

any idea guys?
thankyou for being patient with me.
-agrawal.

Agrawal said:
Thankyou all you guys for quick help.
I am now facing a strange problem.
The values in the sheet1 which I have named as 'parameters' are below (I am
not showing entire sheet here. It is pretty long :)):

/NOPR
*SET BX1 -1.292927501624E-07
*SET BX2 -1.141633954439E-07
*SET BX3 -1.127943903568E-07
*SET BY1 4.467111249673E-08
*SET BY2 1.242211009761E-08
*SET BY3 2.058738127965E-09
*SET BZ1 9.958343153752E-08
*SET BZ2 6.274505098194E-09
*SET BZ3 -3.132556459805E-08
*SET CRACK 2.663850000000E-03
*SET CX1 -2.641880672886E-07
*SET CX2 -2.313901629704E-07
*SET CX3 -2.299511040808E-07
*SET CY1 8.692051321624E-08
*SET CY2 2.452136873548E-08
*SET CY3 3.358494300625E-09
*SET CZ1 1.881823140769E-07
*SET CZ2 1.150936825102E-08
*SET CZ3 -6.567685052698E-08
*SET DISCR 1.400000000000E-02
*SET DX1 -2.319996384970E-07
*SET DX2 -2.305784363904E-07
DX3 -2.275705711076E-07
*SET DX4 -2.311028533460E-07
*SET DX5 -2.284933228005E-07
*SET DY1 4.440036180011E-08
*SET DY2 2.057977463140E-08
*SET DY3 -5.875278474995E-09
*SET DY4 2.851123741177E-08
*SET DY5 1.191354952814E-08
*SET DZ1 8.919140074728E-08
*SET DZ2 -5.337422745664E-09
*SET DZ3 -9.782311158106E-08
*SET DZ4 2.975426185995E-08
*SET DZ5 -3.997589395777E-08


This is the value I am getting on sheet2 using the formula
=IF(ISNA(MATCH(A1,parameters!B:B,1)),"",OFFSET(parameters!$C$1,MATCH(A1,para
meters!B:B,1),0))
When I drag this formula down in column I get all the values correctly but
the first value doesn't show!
I have used the same formula in other sheets to extract the value from
"parameters" sheet. But strangely I am unable to get BX1.
BX1
BY1 4.467111249673E-08
BZ1 9.958343153752E-08
CX1 -2.641880672886E-07
CY1 8.692051321624E-08
CZ1 1.881823140769E-07
DX1 -2.319996384970E-07
DY1 4.440036180011E-08
DZ1 8.919140074728E-08


Any idea why am I getting blank value even if there is a match available!

thanks
Agrawal.

Max said:
One way:

Assume you have
in Sheet1, in B2:C4,
the sample data:

Code Val
hop1 80
elm1 200
skip1 70

In Sheet2
-----------
Suppose you have in col A,
data in A2 down as follows:

Code
elm1
hop1
skip1
etc

Put in B2:
Click to expand...
=IF(ISNA(MATCH(TRIM(A2),Sheet1!B:B,0)),"",OFFSET(Sheet1!$C$1,MATCH(TRIM(A2),
Sheet1!B:B,0)-1,0))

Copy B2 down col B

Col B will extract the corresponding values from col C of Sheet1
for matching items listed in col A

Non-matching items listed in col A will return blanks [""] in col B

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Agrawal said:
Hi,
I want to get some values from sheet1 to sheet2.
The conditions are:
Get the value from sheet1 to 'column A in sheet2' (or any other column).
If Column B in sheet1 has this element say "elm1", then get the value (which
is a numerical value, say "200") from the column "C" in the same row
Click to expand...
in
the
same sheet1.
How to do it? I am really new as I have never done any VBA programming
before. But as I came across this problem, I realized there is no escape
from it.

I hope I am clear in stating the problem here. If not please let me know.

Thanks,
Srikant.
Click to expand...
Click to expand...
Click to expand...
 
..
=IF(ISNA(MATCH(A1,parameters!B:B,1)),"",OFFSET(parameters!$C$1,MATCH(A1,para
meters!B:B,1),0))
The example set-up given earlier presumed row1 contained col headers,
with data from row2 down in both Sheets 1 and 2

But there was a slight typo (my apologies .. )
in the description line: > in Sheet1, in B2:C4,
which should have read: > in Sheet1, in B1:C4,

Ok, since your data starts in *row1* instead of row2
the MATCH part of the formula in OFFSET()
just needs to be amended slightly by subtracting 1 from it,
that is, using your adaptation of the formula:

Instead of: ... MATCH(A1,parameters!B:B,1) ...
Use: ... MATCH(A1,parameters!B:B,1)-1 ...

So try in say B1:

=IF(ISNA(MATCH(TRIM(A1),parameters!B:B,1)),"",OFFSET(parameters!$C$1,MATCH(T
RIM(A1),parameters!B:B,1)-1,0))

and copy down ,as before.

It should work correctly now

Suggest to retain the TRIM() given in the original formula as well

TRIM() will improve robustness in the matching
by removing any leading, trailing or in-between-words spaces (if any)
in the lookup values in col A in Sheet2

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
Agrawal said:
Hi,
It seems that BX1 on the two places were not the same. When I copied and
pasted BX1 from sheet1 (sheet1 is called 'parameters) to sheet2, the
incorrect value of BX1 showed. It seems BX1 was having spaces to the right,
which when I deleted worked fine. I don't know what to say but other values
in the column 'B' of sheet1 is having the same kind of spaces but they are
working absolutely fine.
Now the value of BX1 is incorrect because it is taking value of BX2 after
using the formula =OFFSET(parameters!$C$1,MATCH(A1,parameters!B:B,1),0)
where as this formula works fine for other values.

when I use =OFFSET(parameters!$C$1,MATCH(A1,parameters!B:B,1)-1,0), it works
for BX1 but not for others!

any idea guys?
thankyou for being patient with me.
-agrawal.

Agrawal said:
Thankyou all you guys for quick help.
I am now facing a strange problem.
The values in the sheet1 which I have named as 'parameters' are below (I am
not showing entire sheet here. It is pretty long :)):

/NOPR
*SET BX1 -1.292927501624E-07
*SET BX2 -1.141633954439E-07
*SET BX3 -1.127943903568E-07
*SET BY1 4.467111249673E-08
*SET BY2 1.242211009761E-08
*SET BY3 2.058738127965E-09
*SET BZ1 9.958343153752E-08
*SET BZ2 6.274505098194E-09
*SET BZ3 -3.132556459805E-08
*SET CRACK 2.663850000000E-03
*SET CX1 -2.641880672886E-07
*SET CX2 -2.313901629704E-07
*SET CX3 -2.299511040808E-07
*SET CY1 8.692051321624E-08
*SET CY2 2.452136873548E-08
*SET CY3 3.358494300625E-09
*SET CZ1 1.881823140769E-07
*SET CZ2 1.150936825102E-08
*SET CZ3 -6.567685052698E-08
*SET DISCR 1.400000000000E-02
*SET DX1 -2.319996384970E-07
*SET DX2 -2.305784363904E-07
DX3 -2.275705711076E-07
*SET DX4 -2.311028533460E-07
*SET DX5 -2.284933228005E-07
*SET DY1 4.440036180011E-08
*SET DY2 2.057977463140E-08
*SET DY3 -5.875278474995E-09
*SET DY4 2.851123741177E-08
*SET DY5 1.191354952814E-08
*SET DZ1 8.919140074728E-08
*SET DZ2 -5.337422745664E-09
*SET DZ3 -9.782311158106E-08
*SET DZ4 2.975426185995E-08
*SET DZ5 -3.997589395777E-08


This is the value I am getting on sheet2 using the formula
Click to expand...
=IF(ISNA(MATCH(A1,parameters!B:B,1)),"",OFFSET(parameters!$C$1,MATCH(A1,para
meters!B:B,1),0))
When I drag this formula down in column I get all the values correctly but
the first value doesn't show!
I have used the same formula in other sheets to extract the value from
"parameters" sheet. But strangely I am unable to get BX1.
BX1
BY1 4.467111249673E-08
BZ1 9.958343153752E-08
CX1 -2.641880672886E-07
CY1 8.692051321624E-08
CZ1 1.881823140769E-07
DX1 -2.319996384970E-07
DY1 4.440036180011E-08
DZ1 8.919140074728E-08


Any idea why am I getting blank value even if there is a match available!

thanks
Agrawal.
Click to expand...
=IF(ISNA(MATCH(TRIM(A2),Sheet1!B:B,0)),"",OFFSET(Sheet1!$C$1,MATCH(TRIM(A2),
Sheet1!B:B,0)-1,0))

Copy B2 down col B

Col B will extract the corresponding values from col C of Sheet1
for matching items listed in col A

Non-matching items listed in col A will return blanks [""] in col B

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Hi,
I want to get some values from sheet1 to sheet2.
The conditions are:
Get the value from sheet1 to 'column A in sheet2' (or any other column).
If Column B in sheet1 has this element say "elm1", then get the value
(which
is a numerical value, say "200") from the column "C" in the same row in
the
same sheet1.
How to do it? I am really new as I have never done any VBA programming
before. But as I came across this problem, I realized there is no escape
from it.

I hope I am clear in stating the problem here. If not please let me know.

Thanks,
Srikant.
Click to expand...
Click to expand...
Click to expand...
 
A further correction:

You neeed to change the "1" back to the original "0" inside MATCH for
*exact* match
(besides subtracting "1" from the MATCH)

....
Instead of: ... MATCH(A1,parameters!B:B,1) ...
Use: ... MATCH(A1,parameters!B:B,0)-1 ...

So try in say B1:

=IF(ISNA(MATCH(TRIM(A1),parameters!B:B,0)),"",OFFSET(parameters!$C$1,MATCH(T
RIM(A1),parameters!B:B,0)-1,0))
 
Back
Top