Extracting Excel data into new file

[Closed]
Report
Posts
3
Registration date
Sunday October 25, 2009
Status
Member
Last seen
October 26, 2009
-
 Janib Soomro -
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

7 replies

If you guys are looking for extracting data from any website in csv/excel format, then contact me. I am a professional data miner.

Regards,

Janib Soomro
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
Posts
3
Registration date
Sunday October 25, 2009
Status
Member
Last seen
October 26, 2009

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 https://www.pcreview.co.uk/threads/split-data-to-different-excel-files.3828059/ (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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
i have add text file data into sql and with add new column as add new text file data . how to do it into .net #
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
sorry my knowledge of SQL is nil

Hi

Please use this code, this will help u . change code parameters according to your need.
Private Sub cmdreports_Click()
Dim wbNew As Workbook
Dim thiswb As Workbook


'create folder to save files
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, 15) & " " & DateString
MkDir FolderName
i = 1
For i = 1 To 3
Set thiswb = ThisWorkbook
Workbooks.Add
ActiveSheet.Name = InputBox("Please Enter File Name for Report", "Report")
ActiveWorkbook.SaveAs (FolderName + "\" + ActiveSheet.Name + ".xls")
Set wbNew = ActiveWorkbook
'Depending on file file name report is generated
Select Case ActiveSheet.Name

Case "Ravinder" -- change report name here
thiswb.Sheets(1).Range("A1:c1").EntireColumn.Copy Destination:=wbNew.ActiveSheet.Range("A1")
ActiveWorkbook.Close Savechanges:=True

Case "Abc" ---change report name here
thiswb.Sheets(1).Range("c1:d1").EntireColumn.Copy Destination:=ActiveSheet.Range("A1") ----- instead of entirecolumn type your range
ActiveWorkbook.Close Savechanges:=True

Case Else
'This will delete the report when criteria is not matached
a = ActiveSheet.Name
MsgBox "This is not a valid Criteria for Generating Reports"
wbNew.Close
Kill (FolderName + "\" + a + ".xls")

End Select
Next i

MsgBox " Your reports are saved in" + " '" + FolderName + "'", vbOKOnly, "Thanks"


End Sub