• Log InLog In
  • Register
Liquid`
Team Liquid Liquipedia
EDT 01:00
CEST 07:00
KST 14:00
  • Home
  • Forum
  • Calendar
  • Streams
  • Liquipedia
  • Features
  • Store
  • EPT
  • TL+
  • StarCraft 2
  • Brood War
  • Smash
  • Heroes
  • Counter-Strike
  • Overwatch
  • Liquibet
  • Fantasy StarCraft
  • TLPD
  • StarCraft 2
  • Brood War
  • Blogs
Forum Sidebar
Events/Features
News
Featured News
TL.net Map Contest #21: Voting9[ASL20] Ro4 Preview: Descent11Team TLMC #5: Winners Announced!3[ASL20] Ro8 Preview Pt2: Holding On9Maestros of the Game: Live Finals Preview (RO4)5
Community News
BSL Team A vs Koreans - Sat-Sun 16:00 CET5Weekly Cups (Oct 6-12): Four star herO85.0.15 Patch Balance Hotfix (2025-10-8)80Weekly Cups (Sept 29-Oct 5): MaxPax triples up3PartinG joins SteamerZone, returns to SC2 competition32
StarCraft 2
General
The New Patch Killed Mech! Revisiting the game after10 years and wow it's bad Stellar Fest: StarCraft II returns to Canada herO Talks: Poor Performance at EWC and more... TL.net Map Contest #21: Voting
Tourneys
SC2's Safe House 2 - October 18 & 19 $1,200 WardiTV October (Oct 21st-31st) WardiTV Mondays RSL Offline Finals Dates + Ticket Sales! SC4ALL $6,000 Open LAN in Philadelphia
Strategy
Custom Maps
External Content
Mutation # 495 Rest In Peace Mutation # 494 Unstable Environment Mutation # 493 Quick Killers Mutation # 492 Get Out More
Brood War
General
BW General Discussion BSL Team A vs Koreans - Sat-Sun 16:00 CET Question regarding recent ASL Bisu vs Larva game [Interview] Grrrr... 2024 Pros React To: BarrackS + FlaSh Coaching vs SnOw
Tourneys
[ASL20] Semifinal B SC4ALL $1,500 Open Bracket LAN [Megathread] Daily Proleagues [ASL20] Semifinal A
Strategy
Current Meta BW - ajfirecracker Strategy & Training Relatively freeroll strategies Siegecraft - a new perspective
Other Games
General Games
Stormgate/Frost Giant Megathread Dawn of War IV Path of Exile Nintendo Switch Thread ZeroSpace Megathread
Dota 2
Official 'what is Dota anymore' discussion LiquidDota to reintegrate into TL.net
League of Legends
Heroes of the Storm
Simple Questions, Simple Answers Heroes of the Storm 2.0
Hearthstone
Deck construction bug Heroes of StarCraft mini-set
TL Mafia
SPIRED by.ASL Mafia {211640} TL Mafia Community Thread
Community
General
US Politics Mega-thread Russo-Ukrainian War Thread Things Aren’t Peaceful in Palestine Men's Fashion Thread Sex and weight loss
Fan Clubs
The herO Fan Club! The Happy Fan Club!
Media & Entertainment
Anime Discussion Thread [Manga] One Piece Series you have seen recently... Movie Discussion!
Sports
Formula 1 Discussion 2024 - 2026 Football Thread MLB/Baseball 2023 NBA General Discussion TeamLiquid Health and Fitness Initiative For 2023
World Cup 2022
Tech Support
SC2 Client Relocalization [Change SC2 Language] Linksys AE2500 USB WIFI keeps disconnecting Computer Build, Upgrade & Buying Resource Thread
TL Community
The Automated Ban List Recent Gifted Posts
Blogs
The Heroism of Pepe the Fro…
Peanutsc
Rocket League: Traits, Abili…
TrAiDoS
Customize Sidebar...

Website Feedback

Closed Threads



Active: 1493 users

Excel Pros Please Help! - Page 2

Blogs > Golgotha
Post a Reply
Prev 1 2 3 Next All
Glacierz
Profile Blog Joined May 2010
United States1244 Posts
December 15 2011 19:37 GMT
#21
Macros are basically VBA code that are recorded through user interactions with Excel. If you find yourself repeating the same action multiple times, you can write a macro and loop through it to simplify the work.
Glacierz
Profile Blog Joined May 2010
United States1244 Posts
December 15 2011 19:39 GMT
#22
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
Profile Blog Joined October 2009
Canada6330 Posts
December 15 2011 19:41 GMT
#23
You can learn macros from just simply:
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.
[TLMS] REBOOT
MisterD
Profile Blog Joined June 2010
Germany1338 Posts
December 15 2011 19:45 GMT
#24
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
Gold isn't everything in life... you need wood, too!
infinity21 *
Profile Blog Joined October 2006
Canada6683 Posts
Last Edited: 2011-12-15 19:55:11
December 15 2011 19:49 GMT
#25
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.

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
Official Entusman #21
MoreFaSho
Profile Blog Joined May 2010
United States1427 Posts
Last Edited: 2011-12-15 20:00:52
December 15 2011 19:59 GMT
#26
Does it have to be in excel? I often combine excel with some really light python scripting and it's ridiculously to do things like this. Put the data in a text file:

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.
I always try to shield slam face, just to make sure it doesnt work
zeks
Profile Blog Joined September 2007
Canada1068 Posts
December 15 2011 19:59 GMT
#27
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.

Show nested quote +
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


Me until this work term bahaha
- Richie
"Two roads diverged in a wood, and I-- I took the one less traveled by, And that has made all the difference."
Glacierz
Profile Blog Joined May 2010
United States1244 Posts
December 15 2011 20:00 GMT
#28
I'd also read this:
http://en.wikipedia.org/wiki/Pivot_table
Golgotha
Profile Blog Joined January 2011
Korea (South)8418 Posts
Last Edited: 2011-12-15 20:06:38
December 15 2011 20:03 GMT
#29
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.
Chill
Profile Blog Joined January 2005
Calgary25986 Posts
December 15 2011 20:08 GMT
#30
Your best bet is to use "Record Macro". Do something basic that you need to know the code for. Stop the macro. Press alt 11. Open the macro you just recorded. Steal the code. Put it into your code.

Repeat.
Moderator
infinity21 *
Profile Blog Joined October 2006
Canada6683 Posts
December 15 2011 20:10 GMT
#31
On December 16 2011 04:59 zeks wrote:
Show nested quote +
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.

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


Me until this work term bahaha
- Richie

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...
Official Entusman #21
goldrush
Profile Blog Joined June 2004
Canada709 Posts
Last Edited: 2011-12-15 20:38:05
December 15 2011 20:36 GMT
#32
edit: nevermind, you need it done a different way
Golgotha
Profile Blog Joined January 2011
Korea (South)8418 Posts
Last Edited: 2011-12-15 21:01:42
December 15 2011 20:56 GMT
#33
Excel File:
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
Profile Blog Joined November 2008
Poland605 Posts
December 15 2011 21:00 GMT
#34
On December 16 2011 04:59 MoreFaSho wrote:
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


I was going to recommend python too, but you were faster. Also, [ code] tag is available on TL
MaRiNe23
Profile Blog Joined December 2006
United States747 Posts
Last Edited: 2011-12-15 21:02:29
December 15 2011 21:01 GMT
#35
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?
We have competitive ladder, strong community, progaming in Korea going strong, perfectly balanced game..why do we need sc2? #1 ANTI-SC2 fan
Glacierz
Profile Blog Joined May 2010
United States1244 Posts
December 15 2011 21:10 GMT
#36
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
Profile Blog Joined May 2010
United States1244 Posts
December 15 2011 21:11 GMT
#37
I always wondered what it means to be proficient in Word though...
MaRiNe23
Profile Blog Joined December 2006
United States747 Posts
December 15 2011 21:20 GMT
#38
On December 16 2011 06:10 Glacierz wrote:
Show nested quote +
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.

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..
We have competitive ladder, strong community, progaming in Korea going strong, perfectly balanced game..why do we need sc2? #1 ANTI-SC2 fan
Bauzzy
Profile Blog Joined January 2011
Canada72 Posts
December 15 2011 21:20 GMT
#39
On December 16 2011 06:10 Glacierz wrote:
Show nested quote +
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.



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
Profile Blog Joined May 2010
United States1244 Posts
December 15 2011 21:23 GMT
#40
On December 16 2011 06:20 MaRiNe23 wrote:
Show nested quote +
On December 16 2011 06:10 Glacierz wrote:
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.

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.
Prev 1 2 3 Next All
Please log in or register to reply.
Live Events Refresh
Next event in 5h
[ Submit Event ]
Live Streams
Refresh
StarCraft 2
Nina 158
ROOTCatZ 43
StarCraft: Brood War
PianO 325
zelot 78
NaDa 16
Britney 0
League of Legends
JimRising 768
Counter-Strike
Stewie2K560
Coldzera 247
Heroes of the Storm
Khaldor150
Other Games
summit1g17802
WinterStarcraft641
ViBE167
Trikslyr32
Organizations
Counter-Strike
PGL3621
Other Games
gamesdonequick2066
BasetradeTV34
StarCraft 2
Blizzard YouTube
StarCraft: Brood War
BSLTrovo
sctven
[ Show 14 non-featured ]
StarCraft 2
• practicex 38
• Hupsaiya 32
• AfreecaTV YouTube
• intothetv
• Kozan
• IndyKCrew
• LaughNgamezSOOP
• Migwel
• sooper7s
StarCraft: Brood War
• Diggity7
• BSLYoutube
• STPLYoutube
• ZZZeroYoutube
Dota 2
• WagamamaTV605
Upcoming Events
Sparkling Tuna Cup
5h
Safe House 2
12h
IPSL
14h
Sziky vs Havi
Artosis vs Klauso
Monday Night Weeklies
1d 11h
WardiTV Invitational
2 days
WardiTV Invitational
2 days
Tenacious Turtle Tussle
3 days
The PondCast
4 days
WardiTV Invitational
5 days
Online Event
5 days
[ Show More ]
RSL Revival
5 days
RSL Revival
6 days
WardiTV Invitational
6 days
Liquipedia Results

Completed

Acropolis #4 - TS2
WardiTV TLMC #15
HCC Europe

Ongoing

BSL 21 Points
ASL Season 20
CSL 2025 AUTUMN (S18)
C-Race Season 1
IPSL Winter 2025-26
EC S1
Thunderpick World Champ.
CS Asia Championships 2025
ESL Pro League S22
StarSeries Fall 2025
FISSURE Playground #2
BLAST Open Fall 2025
BLAST Open Fall Qual
Esports World Cup 2025
BLAST Bounty Fall 2025
BLAST Bounty Fall Qual

Upcoming

SC4ALL: Brood War
BSL Season 21
BSL 21 Team A
BSL 21 Non-Korean Championship
RSL Offline Finals
RSL Revival: Season 3
Stellar Fest
SC4ALL: StarCraft II
CranK Gathers Season 2: SC II Pro Teams
eXTREMESLAND 2025
ESL Impact League Season 8
SL Budapest Major 2025
BLAST Rivals Fall 2025
IEM Chengdu 2025
PGL Masters Bucharest 2025
TLPD

1. ByuN
2. TY
3. Dark
4. Solar
5. Stats
6. Nerchio
7. sOs
8. soO
9. INnoVation
10. Elazer
1. Rain
2. Flash
3. EffOrt
4. Last
5. Bisu
6. Soulkey
7. Mini
8. Sharp
Sidebar Settings...

Advertising | Privacy Policy | Terms Of Use | Contact Us

Original banner artwork: Jim Warren
The contents of this webpage are copyright © 2025 TLnet. All Rights Reserved.