Hello
I have an excel file (800,000+ rows) in the form (for example)-
a 9
a 7
a 9
b 10
b 32
b 2
c 5
c 3
c 7
I want to know if there is a way to quickly select all the rows that contain 'a' and export them to another excel file. Similarly, all the rows with 'b' would be exported to another excel file.
Is there a query that can solve this?
Please let me know how to do this.
Thank you
Create Pivot table and then from Report Filter click Show Report Filter Pages.
That will place you each letter (a,b,c) in it's own sheet.
Is this good enough?
To create workbook from each critertia you would need VBA
Never use Merged Cells in Excel
Hello, thank you for your reply, I have attached a small extract of the huge excel file that I have to work with.
As you can see, I would like all the rows that have 'Fuso-class battleship' to be in a new excel file. Similarly, 'Gifu, Gifu' and all its rows should be in another excel file.
Will pivot table work in this case?
bump bump bump
It's not that easy to bump so soon
Here, try this:
1. First define somewhere to save your files. Example is C:\Users\zbor\Documents\Excel forum\
2. Define first row for headers (names doesn't matter)
Last edited by zbor; 06-06-2013 at 03:35 PM.
Thank you so much for the reply once again, and I apologize for bumping too soon.
After changing the file output directory in the macro to the desktop and after pressing 'save each file' I get folders named as '3Gifu, gifu', '3Brandon Graham'
which is fine. But when I try to open them, I get a message -
'The file you are trying to open, '3Gifu, Gifu.xls' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?'
When I click open, I get an empty workbook. Please let me know if I'm doing something wrong.
I have also attached what my desired output should look like for two of the titles.
I really appreciate you taking time to help me with this problem.
I've edited mine post some 10 minutes after first one. did you get mine 2nd one?
For the error try change format into xlsx
Formula:
Please Login or Register to view this content.
Thank you so much that worked like a charm :D
Now I was trying to do the same for the original excel file which is 5.45 mb so I cannot attach it in this thread, so could you please walk me through on how you did the previous file that you attached?
I opened the original file and copy-pasted the code that you did. I would like to know what other steps have to be done.
That's it:
1. Add first row for title
2. Copy-paste code
3. set sheet names and folder path
4. run code
oh, yes, and I think I set to 1000 unique names. Do you have more than that?
I have 800 unique names so that is fine.
I'm not too clear about the steps-
So I first open the 5.45mb excel file, I set the first row for the titles,
copy and paste your code and run, but it does not work, i'm sure i missed something?
Last edited by kclalwani; 06-06-2013 at 04:52 PM.
Are your sheets named sheet1 where your data is stored and you have sheet2?
If yes, try to select some cell in sheet1 and run code.
Save your sheet as .xlsm
Also, it might take a while to do all that
The sheets are named as 'Sheet1' and 'Sheet2'.
After copy-pasting the code and running it, nothing happens. I selected the first few rows from sheet 1 and tried running it but still did not work.
Does it have anything to do with the 'save each file' button you placed in the folder you sent me?
Last edited by zbor; 06-06-2013 at 05:10 PM.
No. But you can insert some button on Sheet1 and assign macro and try. Let me meanwhile check the code.
Does it create list on Sheet2?
It does not create any list on Sheet2.
Remove On Error Resume Next from second macro.
What error does it have now?
Or just copy your data into mine workbook
I get -
Run-time error '1004':
This command requires at least two rows of source data. you cannot use the command on a selection in only one row.
When I copy my my data into your workbook and run the code, I only get the output for the 4 unique names that I sent you as a sample and not all the 800.
Actually, I do not get any output at all when I copy- paste my data into your workbook and run your code.
Last edited by kclalwani; 06-06-2013 at 05:40 PM.
If its not working out, then, is there a way I can send you the 5.45mb excel file? And then you can run the code? Maybe I must have missed a step because I still get error "Run-time error '1004' " That way It will be easier to find the fault.
Even after zipping the excel file and uploading in this thread I still get an error that its too large. Please let me know. I really appreciate the way you've been helping me with my problem
Last edited by kclalwani; 06-06-2013 at 05:54 PM.
Hello again zbor
I just wanted to let you know that I figured it out. When I'm copying my data to your file, I have to make sure that my workbook is in xlsm format before I copy it to your workbook.
I really appreciate you taking the time to help me. Thank you so much
I have marked this thread as 'solved'
Glad I've helped.
Limit for uploading files on the forum is 1MB.
But it's solved now so no need for that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks