PDA

View Full Version : Update: It's done! Whoo! Bronco Bros I Need Your Help: Excel VBA Script to Standardize Names


bronco0608
08-24-2013, 04:51 PM
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:

http://i40.tinypic.com/2dshkcw.jpg

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.

http://i39.tinypic.com/1493ui9.png

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.

http://i39.tinypic.com/2mrchvr.png

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.

bronco0608
08-24-2013, 04:52 PM
Backdrop to Story<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
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."<o:p></o:p>
<o:p> </o:p>
I then proceed to contact all three staffing agency to see if they could all format their invoices exactly the same. <o:p></o:p>
<o:p> </o:p>
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.<o:p></o:p>
<o:p> </o:p>
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. <o:p></o:p>
<o:p> </o:p>
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? <o:p></o:p>
<o:p> </o:p>
Fearing for my job, I said, "Sure, I can do it." She then says, "I need it next Monday." WTF? <o:p></o:p>
<o:p> </o:p>
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. <o:p></o:p>
<o:p> </o:p>
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.<o:p></o:p>
<o:p> </o:p>
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. <o:p></o:p>
<o:p> </o:p>
Help!<o:p></o:p>

broncosteven
08-24-2013, 08:19 PM
Dunno

McDman
08-24-2013, 08:24 PM
Why would you tell her you can do it if you don't know how?

oubronco
08-24-2013, 08:26 PM
Wow have fun with that shyt

Br0nc0Buster
08-24-2013, 08:28 PM
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
08-24-2013, 08:29 PM
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"

bronco0608
08-24-2013, 08:50 PM
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.

Br0nc0Buster
08-24-2013, 09:21 PM
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

That One Guy
08-24-2013, 10:14 PM
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.

bronco0608
08-24-2013, 10:56 PM
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:

http://i39.tinypic.com/16hmcuf.png

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

http://i44.tinypic.com/2rotkbr.png

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

That One Guy
08-24-2013, 11:11 PM
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.

ghwk
08-24-2013, 11:43 PM
Once you get this worked out you need to start looking for a new job. Your boss is an ignorant bitch.

SonOfLe-loLang
08-25-2013, 12:02 AM
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.

broncocalijohn
08-25-2013, 12:23 AM
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.

bronco0608
08-25-2013, 12:47 AM
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.

That One Guy
08-25-2013, 01:00 AM
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("D1:D8"), Type:=xlFillDefault
Range("D1:D8").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C8"), Type:=xlFillDefault
Range("C1:C8").Select
Range("B1").Select

That One Guy
08-25-2013, 01:03 AM
Just make sure you replace the two smiley faces with : D (without the spaces)

bronco0608
08-25-2013, 03:22 AM
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("D1:D8"), Type:=xlFillDefault
Range("D1:D8").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.

CEH
08-25-2013, 07:21 AM
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 One Guy
08-25-2013, 08:27 AM
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.

orangeatheist
08-25-2013, 08:37 AM
The suspense is killing me!

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

http://31.media.tumblr.com/tumblr_lw7qn58NxG1qii6tmo1_500.gif

SonOfLe-loLang
08-25-2013, 08:42 AM
The suspense is killing me!

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

http://31.media.tumblr.com/tumblr_lw7qn58NxG1qii6tmo1_500.gif

I lol'd

bronco0608
08-25-2013, 11:01 AM
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.

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.

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.

Jekyll15Hyde
08-25-2013, 01:47 PM
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

DryHeat
08-25-2013, 01:50 PM
Can't you write a code in python that you can import into Macro?

DryHeat
08-25-2013, 01:51 PM
*access

Eldorado
08-25-2013, 01:56 PM
*access

He'd have to buy it.

broncosteven
08-25-2013, 05:43 PM
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:

http://i39.tinypic.com/16hmcuf.png

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

http://i44.tinypic.com/2rotkbr.png

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.

That One Guy
08-25-2013, 07:20 PM
Do we get an update on the status?

delany
08-25-2013, 07:29 PM
What you are doing is called Extract Translate Load or ETL.



O Rly.

DryHeat
08-25-2013, 08:13 PM
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.

DryHeat
08-25-2013, 08:15 PM
Then again, the first poster said he isn't a programmer. But that seems to me to be the easiest way to do it.

baja
08-25-2013, 08:42 PM
I stayed at a Holiday Inn Express can I help?

Eldorado
08-26-2013, 05:36 AM
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("D1:D8"), Type:=xlFillDefault
Range("D1:D8").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.

bronco0608
08-26-2013, 05:50 AM
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.

DenverBound
08-26-2013, 06:07 AM
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.

TheReverend
08-26-2013, 07:21 AM
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.

HILife
08-26-2013, 07:47 AM
Congrats!

baja
08-26-2013, 07:49 AM
Congrats and kudos to the mods for leaving this on page one. Good non call.

That One Guy
08-26-2013, 07:59 AM
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
08-26-2013, 08:01 AM
Congrats and kudos to the mods for leaving this on page one. Good non call.

Agreed.

Eldorado
08-26-2013, 08:51 AM
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.

R8R H8R
08-26-2013, 09:08 AM
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! ;D

Mr.Meanie
08-26-2013, 09:13 AM
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.

orangeatheist
08-26-2013, 10:16 AM
SWEET!!! A happy ending!

http://24.media.tumblr.com/tumblr_m688inMDaX1r4urpmo1_500.gif

baja
08-26-2013, 10:21 AM
Not sure Michael Jackson is a good theme for a happy ending.


Love Michael never thought he deserved all the rumor based hate.

Bacchus
08-26-2013, 10:28 AM
Ok I am ready to help, what you need?

orangeatheist
08-26-2013, 12:02 PM
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.

baja
08-26-2013, 12:16 PM
I was just trying to make a matching bookend for my Michael popcorn eating meme I'd posted earlier.

Oh Gotcha.

broncosteven
08-26-2013, 01:31 PM
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 keeping your job is a good thing.