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-24-2013, 05:51 PM   #1
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
Default Update: It's done! Whoo! Bronco Bros I Need Your Help: Excel VBA Script to Standardize Names

Hey Guys - First of all my sincerest apologies for placing this on main page after entering On-Season mode yesterday, but I really need some help to keep my job and this is last my hope. I know this will be moved. Sorry once again.

I am at work (on a Saturday) beating my head against my desk trying to standardize names to make a report. Keep in mind, I have no previous VBA experience. This is what I am dealing with:

My boss gets invoices in Excel from three different staffing agencies that supply our company with workers. But each staffing agency lists the names of the workers in different formats. Staffing Agency One puts the last name in column A and the first name in column B; Staffing Agency Two puts the complete name in one cell in [last name, first name format] (Doe, John) ; and Staffing Agency Three puts the complete name in one cell as well but puts it in [first name last name format] (John Doe). And these variations happen for, literally, almost a thousand contract workers.

I need to take all that disjointed data and put into a standardized format that I can then import into Access.

I'm taking baby steps here and trying to at least standardize names into the same format. Ultimately, I want all that data to look like this:



Since I don't know how to code, I searched the web for a VBA script, and I found something on an Excel message board for a guy who was also trying to standardize some names but based off slightly different criteria/data as seen below.



Here is the script the guy posted to standardize his names but it gets a "Subscript out of range" error. Microsoft Help suggests using a For Each....Next construct instead of specifying index elements - whatever that means.



This is my last hope, guys. I'll post another post below for an entertaining backdrop to this story. I'm neither a writer or a programmer. I do know that I am a desperate man trying to keep his job and any help with this would be greatly appreciated. Thanks in advance.

Last edited by bronco0608; 08-26-2013 at 06:52 AM..
bronco0608 is offline   Reply With Quote
Sponsored Links
Old 08-24-2013, 05:52 PM   #2
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
Default

Backdrop to Storyfficeffice" />

Earlier this week, my boss calls me into her office and tells me she is tired of manually entering the contract employees payroll invoice data into an Excel spreadsheet that she hands over to her boss on a weekly basis. She tells me to contact the staffing agencies and see if they can format their Excel invoice reports in the same format so she can simply paste it all into one worksheet and pooff, her report is done in minutes. Thinking this would be a piece of cake, I excitedly told her, "No problem. I'll take care of it."

I then proceed to contact all three staffing agency to see if they could all format their invoices exactly the same.

Surprisingly, they all said no. Basically, they all said my company was just one of their 100s of clients and they can't format their invoices differently for each one - especially a mid-size firm like Employment is Enjoyment (stage name). Ouch. Thanks.

I go back to my boss and let her know that the agencies will not put all the invoices in the same format. Thinking that was the end of the that, I begin to get up when she says, "That's fine. Get Frank in Apps and tell him to write some code in Excel that when you paste all three invoices into the same worksheet, it will format all the data to the look the same and place the information in sheet 2. On Sheet 2, the column field headers should be, in order, 'Date', 'Last Name', 'First Name', 'Complete Name', 'Project', 'Rate', 'Hours Worked', 'OT Hours', 'OT Rate' and 'Total Pay'. From there, tell him import it into an Access database so I can archive it and search the data." Not a prob. Let me go see Frank. Don't know Frank, don't know his department, but he is the app guy. Frank is gonna knock this out. Wrong. Frank tells me my boss has to get clearance through his boss for him to work on a project other than the one that he is currently assigned to. Not a pleasant guy that Frank was either. He seem very irritated by my mere presence much less my request.

Once again, I go back to my boss and let her know about Frank. She says, "**** Frank. You do it." I was like, huh? Does this woman know that I have no clue how to do this? That my only programming experience was 15 years ago when I made a Geocities homepage that had nothing but moving objects like a bouncing basketball and a troll that fell over repeatedly?

Fearing for my job, I said, "Sure, I can do it." She then says, "I need it next Monday." WTF?

Knowing the furious nature of my boss when her employees do not execute the tasks assigned to them, picturing my pregnant wife in my mind's eye, and remembering that this low level job is the only legitimate work I could get after being laid off three years ago, I furiously get to work. Burning the midnight oil, I make a craptastic Access Database - ugly forms, table relationships that makes even Access laugh, and duplicative data everywhere. But it works-sort of.

Then I get to work on trying to standardizing the names in Excel so it will match the fields set in Access. I buy a book on Excel Macros - might as well be written in Chinese - no help; I search the web high low for the code that will do this for me - can't find one. Desperately, I try to write broken ass script after broken ass script...nothing works.

I then reach out to three remaining friends I have in a last ditched attempt for help, but as suspected, they kept the convos centered on fantasy football and stated that don't even know what VBA is much less how to help me with it. That leads me here. Having approximately 38 hours left before I have to demo this for my boss, I have come seeking help from my fellow Broncos brothers.

