The Orange Mane -  a Denver Broncos Fan Community  

Go Back   The Orange Mane - a Denver Broncos Fan Community > Orange Mane Discussion > Orange Mane Central Discussion > Maniac Community Discussion
Register FAQ Members List Calendar Chat Room Mark Forums Read



Reply
 
Thread Tools Display Modes
Old 08-25-2013, 01:50 PM   #26
DryHeat
Guest
 

Posts: n/a
Default

Can't you write a code in python that you can import into Macro?
  Reply With Quote
Old 08-25-2013, 01:51 PM   #27
DryHeat
Guest
 

Posts: n/a
Default

*access
  Reply With Quote
Old 08-25-2013, 01:56 PM   #28
Eldorado
Formerly orange&blue
 
Eldorado's Avatar
 

Join Date: Sep 2007
Posts: 2,288

Adopt-a-Bronco:
Pot Roast
Default

Quote:
Originally Posted by DryHeat View Post
*access
He'd have to buy it.
Eldorado is offline   Reply With Quote
Old 08-25-2013, 05:43 PM   #29
broncosteven
Kranz Dictum
 
broncosteven's Avatar
 
Where is Amazon!

Join Date: Jun 2005
Location: Tranquility Base
Posts: 35,460

Adopt-a-Bronco:
CJ Anderson!
Default

Quote:
Originally Posted by bronco0608 View Post
Thank you so much for asking, That One Guy. Bronco Buster's suggestion was sweet and got me going on the right track -- I think. I should be able to get the names right from there. I am definitely making progress on that front.

Now I need to figure out how to take three different excel invoices, and make them consistent with the same fields on one worksheet. This is what three invoices on the same spreadsheet looks like after pasting them:



And I want to be to automate it to standardize the fields so it looks like this:



I know some of it has to be manual entry. Any suggestions on how to accomplish this?

Standards are a good thing. Have your boss demand that everyone uses the same formatting of their spread sheets moving forward. It will make life easier for you or the guy who replaces you in the long run.
broncosteven is offline   Reply With Quote
Old 08-25-2013, 07:20 PM   #30
That One Guy
Producer of Nonsense
 

Join Date: Aug 2006
Location: Sun and Beachville
Posts: 14,066

Adopt-a-Bronco:
None
Default

Do we get an update on the status?
That One Guy is offline   Reply With Quote
Old 08-25-2013, 07:29 PM   #31
delany
Uber Bronco
 
delany's Avatar
 

Join Date: Mar 2005
Posts: 1,058
Default

Quote:
Originally Posted by CEH View Post
What you are doing is called Extract Translate Load or ETL.
O Rly.
delany is offline   Reply With Quote
Old 08-25-2013, 08:13 PM   #32
DryHeat
Guest
 

Posts: n/a
Default

Quote:
Originally Posted by Eldorado View Post
He'd have to buy it.
You can get Python script readers for free. At least I think you can.

Apple comes with applescript and I'm sure Windows has something, too.

It's real handy when dealing with GIS.
  Reply With Quote
Old 08-25-2013, 08:15 PM   #33
DryHeat
Guest
 

Posts: n/a
Default

Then again, the first poster said he isn't a programmer. But that seems to me to be the easiest way to do it.
  Reply With Quote
Old 08-25-2013, 08:42 PM   #34
baja
Happy camper
 
baja's Avatar
 
Sweet

Join Date: Apr 2001
Location: in the present moment
Posts: 60,005

Adopt-a-Bronco:
Ware
Default

I stayed at a Holiday Inn Express can I help?
baja is offline   Reply With Quote
Old 08-26-2013, 05:36 AM   #35
Eldorado
Formerly orange&blue
 
Eldorado's Avatar
 

Join Date: Sep 2007
Posts: 2,288

Adopt-a-Bronco:
Pot Roast
Default

Quote:
Originally Posted by That One Guy View Post
Just incase you have any issues with the naming thing ('cause I'm about to go to sleep and, possibly, not wake up until Monday), here's a thing I did that might help. You'll have a couple columns you'll need to hide but it'll help on the one that sometimes provides middle initials. Just go into your macro editor, make a new one, and paste everything in the middle. These equations will get your names if they're in First Last or First MI Last format. You'd just have to make an inverted of the first last for the ones that do last first.

Once you have all the columns broken up into data that can be similarly grouped, move it around as needed. You can record yourself moving that around to get it right. To get to the next line in a column, just select the first cell in the column, hold ctrl and hit down, and you will be at the last entry in a column. After that, just insert the line Selection.Offset(1, 0).Select and it'll move you to the very next row.

