Hi Mctabish,
Define header to your data and simply create a pivot table from that.
PhoneID on Row, function on Column and function on Data.
You will get a table as below.
Count of Function
Function
Model
Call Forwarding
Call Waiting
Caller ID
Speed Dial
Voice Mail
Grand Total
555-1111
1
1
1
1
4
555-2222
1
1
1
3
Grand Total
1
2
2
1
1
7
Now replace 1 with Yes..
Let me know if it helps...
Thanks and Regards,
Upendra Singh
+91-9910227325
-----Original Message-----
From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com]
On Behalf Of bruce
Sent: Wednesday, October 07, 2009 6:41 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Looking for most effecient way to convert lots of
data
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
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
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
-~----------~----~----~----~------~----~------~--~---