Help!
bronco0608 is offline   Reply With Quote
Old 08-24-2013, 09:19 PM   #3
broncosteven
Kranz Dictum
 
broncosteven's Avatar
 
Beat the Chefs! Tough and Competent

Join Date: Jun 2005
Location: Tranquility Base
Posts: 37,534

Adopt-a-Bronco:
CJ Anderson
Default

Dunno
broncosteven is offline   Reply With Quote
Old 08-24-2013, 09:24 PM   #4
McDman
Ring of Famer
 
McDman's Avatar
 
Huzzah!

Join Date: Jul 2006
Posts: 4,741

Adopt-a-Bronco:
None
Default

Why would you tell her you can do it if you don't know how?
McDman is offline   Reply With Quote
Old 08-24-2013, 09:26 PM   #5
oubronco
John Foneco !!
 
oubronco's Avatar
 
Mile High Magic

Join Date: Nov 2006
Location: Sooner Country
Posts: 21,304
Default

Wow have fun with that shyt
oubronco is online now   Reply With Quote
Old 08-24-2013, 09:28 PM   #6
Br0nc0Buster
2013 AFC Champs
 
Br0nc0Buster's Avatar
 
Oh........yea

Join Date: Apr 2007
Location: Kansas
Posts: 5,974
Default

If I am reading this right you should be able to solve all your problems pulling data in cells apart with text to columns and putting them back together using a concatenate formula.
Have you tried that?
Br0nc0Buster is offline   Reply With Quote
Old 08-24-2013, 09:29 PM   #7
Br0nc0Buster
2013 AFC Champs
 
Br0nc0Buster's Avatar
 
Oh........yea

Join Date: Apr 2007
Location: Kansas
Posts: 5,974
Default

Text to columns the cells for staffing agency 2, and choose delimited
pick comma to break the data into two separate cells

Do the same thing for staffing agency three except using spaces instead of commas

That will break all the data into cells for their last name and cells for their first name
Use a concatenate formula to add those values into one cell to get "complete name"
Br0nc0Buster is offline   Reply With Quote
Old 08-24-2013, 09:50 PM   #8
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
Default

Quote:
Originally Posted by Br0nc0Buster View Post
Text to columns the cells for staffing agency 2, and choose delimited
pick comma to break the data into two separate cells

Do the same thing for staffing agency three except using spaces instead of commas

That will break all the data into cells for their last name and cells for their first name
Use a concatenate formula to add those values into one cell to get "complete name"
Thanks for the reply, bud. I really, really appreciate it.

These staffing agencies are all over the place with the name formats. I was just looking at the latest invoice, and one of them decided to middle initials and full middle names in there!

John Q. Wilson
Melissa Marie Owens
Terry C. Edwards
Michael O. Kim

This is hurting my head.
bronco0608 is offline   Reply With Quote
Old 08-24-2013, 10:21 PM   #9
Br0nc0Buster
2013 AFC Champs
 
Br0nc0Buster's Avatar
 
Oh........yea

Join Date: Apr 2007
Location: Kansas
Posts: 5,974
Default

Quote:
Originally Posted by bronco0608 View Post
Thanks for the reply, bud. I really, really appreciate it.

These staffing agencies are all over the place with the name formats. I was just looking at the latest invoice, and one of them decided to middle initials and full middle names in there!

John Q. Wilson
Melissa Marie Owens
Terry C. Edwards
Michael O. Kim

This is hurting my head.
Well that wont prevent you from still organizing the names however you wish

If you text to columns names using spaces and you get middle names, initials, etc.. it will just break their name up into 3 cells instead of 2
You can then just delete the middle name cell if you dont need it
Or you can run a concatenate formula to combine them just so you have two cells for their entire name

You can pull data from different formats and combine it all into one format using the methods I described in the previous post
If you are not too familiar with concatenates and text to columns, then I suggest doing a little google searching and tinkering in Excel with them

Good news is this shouldnt take long at all (like under 30 min) once you know what you are doing
And you wont have to run a macro or anything
Br0nc0Buster is offline   Reply With Quote
Old 08-24-2013, 11:14 PM   #10
That One Guy
Producer of Nonsense
 

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

Adopt-a-Bronco:
None
Default

Let us know if you're able to do what Broncobuster said. Hopefully you can but if not, I was once a self-taught master of horribly inefficient VBA back in my day. It got the job done, though. I just don't even wanna break out that part of my brain again if this up here worked.
That One Guy is offline   Reply With Quote
Old 08-24-2013, 11:56 PM   #11
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
Default