'
' Macro7 Macro
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = "Knowshon O. Moreno"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Peter Piper"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Funny E. McGee"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Pumkin Sally"
Range("A5").Select
ActiveCell.FormulaR1C1 = "John Q. Smelly"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Sleepy j. McTired"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Gone B. NightNight"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Wee McBangsalot"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=FIND("" "", RC[-1], 1)"
Range("C1").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("C4").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("C7").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("C8").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(UPPER(RC[-2]),"" "",""""))=2, FIND("" "", RC[-2], RC[-1]+1), ""NA"")"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-3])"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-3])"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-3])"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-3])"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-3])"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-3])"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4], 1, RC[-3]-1)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4], 1, RC[-3]-1)"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4], 1, RC[-3]-1)"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4], 1, RC[-3]-1)"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4], 1, RC[-3]-1)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4], 1, RC[-3]-1)"
Range("F1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""NA"",MID(RC[-5],RC[-4]+1,RC[-2]-RC[-4]),MID(RC[-5],RC[-3]+1,RC[-2]-RC[-3]))"
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""NA"",MID(RC[-5],RC[-4]+1,RC[-2]-RC[-4]),MID(RC[-5],RC[-3]+1,RC[-2]-RC[-3]))"
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""NA"",MID(RC[-5],RC[-4]+1,RC[-2]-RC[-4]),MID(RC[-5],RC[-3]+1,RC[-2]-RC[-3]))"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""NA"",MID(RC[-5],RC[-4]+1,RC[-2]-RC[-4]),MID(RC[-5],RC[-3]+1,RC[-2]-RC[-3]))"
Range("F7").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""NA"",MID(RC[-5],RC[-4]+1,RC[-2]-RC[-4]),MID(RC[-5],RC[-3]+1,RC[-2]-RC[-3]))"
Range("F9").Select
Range("A1:F1").Select
Selection.AutoFill Destination:=Range("A1:F8"), Type:=xlFillFormats
Range("A1:F8").Select
Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F8"), Type:=xlFillDefault
Range("F1:F8").Select
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E8"), Type:=xlFillDefault
Range("E1:E8").Select
Range("D1").Select
Selection.AutoFill Destination:=Range("D18"), Type:=xlFillDefault
Range("D18").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C8"), Type:=xlFillDefault
Range("C1:C8").Select
Range("B1").Select

With the smiley faces at the end like that? totally looks like your ****ing with him.
Eldorado is offline   Reply With Quote
Old 08-26-2013, 05:50 AM   #36
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
Default

Guys, I want to thank everyone for all their help. I was able to automate 6 hours of manual entry into 5 mins worth of copying and pasting and letting macros do the rest of the work. I'm so tired I'm seeing double right now; finished one and half ours before demo time.

If I figure out how to do it, I will post a video of the Excel automation. Couldn't have done with this board's help. That's pretty amazing.

Thanks once again. I need a beer.
bronco0608 is offline   Reply With Quote
Old 08-26-2013, 06:07 AM   #37
DenverBound
Thanks for the memories
 
DenverBound's Avatar
 
This one's for Pat!

Join Date: Oct 2009
Posts: 967
Default

What an awesome community. Everyone who helped out with this should be really proud. You guys totally helped this guy keep his job. Kudos to all of you.
DenverBound is offline   Reply With Quote
Old 08-26-2013, 07:21 AM   #38
TheReverend
Permanent Facepalm
 
TheReverend's Avatar
 
Not. Too. Shabby.

Join Date: Apr 2005
Location: Jacksonville, FL
Posts: 37,308

Adopt-a-Bronco:
Mike Shanahan
Default

Quote:
Originally Posted by bronco0608 View Post
Guys, I want to thank everyone for all their help. I was able to automate 6 hours of manual entry into 5 mins worth of copying and pasting and letting macros do the rest of the work. I'm so tired I'm seeing double right now; finished one and half ours before demo time.

If I figure out how to do it, I will post a video of the Excel automation. Couldn't have done with this board's help. That's pretty amazing.

Thanks once again. I need a beer.
Congrats man.

I thought I knew Excel pretty well, so reading this thread was a humbling experience. Sorry I wasn't able to help but this was all very beyond my Excel experience.
TheReverend is offline   Reply With Quote
Old 08-26-2013, 07:47 AM   #39
HILife
Ring of Famer
 
HILife's Avatar
 
Mrs. Alicia Hilife

Join Date: Jun 2006
Location: DC - NOVA - DMV - VA - Take your pick
Posts: 4,513
Default

Congrats!
HILife is offline   Reply With Quote
Old 08-26-2013, 07:49 AM   #40
baja
Happy camper
 
baja's Avatar
 
Sweet

Join Date: Apr 2001
Location: in the present moment
Posts: 60,005

Adopt-a-Bronco:
Ware
Default

Congrats and kudos to the mods for leaving this on page one. Good non call.
baja is offline   Reply With Quote
Old 08-26-2013, 07:59 AM   #41
That One Guy
Producer of Nonsense
 

Join Date: Aug 2006
Location: Sun and Beachville
Posts: 14,066

Adopt-a-Bronco:
None
Default

Quote:
Originally Posted by bronco0608 View Post
Guys, I want to thank everyone for all their help. I was able to automate 6 hours of manual entry into 5 mins worth of copying and pasting and letting macros do the rest of the work. I'm so tired I'm seeing double right now; finished one and half ours before demo time.

If I figure out how to do it, I will post a video of the Excel automation. Couldn't have done with this board's help. That's pretty amazing.

Thanks once again. I need a beer.
Congrats, man. The VBA isn't too tough to learn but it takes work to logically figure out what needs to be done and get the commands to do it. The sad part is whoever is on the receiving end probably won't appreciate all the work you put into it but that's ok.
That One Guy is offline   Reply With Quote
Old 08-26-2013, 08:01 AM   #42
That One Guy
Producer of Nonsense
 

Join Date: Aug 2006
Location: Sun and Beachville
Posts: 14,066

Adopt-a-Bronco:
None
Default

Quote:
Originally Posted by baja View Post
Congrats and kudos to the mods for leaving this on page one. Good non call.
Agreed.
That One Guy is offline   Reply With Quote
Old 08-26-2013, 08:51 AM   #43
Eldorado
Formerly orange&blue
 
Eldorado's Avatar
 

Join Date: Sep 2007
Posts: 2,288

Adopt-a-Bronco:
Pot Roast
Default

Quote:
Originally Posted by TheReverend View Post
Congrats man.

I thought I knew Excel pretty well, so reading this thread was a humbling experience. Sorry I wasn't able to help but this was all very beyond my Excel experience.
VBA is a WHOLE new world.
Eldorado is offline   Reply With Quote
Old 08-26-2013, 09:08 AM   #44
R8R H8R
Ring of Famer
 
R8R H8R's Avatar
 

Join Date: Apr 2005
Location: Exiled in So. Cal.
Posts: 2,691

Adopt-a-Bronco:
Virgil Green
Default

Congrats! Like others, I had no idea how to help you, but props to the guys that did.
That being said, please don't post something in 6 mos. saying you were laid off because the new spreadsheet modification made your job expendable!
R8R H8R is offline   Reply With Quote
Old 08-26-2013, 09:13 AM   #45
Mr.Meanie
Ring of Famer
 
Mr.Meanie's Avatar
 

Join Date: Aug 2008
Posts: 2,607

Adopt-a-Bronco:
None
Default

For what it's worth, when I used to write excel vba macros the absolute best help I could find was from the www.mrexcel.com and www.ozgrid.com/VBA message boards. There are people full time on the site who are perfectly happy to run through your code and work out problems with you. It might be a better source for some help.
Mr.Meanie is offline   Reply With Quote
Old 08-26-2013, 10:16 AM   #46
orangeatheist
Champion of the Godless
 
orangeatheist's Avatar
 

Join Date: Oct 2003
Posts: 3,156
Default

SWEET!!! A happy ending!

orangeatheist is offline   Reply With Quote
Old 08-26-2013, 10:21 AM   #47
baja
Happy camper
 
baja's Avatar
 
Sweet

Join Date: Apr 2001
Location: in the present moment
Posts: 60,005

Adopt-a-Bronco:
Ware
Default

Not sure Michael Jackson is a good theme for a happy ending.


Love Michael never thought he deserved all the rumor based hate.
baja is offline   Reply With Quote
Old 08-26-2013, 10:28 AM   #48
Bacchus
Guest
 

Posts: n/a
Default

Ok I am ready to help, what you need?
  Reply With Quote
Old 08-26-2013, 12:02 PM   #49
orangeatheist
Champion of the Godless
 
orangeatheist's Avatar
 

Join Date: Oct 2003
Posts: 3,156
Default

Quote:
Originally Posted by baja View Post
Not sure Michael Jackson is a good theme for a happy ending.


Love Michael never thought he deserved all the rumor based hate.
I was just trying to make a matching bookend for my Michael popcorn eating meme I'd posted earlier.
orangeatheist is offline   Reply With Quote
Old 08-26-2013, 12:16 PM   #50
baja
Happy camper
 
baja's Avatar
 
Sweet

Join Date: Apr 2001
Location: in the present moment
Posts: 60,005

Adopt-a-Bronco:
Ware
Default

Quote:
Originally Posted by orangeatheist View Post
I was just trying to make a matching bookend for my Michael popcorn eating meme I'd posted earlier.
Oh Gotcha.
baja is offline   Reply With Quote
Reply

Thread Tools
Display Modes



Forum Jump


All times are GMT -7. The time now is 12:29 AM.


Denver Broncos