I just uploaded a file called PhoneFeatures.zip into the files area.
However, the Excel file is an Excel2007 file.
What version are you using?
Paul
On Oct 8, 2:03 am, Mog Obahor <oba...@gmail.com> wrote:
> 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 <schreiner_p...@att.net>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 = 250000
> > 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:XY100000").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 <schreiner_p...@att.net>
> > *To:* MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > *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
> > correct entry to modify!
> > so, I use a Dictionary Object to store the array index...
>
> > Can you give me a list of "features" you're reporting and what order
> > you want them in?
> > I think I can generate a list of random phone numbers to use in some
> > sample data.
>
> > Or, better yet.. can you send me a file in which you've replaced the
> > phone numbers
> > with some random numbers?
>
> > I'd be glad to take a look and "play"..
>
> > Paul
> > On Oct 6, 9:11 pm, bruce <goo...@johnsonclan.net> wrote:
> > > I am creating a program that does daily QA of our products. I work for
> > > a phone company and we sell differnt lines, with differnt options
> > > What I need to do is to convert one speadsheet where the features of
> > > the phone lines are in a list, one feature per row
>
> > > Simplified input file
> > > PhoneNum
> > > 555-1111 Caller ID
> > > 555-1111 Voice Mail
> > > 555-1111 Call Waiting
> > > 555-1111 Speed Dial
> > > 555-2222 Caller ID
> > > 555-2222 Call Forwarding
> > > 555-2222 Call Waiting
>
> > > The output I need is to be 1 row per phone number, with each feature
> > > checked in it own column
> > > Desired output would be
> > > PhoneNum CallerID VoiceMail CallWaiting SpeedDial
> > > CallForwarding
> > > 555-1111 Yes Yes Yes
> > > Yes No
> > > 555-2222 Yes No Yes
> > > No Yes
>
> > > I have over 200K row of input, each phone may be upwards of 40
> > > features each
> > > I recieve about 600 new entries a day
>
> > > this is a very simplified example, as I need to deal with the account
> > > numbers, each account can have upto about 32 phone lines (each row
> > > will be its own phone number), and the source data is broken into 6
> > > differnt files, each with its own key, that needs to be related to a
> > > differnt key/file) These different files have the different "feature
> > > sets" (for security reasons, none of these files have the phone
> > > number, so I still need to pull that from a DIFFERENT file, and then
> > > cross reference)
>
> > > There are, between yellow page features, phone feature, directory
> > > assist listing info, there are about 100 items that each number COULD
> > > have any selection of (I will need to build in error correcting, as
> > > some feature will force other feature OFF, but that is a seperate item
> > > unto itself)
>
> > > What would be the most effecient way to handle this? A top down from
> > > the SOURCE, processing each line one by one, or getting a list of the
> > > phone numbers, and then from the desired output, find the phone, and
> > > desired feature to check for.
>
> > > I think the first way would be the best way, but I am not sure I would
> > > need to process each source, line by line, and then doing a look of
> > > the phone number in its speardsheet, then do a lookup in the output
> > > file.I think with the fact of the cross referencing going on, option 2
> > > might be easier, but I would be doing lots of vlookup/matches, and
> > > creating "key fields" to search on.
>
> > > Are there other ways I might consider?
>
> > > Thanks
> > > Mctabish
>
> --
> ******************************************************
> Omoghene Obahorwww.obahor.com
> Cell: 214-493-7403
> Fax: 866-338-1397
> "As a Man Thinks So is He"
> ******************************************************- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
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
-~----------~----~----~----~------~----~------~--~---
