conditionally taking information from a cell

  • Thread starter Thread starter jcm2313
  • Start date Start date
J

jcm2313

Hi all,

I have an excel database I want to move the information from on
worksheet to another. example:

A1: AX0012
A2: BO0023
A3: BE2932
A4: TR3403

I want to take all the information from all the cells (A,B,C,D) i
which cell A1 contains AX and BE.

Can anyone help, do you need more info? Thanks in advanced
 
One way, if I've read your intent correctly ..:

Assuming the 4 cols (cols A to D) are
in Sheet1, data from row1 down, viz.

Col A will contain in A1:A4, for example:
A1: AX0012
A2: BO0023
A3: BE2932
A4: TR3403
Click to expand...

with other data in cols B to D

In Sheet1
--------------

In an empty col to the right, say in col F,

Put in F1:

=IF(A1="","",IF(AND(ISERROR(FIND("AX",A1)),ISERROR(FIND("BE",A1))),"",ROW())
)

Copy down as many rows as there is data in col A

In Sheet2
--------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)-1),"",OFFSET(S
heet1!$A$1,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)-1,COLUMN(A1)-1)
)

Copy A1 across to D1, then fill down
as many rows as there is data in col A in Sheet1

Sheet2 will return only the data from Sheet1's cols A to D
where col A in Sheet1 contains either "AX" or "BE"
(this is my reading of your objective ..)
 
In Sheet2heet1!$A$1,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)-1,COLUMN(A1)-1)
)[/QUOTE]

A minor "correction" to the above formula.

Put instead in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)-1,COLUMN(A1)-1))
 
Back
Top