Searching Data in Rows/Columns

[aernst]

Hi

(A)
does anyone know how to search for the text content of a cell through
column and then return the value/content of the cell of a certain othe
column of the same row where the text content was found?
e.g.

A1 B1 C1 D1
2 013004T XY
3 092004TP YZ
4 012004TP ZY
5 052004P YX
6 092004TP YY [FORMULA]
7 072004TP ZZ

6) and return the value of that row in column C (C3=YZ)."
NOTE: There might be more than 1 row with the string '092004TP' i
column A - I would need the sum of all results in column C where th
FORMULA finds this string.

(B)
I also have a similar problem with another worksheet:
It would require a formula which searches through a whole column fo
certain names, e.g. "Donna", and then returns the value of anothe
column in the same row where it found "Donna"; the value, again, is no
in a column directly adjacent to the column where "Donna" would b
found.
This is fairly identical to problem (A), except that the search strin
would be unique (only 1 result), and it doesn't have to take int
account (and disregard) if the string was found in the same row of th
FORMULA (the search column would be in a different worksheet from th
FORMULA worksheet).

Thanks for any suggestions!

Cheers
from New Zealand

Andrea

 

[Max]

-----
(A) - One way using SUMIF()
-----

NOTE: There might be more than 1 row with the string '092004TP' in
column A - I would need the sum of all results in column C where the
FORMULA finds this string.

Going by the above ..

Suppose you have in Sheet1, from row2 down, the sample table

013004T xx 1
092004TP xx 2
012004TP xx 3
052004P xx 4
092004TP xx 5
072004TP xx 6
etc

where

col A = strings
col B = "xx" (not relevant here)
col C contains numbers to be summed

In a new Sheet2, say
-----------------------------
and you have a unique list of strings
listed in col A, A2 down

Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!C:C)
Copy B2 down

Col B will return the sum of *all* the numbers in col C of Sheet1
for the corresponding string in col A

For the sample table in Sheet1:
If A2 in Sheet2 contains the string : 092004TP
B2 will return "7" (= 2 + 5)

----
(B) - One way using OFFSET(..MATCH(..)..)
-----
Assume you have in Sheet3,
unique names listed in col A,
with col *C* containing the target contents for extract
(data from row2 down)

John.......Text1
Donna.....Text2
etc

In a new Sheet4, say
-----------------------------
you have the names in col A,
data from row2 down

Donna
John
etc

Put in B2:

=OFFSET(Sheet3!$A$1,MATCH(TRIM(A2),Sheet3!A:A,0)-1,2)

Copy B2 down

Col B will extract "Text2" for "Donna", "Text1" for "John", etc

The rightmost fig: "2" in the OFFSET formula above
is the cols param (Note: the height and width params are omitted)
which tells the function to go 2 cols to the right
of the anchor cell's col (anchor cell is Sheet3!$A$1)
i.e. go to col C and extract the corresponding value there

If the target col is col D, just change the fig to "3", and so on ..

If you need error-trapping to cater for any unmatched cases,
the formula in B2 would be:

=IF(ISNA(MATCH(TRIM(A2),Sheet3!A:A,0)),"-",OFFSET(Sheet3!$A$1,MATCH(TRIM(A2)
,Sheet3!A:A,0)-1,2))

where any unmatched names in col A will return "-" in col B
instead of "#NA"s

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi

(A)
does anyone know how to search for the text content of a cell through a
column and then return the value/content of the cell of a certain other
column of the same row where the text content was found?
e.g.

A1 B1 C1 D1
2 013004T XY
3 092004TP YZ
4 012004TP ZY
5 052004P YX
6 092004TP YY [FORMULA]
7 072004TP ZZ

6) and return the value of that row in column C (C3=YZ)."
NOTE: There might be more than 1 row with the string '092004TP' in
column A - I would need the sum of all results in column C where the
FORMULA finds this string.

(B)
I also have a similar problem with another worksheet:
It would require a formula which searches through a whole column for
certain names, e.g. "Donna", and then returns the value of another
column in the same row where it found "Donna"; the value, again, is not
in a column directly adjacent to the column where "Donna" would be
found.
This is fairly identical to problem (A), except that the search string
would be unique (only 1 result), and it doesn't have to take into
account (and disregard) if the string was found in the same row of the
FORMULA (the search column would be in a different worksheet from the
FORMULA worksheet).

Thanks for any suggestions!

Cheers
from New Zealand

Andreas