The Orange Mane -  a Denver Broncos Fan Community

The Orange Mane - a Denver Broncos Fan Community (http://www.orangemane.com/BB/index.php)
-   Maniac Community Discussion (http://www.orangemane.com/BB/forumdisplay.php?f=43)
-   -   Update: It's done! Whoo! Bronco Bros I Need Your Help: Excel VBA Script to Standardize Names (http://www.orangemane.com/BB/showthread.php?t=111835)

bronco0608 08-24-2013 04:51 PM

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:

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

Quote:

Originally Posted by Br0nc0Buster (Post 3901014)
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

Quote:

Originally Posted by bronco0608 (Post 3901028)
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

Quote:

Originally Posted by That One Guy (Post 3901082)
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

Quote:

Originally Posted by bronco0608 (Post 3901098)
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 b****.

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

Quote:

Originally Posted by That One Guy (Post 3901108)
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

Quote:

Originally Posted by bronco0608 (Post 3901141)
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

Quote:

Originally Posted by That One Guy (Post 3901142)
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

Quote:

Originally Posted by bronco0608 (Post 3901160)
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_lw...i6tmo1_500.gif

SonOfLe-loLang 08-25-2013 08:42 AM

Quote:

Originally Posted by orangeatheist (Post 3901214)
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_lw...i6tmo1_500.gif

I lol'd

bronco0608 08-25-2013 11:01 AM

Quote:

Originally Posted by CEH (Post 3901194)
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.

Jekyll15Hyde 08-25-2013 01:47 PM

Quote:

Originally Posted by That One Guy (Post 3901213)
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


All times are GMT -7. The time now is 06:32 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.