| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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(c32000,"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. |
|
#2
|
|||
|
|||
|
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 <atyahoo<dotcom ---- "Tiziano" wrote in message ... 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(c32000,"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. |
|
#3
|
|||
|
|||
|
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(c32000,"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. |
|
#4
|
|||
|
|||
|
The X's in col. B are the result of an IF
formula, something like =if(c32000,"X",""), Oops, missed the significance of this part of your post earlier .. If you currently have in B1: =IF(C12000,"X",""), copied down just change the formula in B1 to : =IF(C12000,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 <atyahoo<dotcom ---- |
|
#5
|
|||
|
|||
|
Thanks for the help!
"Max" wrote in message ... The X's in col. B are the result of an IF formula, something like =if(c32000,"X",""), Oops, missed the significance of this part of your post earlier .. If you currently have in B1: =IF(C12000,"X",""), copied down just change the formula in B1 to : =IF(C12000,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 <atyahoo<dotcom ---- |
|
#6
|
|||
|
|||
|
Thanks for all your kind help!
"Aladin Akyurek" wrote in message ... 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(c32000,"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. |
|
#7
|
|||
|
|||
|
You're welcome !
Hope it worked for you -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Tiziano" wrote in message ... Thanks for the help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing Data | Excel Discussion (Misc queries) | |||
| Help with data not getting plotted | Charts and Charting in Excel | |||
| Entering data on template and then data going to a spreadsheet. | Excel Discussion (Misc queries) | |||
| Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
| Importing XML data from http request | Excel Discussion (Misc queries) | |||
Linear Mode
