View Single Post
Old 08-25-2013, 01:00 AM   #15
That One Guy
Producer of Nonsense
 

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

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 01:07 AM..
That One Guy is offline   Reply With Quote