Hello. Have a look at the attached file. Note that the formula in cells F3 and below are array formulae (validate with Ctrl+Shift+Enter)
HTH Daniel > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel- > mac...@googlegroups.com] On Behalf Of bruce > Sent: mercredi 7 octobre 2009 03:11 > 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 -~----------~----~----~----~------~----~------~--~---
Looking for most effecient way to convert lots of data.xls
Description: MS-Excel spreadsheet