Quote:
Originally Posted by That One Guy View Post
Let us know if you're able to do what Broncobuster said. Hopefully you can but if not, I was once a self-taught master of horribly inefficient VBA back in my day. It got the job done, though. I just don't even wanna break out that part of my brain again if this up here worked.
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?
bronco0608 is offline   Reply With Quote
Old 08-25-2013, 12:11 AM   #12
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
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?
Sorry, it's late and my brain is half ready to nod off. If I understand correctly, you're expecting to be able to get the names broken into the appropriate columns?

If so, what I would do is make a 4 page excel. Make the front page your end product then put each company on its own tab. As long as you have the columns set up right, this will allow you to copy different columns from each sheet and combine them. Getting the names broken up would be the hard part. After that, you can automate it by just recording a macro and there'd be nothing to write.

Sorry if I'm not getting where you're at but it seems like you should be on the homestretch if you got the names to break up properly.
That One Guy is offline   Reply With Quote
Old 08-25-2013, 12:43 AM   #13
ghwk
Ring of Famer
 
ghwk's Avatar
 
Survivor survivor!

Join Date: Jul 2008
Posts: 4,829

Adopt-a-Bronco:
Peyton's C3-C5
Default

Once you get this worked out you need to start looking for a new job. Your boss is an ignorant b****.
ghwk is offline   Reply With Quote
Old 08-25-2013, 01:02 AM   #14
SonOfLe-loLang
Young Buck
 
SonOfLe-loLang's Avatar
 

Join Date: Jan 2007
Location: Los Angeles
Posts: 21,215

Adopt-a-Bronco:
Thunder (RIP)
Default

when people have asked me what I want to do for a living, I tell them I never want to work with excel.

True story.
SonOfLe-loLang is offline   Reply With Quote
Old 08-25-2013, 01:23 AM   #15
broncocalijohn
Famer of Rings
 
broncocalijohn's Avatar
 
I said Do It!

Join Date: Oct 2004
Location: Lake Forest, Orange County, Calif.
Posts: 23,258

Adopt-a-Bronco:
Simon Fletcher
Default

I don't know but I think it is awesome that you have Elway, Bailey, Manning, Floyd Little and Randy Gradishar working at your place.
broncocalijohn is offline   Reply With Quote
Old 08-25-2013, 01:47 AM   #16
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
Default

Quote:
Originally Posted by That One Guy View Post
Sorry, it's late and my brain is half ready to nod off. If I understand correctly, you're expecting to be able to get the names broken into the appropriate columns?

If so, what I would do is make a 4 page excel. Make the front page your end product then put each company on its own tab. As long as you have the columns set up right, this will allow you to copy different columns from each sheet and combine them. Getting the names broken up would be the hard part. After that, you can automate it by just recording a macro and there'd be nothing to write.

Sorry if I'm not getting where you're at but it seems like you should be on the homestretch if you got the names to break up properly.
Thanks, Bud. I'll try that and see how it works.
bronco0608 is offline   Reply With Quote
Old 08-25-2013, 02:00 AM   #17
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
Thanks, Bud. I'll try that and see how it works.
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

Last edited by That One Guy; 08-25-2013 at 02:07 AM..
That One Guy is offline   Reply With Quote
Old 08-25-2013, 02:03 AM   #18
That One Guy
Producer of Nonsense
 

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

Adopt-a-Bronco:
None
Default

Just make sure you replace the two smiley faces with : D (without the spaces)
That One Guy is offline   Reply With Quote
Old 08-25-2013, 04:22 AM   #19
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
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
Dude, I can never thank you enough for you have done. I don't even know if this script works (not because of you, but because of me--might be too dumb to run it properly), but man, you really stepped up for a guy you don't even know.

I can never thank you enough. If I get this to work, I'll shoot over the workbook to you.

Thanks, That One Guy. You are awesome.
bronco0608 is offline   Reply With Quote
Old 08-25-2013, 08:21 AM   #20
CEH
Ring of Famer
 

Join Date: Mar 2004
Posts: 4,266
Default

Another solution since you are not a VBA guy and really not a skill set to learn

What you are doing is called Extract Translate Load or ETL.


I would look for a free ETL tool on the web. Odds are someone else has already done the work for you unless your company has a policy against shareware

You then use their UI designer to build each clients invoice meta data once and let the engine go the work transforming the data source to your company's target format

Now you have a tool that can be extended to any new customer's format in minutes

Found one CloverETL http://www.cloveretl.com/

Uses the community version it's free and supports basic Excel files

Good luck

Last edited by CEH; 08-25-2013 at 08:23 AM..
CEH is offline   Reply With Quote
Old 08-25-2013, 09:27 AM   #21
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
Dude, I can never thank you enough for you have done. I don't even know if this script works (not because of you, but because of me--might be too dumb to run it properly), but man, you really stepped up for a guy you don't even know.

I can never thank you enough. If I get this to work, I'll shoot over the workbook to you.

