$$Excel-Macros$$ Need Help Urgently on how to Parse data
I have an excel file with a list of column data. first of all, after the data is sorted based on the Col D, then I need a routine to check if any row data in Col D occurs more than once. If it occurs more than once.. For example: Column D has data on Row 2 and Row 3 same but the Date on Column I are different A B CD E F G H I akd oij4eij oin 01-1220 alkmd klmd Joe Bloe lkdlkjd 04/13/2012 adfl pojp ad 01-3123 aldad dfd Ben Janaklndl 01/22/2011 ioej oijdooij 01-3123 adfdf dfadSuzy J akddfd 02/12/2012 I need a macro that will run through the contiguous data and delete the ENTIRE row of data where the data between all occurence are old but keep the latest farthest date. So using the above example, I want to delete row 2 since the date is 1/22/2011. There are times when there are 3 or 4 occurences in the data so the determining factor of deleting the rows is based on the dates. After all the parsing andn sorting is done, I need it to sort the entire spreadsheet data based on the date in Col I Also I want the year of 2011 and 2012 separated either by separate sheets.. So in a nutshell it starts out with one sheet then ends up with two sheets one with data for 2011 and the other with data for 2012 Thanks.. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: Looking for most effecient way to convert lots of data
Hey Paul
Good work on the Phone number exercise. I was wondering if you don't mind
sharing the dummy *.txt file you created to test the program and also if you
could send the excel spreadsheet with the VBA code where you implemented
it. I will like to study the methodology or your approach. It was genius,
I think I can learn a thing or two from what you did.
Thanks
Mog
On Wed, Oct 7, 2009 at 10:16 AM, Paul Schreiner wrote:
> ok, since I haven't heard from you yet,
> I went ahead and created some "dummy" data.
> 10,000 phone numbers,
> 50 "features" (called "Feature_01", "Feature_02", ...)
> and created just under 200,000 records (196,810 actually)
> I "assumed" that the phone number and 'feature'
> were separated by a "tab"character.
>
> I also "assumed" that you put the features as "column Headings"
> in the sheet called "Summary"...
> ad you can see, I made a lot of assumptions,
> but, this code reads the 196,000 records, categorizes them,
> and updates the report in approximately 12 seconds...
>
> perhaps you can get enough info from what I've written
> to adapt it to your needs...
>
> hope this helps,
> Paul
>
> Option Explicit
> Public Const ForReading = 1, ForWriting = 2, ForAppending = 8
> Public Const CntFeatures = 60, MaxNums = 25
> Public Dict_Features, Col_Feature
> Public SummarySht
> Sub ImportData()
> Dim DataFile, RecCnt, stat, FeatureCnt
> Dim fso, f, Str, StrArray
> Dim ArrayFeature(MaxNums, CntFeatures)
> Dim Dict_PhNum, PhInx, R, C
> Dim tstart, tstop
> Dim tMin, tSec, tElapsed, msg
>
> tstart = Timer
> SummarySht = "Summary"
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set Dict_PhNum = CreateObject("Scripting.Dictionary")
> Set Dict_Features = CreateObject("Scripting.Dictionary")
> stat = Dict_PhNum.RemoveAll
> stat = Dict_Features.RemoveAll
> Load_Features
> RecCnt = 0
> PhInx = 1
> DataFile = "C:\temp\PhoneFeatures.txt"
> Set f = fso.OpenTextFile(DataFile, ForReading)
> Application.StatusBar = "Searching Phone Features"
> Do While Not f.AtEndOfStream
> RecCnt = RecCnt + 1
> If (RecCnt Mod 1000 = 0) Then Application.StatusBar = "Searching
> Phone Features: " & RecCnt
> Str = f.ReadLine
> StrArray = Split(Str, Chr(9)) 'Assumes phone/feature separated by
> "tab" character
> If (UBound(StrArray) > 0) Then
> If (Not Dict_PhNum.exists(StrArray(0))) Then
> PhInx = PhInx + 1
> Dict_PhNum.Add StrArray(0), PhInx
> ArrayFeature(PhInx, 1) = StrArray(0)
> End If
> If (Not Dict_Features.exists(StrArray(1))) Then
> Col_Feature = Col_Feature + 1
> ThisWorkbook.Sheets(SummarySht).Cells(1, Col_Feature) =
> StrArray(1)
> Dict_Features.Add StrArray(1), Col_Feature
> End If
> ArrayFeature(PhInx, Dict_Features.Item(StrArray(1))) = "X"
> End If
> Loop
> f.Close
> ThisWorkbook.Activate
> ThisWorkbook.Sheets(SummarySht).Select
> ThisWorkbook.Sheets(SummarySht).Range("A2:XY10").ClearContents
> Application.StatusBar = "Displaying Results"
> Application.ScreenUpdating = False
> For R = 2 To PhInx
> If (R Mod 250 = 0) Then Application.StatusBar = "Displaying
> Results: " & R & " of " & PhInx
> For C = 1 To Col_Feature
> If (ArrayFeature(R, C) <> "") Then
> ThisWorkbook.Sheets(SummarySht).Cells(R, C) = ArrayFeature(R, C)
> Next C
> Next R
> Application.ScreenUpdating = True
> msg = R & " Phone Numbers "
> msg = msg & Chr(13) & "from " & RecCnt & " Records"
> tstop = Timer
> tMin = 0
> tElapsed = tstop - tstart
> tMin = tElapsed \ 60
> tSec = tElapsed Mod 60
> msg = msg & Chr(13) & Chr(13)
> If (tMin > 0) Then msg = msg & tMin & " mins "
> msg = msg & tSec & " sec"
> MsgBox msg
> Application.StatusBar = False
> End Sub
> Sub Load_Features()
> Dim FeatureCnt, C
> FeatureCnt =
> Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(SummarySht).Range("A1:XY1"))
> For C = 2 To FeatureCnt
> If (Not
> Dict_Features.exists(ThisWorkbook.Sheets(SummarySht).Cells(1, C).Value))
> Then
> Dict_Features.Add ThisWorkbook.Sheets(SummarySht).Cells(1,
> C).Value, C
> End If
> Next C
> Col_Feature = C - 1 'Set to column of last feature
> End Sub
>
>
> --
> *From:* Paul
> *To:* MS EXCEL AND VBA MACROS
> *Sent:* Wednesday, October 7, 2009 8:36:19 AM
> *Subject:* $$Excel-Macros$$ Re: Looking for most effecient way to convert
> lots of data
>
>
> I think I know how I'd approach it.
> I deal with something similar.
> I use an array to store the values.
> But by the time you add a couple thousand entries to the array,
> it becomes very time consuming FINDING the array element that has the
> corre
$$Excel-Macros$$ Re: need help in excel formula
Sandeep I don't quite understand your problem, could you please restate it clearly. thanks Mog On Wed, Oct 7, 2009 at 9:36 AM, Sandeep wrote: > > > - Original Message - > *From:* G. Ram > *To:* excel-macros@googlegroups.com > *Sent:* Saturday, October 03, 2009 8:20 AM > *Subject:* $$Excel-Macros$$ need help in excel formula > > Dear all, > I need your help in formula, i need correct formula to get daily > total salary and ot amount.. > please help me.. i have send the attachment too.. > > Thank®ards, > Ram, > > > > > -- ** Omoghene Obahor www.obahor.com Cell: 214-493-7403 Fax: 866-338-1397 "As a Man Thinks So is He" ** --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: PDF password CRACKER
send it to me vivek I will help u crack it. On 9/30/09, vivek agrawal wrote: > Hi There.. > > I have downloaded a pdf file which is PASSWORD PROTECTED. > > CAn anyone PLEASE help crack the password. IF YES, then pls tell me so that > i can mail the pdf file to the concerned person. > > I would be very oblidged for the help. > > Thanks in advance > > > Thanks and Regards, > Vivek Agrawal > Skype ID - vivek.agrawal83 > > GoogleMoonlight.com - Saving energy-Save Earth > > > > -- ** Omoghene Obahor www.obahor.com Cell: 214-493-7403 Fax: 866-338-1397 "As a Man Thinks So is He" ** --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ if statements to compare string
Hello Team, I have all the variables in the cells stored in an array format and I am iterating through the entire value to detect where "Jan" is . How do I do an If Statement check for this. thanks *Project Num* *Project Title* *Project Type* *Impl Date* *Jan* *Feb* *Mar* *Apr* *May* *Jun* *Jul* *Aug* -- ** Omoghene Obahor www.obahor.com Cell: 214-493-7403 Fax: 866-338-1397 "As a Man Thinks So is He" ** --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
