EPTExcel Pros Please Help! - Page 2
| Blogs > Golgotha |
|
Glacierz
United States1245 Posts
| ||
|
Glacierz
United States1245 Posts
On December 16 2011 04:36 mrafaeldie12 wrote: for(int i = 0;i<cells.length();i++){ for(int j=0;j<cells.length();j++){ if(name[i].trim() == name[j].trim() && phone[i].trim() == phone[j].trim()){ //delete the row corresponding to name[j] } } } I don't know how to make macros in excel but I hope that helps  Your inner loop should be for(int j = i+1;...) not j=0, using it as it is is gonna remove every entry. | ||
|
OpticalShot
Canada6330 Posts
1) start recording 2) do the action you want to do 3) stop recording 4) check macro Alternatively, you can google for similar macros and you'll find a few good ones. | ||
|
MisterD
Germany1338 Posts
 | ||
|
infinity21
Canada6683 Posts
2) Remove duplicates 3) Add a column with the function =COUNTIF(A:A,A2) where 2 is the row number 4) Filter for the new column for values >=2 If there's entries for 3+ phone numbers, you're going to have to append the numbers to a different column If you're lucky and there's only a few rows with >=2 phone #s, then you can do it manually without having to code in VBA. On December 16 2011 04:45 MisterD wrote: set up a mysql database with phpmyadmin, export table as csv, import table in phpmyadmin, do your modifications through SQL queries, export as csv, import to excel sheet. If you are familiar with mysql/phpmyadmin, that's probably the easiest way if it's just a one time deal. If either of these conditions is false though, ignore this post I don't think someone who doesn't know VBA would know SQL lol | ||
|
MoreFaSho
United States1427 Posts
file = open(filename) people = {} for line in file: person, number = line.split() if person not in people.keys(): people[person] = {} people[person][number] = 0 # I'm just being lazy with sets for person in people.keys(): res = "person" for number in person[people].keys(): res += "\t%s" % number print res Just pipe that out into a csv and you can copy right back into excel, ezpz. edit: damnit, it's not preserving my spacing, but if you quote my post it shows the spacing. | ||
|
zeks
Canada1068 Posts
On December 16 2011 04:49 infinity21 wrote: 1) Sort 2) Remove duplicates 3) Add a column with the function =COUNTIF(A:A,A2) where 2 is the row number 4) Filter for the new column for values >=2 If there's entries for 3+ phone numbers, you're going to have to append the numbers to a different column If you're lucky and there's only a few rows with >=2 phone #s, then you can do it manually without having to code in VBA. I don't think someone who doesn't know VBA would know SQL lol Me until this work term bahaha - Richie | ||
|
Glacierz
United States1245 Posts
http://en.wikipedia.org/wiki/Pivot_table | ||
|
Golgotha
Korea (South)8418 Posts
edit: it will just be a small part of the info, to keep things simple. | ||
Chill
Calgary25993 Posts
Repeat. | ||
|
infinity21
Canada6683 Posts
haha <3 On December 16 2011 05:03 Golgotha wrote: Thanks guys, I feel like I have a direction now. If I uploaded the same file but with fake information (I cannot upload actual contact information), could you guys write a macro for it so I can see what you guys did for this specific problem? I can write macros for other simple functions but the ones listed here are over my head. edit: it will just be a small part of the info, to keep things simple. Try what I wrote and if you get a reasonable number of rows (~100?) with 2+ phone numbers, I suggest just doing it manually. If it turns out that there's 500+, post a fake file here and I'll help you out after I'm done with my exam tonight. I'm strangely helpful during exam times... | ||
|
goldrush
Canada709 Posts
| ||
|
Golgotha
Korea (South)8418 Posts
http://www.mediafire.com/?jzzta80m4t973fg I took the original file and cut it down a bunch and changed all the names. But the template we used to map the fields are all the same! Some of the fields have nothing in them but that is how it really is; the whole project was a bit messy. In this example there is only one person who is a duplicate. Before I delete the duplicate, I must combine all the other information listed under his name from both rows into one. For example: We can see that he works for two different companies, University of Polo and The Jukebox. I want to combine them both into one field before I delete the duplicate. So in the end the finished part should look something like this: http://i.imgur.com/uT1Qr.png I hope this more clearly presents what I am trying to do. Thank you! I am hoping I can learn how to automate this process, otherwise I will just do everything manually. But please I don't fancy going over a 6,000 person list | ||
|
myzael
Poland605 Posts
On December 16 2011 04:59 MoreFaSho wrote: file = open(filename) I was going to recommend python too, but you were faster. Also, [ code] tag is available on TL | ||
|
MaRiNe23
United States747 Posts
| ||
|
Glacierz
United States1245 Posts
On December 16 2011 06:01 MaRiNe23 wrote: It's SO ironic that I was going to create a thread about Excel yesterday but thought it would get closed so I hope the OP doesn't mind me asking this question here but I just finished a course in VBA programming this semester and was wondering if I could put on my resume that I am proficient in excel or is the "programming" side of excel different from what they need in an office. I see so many times in job descriptions of clerical jobs that they want someone who is proficient in word,excel blah blah but for excel I'm guessing they are stricly talking about working with spreadsheets and not the coding aspect of it? Proficiency in excel does not mean VBA. If you know how to work a pivot table you should be able to put both Excel and VBA on your resume with no problem. | ||
|
Glacierz
United States1245 Posts
| ||
|
MaRiNe23
United States747 Posts
On December 16 2011 06:10 Glacierz wrote: Proficiency in excel does not mean VBA. If you know how to work a pivot table you should be able to put both Excel and VBA on your resume with no problem. If I learn that through google or youtube do you think I'll be okay to put it on my resume then? Because I know this guy on youtube "excelisfun" who has like 10 billion youtube videos about excel. I think I can find out how to work a pivot table from one of those vids and worth it to learn if it'll allow me to put those two on my resume.. | ||
|
Bauzzy
Canada72 Posts
On December 16 2011 06:10 Glacierz wrote: Proficiency in excel does not mean VBA. If you know how to work a pivot table you should be able to put both Excel and VBA on your resume with no problem. I agree with Glacierz. - put both and put more than proficient. That is such a resume buzz word that someone may just assume you know how to use excel, but not know VBA or more. If you get an interview definitely describe how progaming (gramming) can help their department. I work in accounting and in the first year here took a lot of the tasks they gave me and halved the time previously spent on it. Office environments need excel programmers, they just don't know they do (maybe not full time, but they should be talking to consultants). Good luck | ||
|
Glacierz
United States1245 Posts
On December 16 2011 06:20 MaRiNe23 wrote: If I learn that through google or youtube do you think I'll be okay to put it on my resume then? Because I know this guy on youtube "excelisfun" who has like 10 billion youtube videos about excel. I think I can find out how to work a pivot table from one of those vids and worth it to learn if it'll allow me to put those two on my resume.. That's fine. MS office products do not require actual courses to master (maybe VBA would be the only exception). Just google whatever you need to learn. I put pivot table out there because that's as far as any job would require when Excel is listed as a pre-req. I'd look through a curriculum, and stop after pivot tables. | ||
| ||
