Importing Data From Another Spreadsheet

I would like to import some data from another
spreadsheet. The spreadsheet that would supply
the data looks like this:

Column A Column B
-------- --------
A6520 04-02 X
1511 03-01-VT X
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

The X's in col. B are the result of an IF
formula, something like =if(c3>2000,"X",""),
etc., thus they will/will not be there depending
on the values in col. C.

The data in col. A should be imported into
the other spreadsheet only if an X is present
in col. B. Also, it should be imported in
such a manner as not to leave blank rows inbetween
each piece of data imported.

Therefore, the data imported into the other
spreadsheet should look like this:
A6520 04-02
1511 03-01-VT
BC6520 04-02-01
8951511 03-01-VTM

I would appreciate any suggestions as to how
to set up the formula to import the data.
Thanks.

One way..

Suppose this data is in Sheet1, cols A and B, row1 down

> A6520 04-02 X
> 1511 03-01-VT X
> S6520 00
> mtr-AAA-TR565
> BC6520 04-02-01 X
> D6520 00-ABC
> 8951511 03-01-VTM X

Use an empty col, col C?
Put in C1: =IF(B1="x",ROW(),"")
Copy down by as many rows as there is data in cols A and B

In Sheet2
-----------
Put in say, A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0)),"",INDE
X(Sheet1!A:A,MATCH(SMALL(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0)))

Copy down by as many rows as was done in col C in Sheet1

You'll get:

> A6520 04-02
> 1511 03-01-VT
> BC6520 04-02-01
> 8951511 03-01-VTM
(rest are blanks)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Tiziano" <[email protected]> wrote in message
news:[email protected]...
> I would like to import some data from another
> spreadsheet. The spreadsheet that would supply
> the data looks like this:
>
> Column A Column B
> -------- --------
>
> The X's in col. B are the result of an IF
> formula, something like =if(c3>2000,"X",""),
> etc., thus they will/will not be there depending
> on the values in col. C.
>
> The data in col. A should be imported into
> the other spreadsheet only if an X is present
> in col. B. Also, it should be imported in
> such a manner as not to leave blank rows inbetween
> each piece of data imported.
>
> Therefore, the data imported into the other
> spreadsheet should look like this:
> A6520 04-02
> 1511 03-01-VT
> BC6520 04-02-01
> 8951511 03-01-VTM
>
> I would appreciate any suggestions as to how
> to set up the formula to import the data.
> Thanks.
>
>

Option 1. A fast formula system

Let A1:B8 on Sheet1 house the sample you provided, including appropriate
labels:

{"Item","Include";
"A6520 04-02","X";
"1511 03-01-VT","X";
"S6520 00","";
"mtr-AAA-TR565","";
"BC6520 04-02-01","X";
"D6520 00-ABC","";
"8951511 03-01-VTM","X"}

The above is a way of displaying your sample. Note the labels Item and
Include in A1:B1.

Sheet1 (Source)

In C1 enter: 0 [ required ]

In C2 enter & copy down:

=IF(B2="X",LOOKUP(9.99999999999999E+307,$C$1:C1)+1,"")

Sheet2 (Destination)

In A1 enter:

=LOOKUP(9.99999999999999E+307,Sheet1!C:C)

In A2 enter: List [ just a label ]

In A3 enter & copy down:

=IF(ROW()-ROW(A$3)+1<=$A$1,LOOKUP(ROW()-ROW(A$3)+1,Sheet1!C:C,Sheet1!A:A),"")

Option 2. Advanced Filter

Sheet2 (Destination)

In A1 enter: Include [ the appropriate label from Sheet1, the source ]

In A2 enter: X [ the marker ]

Select A1:A2.
Fire up Data|Filter|Advanced Filter.
Click OK.
Check the option "Copy to another location".
Enter Sheet1!$A$1:$B$8 in the box for "List range".
Enter $A$1:$A$2 in the box for "Criteria range".
Enter $A$3 in the box for "Copy to".
Leave the "Unique records only" option unchecked.
Click OK.

Note. The labels Item and Include must be distinctly formatted, e.g., in
bold and italic, to allow Advanced Filter to distinguish between text
labels and text data.

Tiziano wrote:
> I would like to import some data from another
> spreadsheet. The spreadsheet that would supply
> the data looks like this:
>
> Column A Column B
> -------- --------
> A6520 04-02 X
> 1511 03-01-VT X
> S6520 00
> mtr-AAA-TR565
> BC6520 04-02-01 X
> D6520 00-ABC
> 8951511 03-01-VTM X
>
> The X's in col. B are the result of an IF
> formula, something like =if(c3>2000,"X",""),
> etc., thus they will/will not be there depending
> on the values in col. C.
>
> The data in col. A should be imported into
> the other spreadsheet only if an X is present
> in col. B. Also, it should be imported in
> such a manner as not to leave blank rows inbetween
> each piece of data imported.
>
> Therefore, the data imported into the other
> spreadsheet should look like this:
> A6520 04-02
> 1511 03-01-VT
> BC6520 04-02-01
> 8951511 03-01-VTM
>
> I would appreciate any suggestions as to how
> to set up the formula to import the data.
> Thanks.
>
>

> > The X's in col. B are the result of an IF
> > formula, something like =if(c3>2000,"X",""),

Oops, missed the significance of this part of your post earlier ..

If you currently have
in B1: =IF(C1>2000,"X",""), copied down

just change the formula
in B1 to : =IF(C1>2000,ROW(),"")
and copy down

The above formula replaces the previous
> Put in C1: =IF(B1="x",ROW(),"")

Then in Sheet2,

Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$B:$B,ROWS($A$1:A1)),Sheet1!$B:$B,0)),"",INDE
X(Sheet1!A:A,MATCH(SMALL(Sheet1!$B:$B,ROWS($A$1:A1)),Sheet1!$B:$B,0)))

and copy down, as before

(It's the same formula as previous, except pointing now
to Sheet1!$B:$B within the MATCH(SMALL(...)...) parts
instead of the previous Sheet1!$C:$C)

You'll get the desired results in col A
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Thanks for the help!


"Max" <[email protected]> wrote in message
news:OqQwdJ%[email protected]...
> > > The X's in col. B are the result of an IF
> > > formula, something like =if(c3>2000,"X",""),
>
> Oops, missed the significance of this part of your post earlier ..
>
> If you currently have
> in B1: =IF(C1>2000,"X",""), copied down
>
> just change the formula
> in B1 to : =IF(C1>2000,ROW(),"")
> and copy down
>
> The above formula replaces the previous
> > Put in C1: =IF(B1="x",ROW(),"")
>
> Then in Sheet2,
>
> Put in A1:
>
>
=IF(ISERROR(MATCH(SMALL(Sheet1!$B:$B,ROWS($A$1:A1)),Sheet1!$B:$B,0)),"",INDE
> X(Sheet1!A:A,MATCH(SMALL(Sheet1!$B:$B,ROWS($A$1:A1)),Sheet1!$B:$B,0)))
>
> and copy down, as before
>
> (It's the same formula as previous, except pointing now
> to Sheet1!$B:$B within the MATCH(SMALL(...)...) parts
> instead of the previous Sheet1!$C:$C)
>
> You'll get the desired results in col A
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

Thanks for all your kind help!

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Option 1. A fast formula system
>
> Let A1:B8 on Sheet1 house the sample you provided, including appropriate
> labels:
>
> {"Item","Include";
> "A6520 04-02","X";
> "1511 03-01-VT","X";
> "S6520 00","";
> "mtr-AAA-TR565","";
> "BC6520 04-02-01","X";
> "D6520 00-ABC","";
> "8951511 03-01-VTM","X"}
>
> The above is a way of displaying your sample. Note the labels Item and
> Include in A1:B1.
>
> Sheet1 (Source)
>
> In C1 enter: 0 [ required ]
>
> In C2 enter & copy down:
>
> =IF(B2="X",LOOKUP(9.99999999999999E+307,$C$1:C1)+1,"")
>
> Sheet2 (Destination)
>
> In A1 enter:
>
> =LOOKUP(9.99999999999999E+307,Sheet1!C:C)
>
> In A2 enter: List [ just a label ]
>
> In A3 enter & copy down:
>
>
=IF(ROW()-ROW(A$3)+1<=$A$1,LOOKUP(ROW()-ROW(A$3)+1,Sheet1!C:C,Sheet1!A:A),""
)
>
> Option 2. Advanced Filter
>
> Sheet2 (Destination)
>
> In A1 enter: Include [ the appropriate label from Sheet1, the source ]
>
> In A2 enter: X [ the marker ]
>
> Select A1:A2.
> Fire up Data|Filter|Advanced Filter.
> Click OK.
> Check the option "Copy to another location".
> Enter Sheet1!$A$1:$B$8 in the box for "List range".
> Enter $A$1:$A$2 in the box for "Criteria range".
> Enter $A$3 in the box for "Copy to".
> Leave the "Unique records only" option unchecked.
> Click OK.
>
> Note. The labels Item and Include must be distinctly formatted, e.g., in
> bold and italic, to allow Advanced Filter to distinguish between text
> labels and text data.
>
> Tiziano wrote:
> > I would like to import some data from another
> > spreadsheet. The spreadsheet that would supply
> > the data looks like this:
> >
> > Column A Column B
> > -------- --------
> > A6520 04-02 X
> > 1511 03-01-VT X
> > S6520 00
> > mtr-AAA-TR565
> > BC6520 04-02-01 X
> > D6520 00-ABC
> > 8951511 03-01-VTM X
> >
> > The X's in col. B are the result of an IF
> > formula, something like =if(c3>2000,"X",""),
> > etc., thus they will/will not be there depending
> > on the values in col. C.
> >
> > The data in col. A should be imported into
> > the other spreadsheet only if an X is present
> > in col. B. Also, it should be imported in
> > such a manner as not to leave blank rows inbetween
> > each piece of data imported.
> >
> > Therefore, the data imported into the other
> > spreadsheet should look like this:
> > A6520 04-02
> > 1511 03-01-VT
> > BC6520 04-02-01
> > 8951511 03-01-VTM
> >
> > I would appreciate any suggestions as to how
> > to set up the formula to import the data.
> > Thanks.
> >
> >

You're welcome !
Hope it worked for you
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Tiziano" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the help!