Search : in
By :

Extracting Excel data into new file

Last answer on Oct 27, 2009 10:58:42 am GMT gloriaagustyaningrum, on Oct 26, 2009 3:51:36 pm GMT 
 Report this message to moderators

Hello,

I have an Excel like below. Project Code, Site, Donor and Partner are the heading. 

Project Code     Site        Donor         Partner
ECON100          Jakarta     Worldbank     Yayasan Serasi
HEAL230          Medan       JICA          Yayasan Serasi
INFR371          Bandung     APBN          Intekbis ITB


I need to copy rows containing Partner: Yayasan Serasi into new file named yayasanserasi.xls, and row containing Partner: Intekbis ITB into another separate file named intekbisitb.xls. Such row or rows on the each of new files must be under the same the heading like the original table:

In yayasanserasi.xls 

Project Code     Site        Donor         Partner
ECON100          Jakarta     Worldbank     Yayasan Serasi
HEAL230          Medan       JICA          Yayasan Serasi


and in intekbisitb.xls 

Project Code     Site        Donor         Partner
INFR371          Bandung     APBN          Intekbis ITB


I have like 2700 rows, so that doing copy-pasting seems too overwhelming. Please help me with this matter. Thank you and my apologies for my bad English.

Regards,
Gloria
Configuration: Windows XP
Safari 532.0

Best answers for « Extracting Excel data into new file » in :
Excel – How to extract partial data? ShowExcel – How to extract partial data? Issue Solution Note Issue I have an Excel spreadsheet that contains around 1500 html links. All the addresses start out common... i.e. http://www.frogs.com/. What I want to extract into another...
How to convert Excel into PDF? ShowHow to convert Excel into PDF? Here is a small tips about how to convert your excel files into PDF for your presentation. Step 1 PDF995 is software that gets installed on your computer which enables you to print any sources of document to...
Associating an application to a file extension ShowAssociating an application to a file extension Display all file extensions Modify an existing file association Create a new file association Manage the list of file extensions Display all file extensions Windows XP by default will mask...
Download Export Table To Text For Sql Server Show
Download Excel-MySQL converter ShowHenceforth with Excel-MySQL Converter, it is not necessary to be a developer to be able to convert Microsoft Excel data into MySQL database or vice versa. This program allows you to convert quickly your data without using other material. Depending...
Download Data Manager for Excel ShowHave you already thought that your Excel worksheet can be transformed into a true database? Now, it is possible thanks to Data Manager for Excel. Data Manager for Excel is an add-in for Microsoft Excel allowing to transform the worksheets into...
BinHex encoding ShowBinHex encoding BinHex encoding (a contraction of binary-to-hexadecimal) is a proprietary algorithm owned by Apple for converting 8-bit binary data into a 7-bit format. BinHex encoding, designed for Macintosh systems, is used to preserve file...
Spreadsheets - Data Entry ShowCell Content A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics: a type, which means the intrinsic type of the data. There are generally three types of values: numeric values, for example...
Ask your question Reply

1

venkat1926, on Oct 27, 2009 2:41:25 am GMT

You have two thousand rows. how many UNIQUE partners (that is how many partner files ) are necessary? the complexity or otherwise of the macro depends upon this.

also are the main file and partner files in the same path.

   
Reply to venkat1926

2

gloriaagustyaningrum, on Oct 27, 2009 3:37:51 am GMT

Dear venkat1926,

Unique partner is 240. There should be 240 new files created on the fly.

I got a macro that can be a similiar workaround at http://www.pcreview.co.uk/forums/thread-3828059.php (see post from someone named Joel). In my case, "Partner" (or "Department" in that example) is column G. I tried changing "F" in that example to "G" but no luck so far.

I checked my sheet and found that some of "Partner" are actually more than one, e.g. Intekbis ITB; Yayasan Nurani Dunia in a cell. I changed the ";" with "and" just to check if character ";" may be the problem but it didn't workout.

Yes, they are in same path.

Let me know what you think.

Thanks!
Gloria

   
Reply to gloriaagustyaningrum

3

 venkat1926, on Oct 27, 2009 10:58:42 am GMT

CONSIDER THESE POINTS

you have 240 unique names of partners. That means you have 240 workbooks in additon to the main database. If you keep all the destination files open the writing of the macro will be less complex
what the macro can do is
1. find the unique names of partners in column D in the main database using advance filter and park it somewhere
2. auto filter the data for each of these 240 names and copy the filtered data.
3. open that workbooks of that name and sheet no. 1 and paste the filtered data
4. save the file and clsoe it
5. loop it like this for all the 240 names. I do not know how long will it take because you are opening 240 files, copy paste data, save and close.

I do not know how long it will take. As far as I know copying to closed files is not possible.

It is not the macro creation is complex but running th macro is complex.

instead of 240 workbooks will it be possible to have one worksheet for each of the names in the main database. Then you need open only one file the main database.
AS you are using excel 2007
It is better not to have two partners names in ONE cell. It will make the solution more complex.

Take into considerations in view and see whether you can design the main database
less complex.

post your comments

   
Reply to venkat1926
Ask your question Reply
They need your help