I have a Notepad file which has N number (very large) of data. I also have a workbook which has just a few data as of the Notepad data. There may be possibilities ...either if the available ID number in the Excel Workbook is more than one time in the Notepad data or not available at all, so I need to pull all those lines (records) completely from Notepad to the workbook's a sheet (for knowing the repetition of ID in the notepad).
Can anyone plz help me out with this urgent problem. I wanna run codes for this from MS Excel to Notepad, and just think that applying loops with count & find tricks can work in a faster way (...but don't know how to write codes for this situation).
P.S. - Below are the attached sample files.
Thanks in advance!
Last edited by SunOffice; 11-07-2011 at 05:03 PM.
Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!
...and one more thing I require is that If a list of a few ID numbers are available on the worksheet then how can I come to know about each ID number's repetition (frequency) count in the available large data of Notepad file?
Please see below attached file. I have just added a new column for the above situation or you can apply your ways.
P;S. »» I need to paste all the found records(lines) from Notepad to another worksheet, so that there is no need to open the notepad again and verify manually.
P;S. »» I need to paste all the found records(lines) from Notepad to another worksheet, so that there is no need to open the notepad again and verify manually.
...actually when I was working with you codes for my project; I just came to know these essential requirement. ...hope you/anyone can look into this situation, so tht I can successfully closed this thread from my end.
...Your programing tricks open a new channel for my vision ...thanks again for knowledge sharing.
Here is the updated attached, a sample text file. All the requirements are the same as said above in the posts or let me know if anything else is required.
I tried with below, did a bit modifications, but could not get success - not getting the complete found data lines, line by lines.
...There are more than a million data available in the notepad/text file(s). and I have an excel sheet with a few records around 500, which my team has to manually find/ search in the notepad one by one. If one record is found single/ multiple times then need to copy that whole text line and paste in another sheet as well for data verification & collation purpose.
1. the fastest search method in VBA I am familiar with is Instr.
2. so I put all the existing ID's in column2 into 1 textstring (c01), each ID separated by a pipeline |
3. I open the 'notepad' file, split all the rows and filter only those lines that contain a colon (only lines with a colon contain an ID in your notepad example): the result is a 1 dimensional array sn
4. Now we check each line from the notepad file (= each element in the array sn) to the string that contains all existing ID's., using Instr.
- To get the ID from each array-item we split each array-item by the tabdelimiter and take the first item from the splitting result. The first item is item 0.
- we look whether the string c01 contains that item; if not
* mark that item replacing it's value by "~~", so we can filter all "~~" items afterwards
or (method snb_2)
* filter the array sn by the not found ID, removing all items from sn that contain the not-found ID
- method 1: remove from array sn all items that contain "~~")
5. write the resulting array sn into sheet2
Yeah it is working fine here; let me try this on the main/ original text file at my work.
...and will let you know if anything goes wrong now.
...btw for below: Since there are many columns (15-20) with a particular order of data in the notepad, then what should I write instead of 4? or just go with this 4
LinkBack URL
About LinkBacks
Excel to find in Notepad and pull data lines
Register To Reply
Re: Excel to find in Notepad and pull data lines
Bookmarks