Importing Data From Another Spreadsheet

T

Tiziano

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.
 
M

Max

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
Click to expand...

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
Click to expand...
(rest are blanks)
 
A

Aladin Akyurek

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.
 
M

Max

The X's in col. B are the result of an IF
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(),"")
Click to expand...

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
 
T

Tiziano

Thanks for the help!


Max said:
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

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
Click to expand...
 
T

Tiziano

Thanks for all your kind help!

Aladin Akyurek said:
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.
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Searching for Substrings Within Strings 8
Counting with multiple criteria in separate Columns 6
How to convert half hourly data into hourly 5
Mixed Data in column 5
Exported data pulling into another spreadsheet 6
Specific Date from a Range 3
Start and End date 2
Pivot Table? ID, Date, Value sorting 4

Top