Thanks, That One Guy. You are awesome.
I would definitely consider what the CEH just said because that'd make life a lot simpler. I was laying in bed cussing the world for the wife and kid waking me up so early and it hit me that even the extra columns were unnecessary that I had. Their functions could be embedded in the larger function. So if you have to use the method I sent you, replace the columns with

=MID(A1, 1, (FIND(" ", A1, 1))-1) and

=IF(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA")="NA",MID(A1,(FIND(" ", A1, 1))+1,LEN(A1)-(FIND(" ", A1, 1))),MID(A1,(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA"))+1,LEN(A1)-(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA"))))

The first one for the first name column, last one for the last name column. This'll allow you to not have any random columns in the middle of your sheet. Keep us posted on the progress.
That One Guy is offline   Reply With Quote
Old 08-25-2013, 09:37 AM   #22
orangeatheist
Champion of the Godless
 
orangeatheist's Avatar
 

Join Date: Oct 2003
Posts: 3,169
Default

The suspense is killing me!

It's Sunday! Will he get it done by Monday morning when the boss returns?

orangeatheist is offline   Reply With Quote
Old 08-25-2013, 09:42 AM   #23
SonOfLe-loLang
Young Buck
 
SonOfLe-loLang's Avatar
 

Join Date: Jan 2007
Location: Los Angeles
Posts: 21,215

Adopt-a-Bronco:
Thunder (RIP)
Default

Quote:
Originally Posted by orangeatheist View Post
The suspense is killing me!

It's Sunday! Will he get it done by Monday morning when the boss returns?

I lol'd
SonOfLe-loLang is offline   Reply With Quote
Old 08-25-2013, 12:01 PM   #24
bronco0608
Ring of Famer
 
New to the Forum

Join Date: Jul 2007
Posts: 1,276

Adopt-a-Bronco:
None
Default

Quote:
Originally Posted by CEH View Post
Another solution since you are not a VBA guy and really not a skill set to learn

What you are doing is called Extract Translate Load or ETL.


I would look for a free ETL tool on the web. Odds are someone else has already done the work for you unless your company has a policy against shareware

You then use their UI designer to build each clients invoice meta data once and let the engine go the work transforming the data source to your company's target format

Now you have a tool that can be extended to any new customer's format in minutes

Found one CloverETL http://www.cloveretl.com/

Uses the community version it's free and supports basic Excel files

Good luck
That's for the head's up, CEH. I'll have to check into this.

Quote:
Originally Posted by That One Guy
would definitely consider what the CEH just said because that'd make life a lot simpler. I was laying in bed cussing the world for the wife and kid waking me up so early and it hit me that even the extra columns were unnecessary that I had. Their functions could be embedded in the larger function. So if you have to use the method I sent you, replace the columns with

=MID(A1, 1, (FIND(" ", A1, 1))-1) and

=IF(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA")="NA",MID(A1,(FIND(" ", A1, 1))+1,LEN(A1)-(FIND(" ", A1, 1))),MID(A1,(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA"))+1,LEN(A1)-(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA"))))

The first one for the first name column, last one for the last name column. This'll allow you to not have any random columns in the middle of your sheet. Keep us posted on the progress.
Thanks, bud. Thanks to you, I discovered formulas last night. Not really discovered, but sort of understood how they worked and operated.

Scary, I know.

Quote:
Originally Posted by orangeatheist
The suspense is killing me!

It's Sunday! Will he get it done by Monday morning when the boss returns?

You and me both! I worked throughout the night and crashed at 8 this morning. Woke up at 1 and right back at it.
bronco0608 is offline   Reply With Quote
Old 08-25-2013, 02:47 PM   #25
Jekyll15Hyde
Anti Frown Cannon & McD..
 
Jekyll15Hyde's Avatar
 

Join Date: Sep 2008
Posts: 1,866
Default

Quote:
Originally Posted by That One Guy View Post
I would definitely consider what the CEH just said because that'd make life a lot simpler. I was laying in bed cussing the world for the wife and kid waking me up so early and it hit me that even the extra columns were unnecessary that I had. Their functions could be embedded in the larger function. So if you have to use the method I sent you, replace the columns with

=MID(A1, 1, (FIND(" ", A1, 1))-1) and

=IF(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA")="NA",MID(A1,(FIND(" ", A1, 1))+1,LEN(A1)-(FIND(" ", A1, 1))),MID(A1,(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA"))+1,LEN(A1)-(IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," ",""))=2, FIND(" ", A1, (FIND(" ", A1, 1))+1), "NA"))))

The first one for the first name column, last one for the last name column. This'll allow you to not have any random columns in the middle of your sheet. Keep us posted on the progress.
As an Excel junkie, I fully endorse this answer! Nice work
Jekyll15Hyde is offline   Reply With Quote
Reply

Thread Tools
Display Modes



Forum Jump


All times are GMT -7. The time now is 05:26 PM.


Denver Broncos