Alphabetize every second row

[kdr]

I have a rather large spreadsheet of ticket/certifications and expiries. I'd like to alphabetize the employees without mixing up their information; their names are in every second row with certification dates following, and on the row below their names underneith their certification dates are the expiries:

eg.

SMITH, John Jan 1, 2009
Expiry Jan 1, 2012
DOE, Jane March 2, 2009
Expiry March 2, 2012

And so on. The second rows all say "Expiry" so I don't want it to sort those too.

Can this be done?

 

[btadams]

Is

SMITH, John Jan 1, 2009

all in one cell or multiple cells?

 

[hiker95]

kdr,


Sample data before the macro, with Smith, John Jan 1, 2009 in cell A1:

Excel Workbook
	A
1	Smith, John Jan 1, 2009
2	Expiry Jan 1, 2012
3	Doe, Jane March 2, 2009
4	Expiry March 2, 2012
5
Sheet1

After the macro:

Excel Workbook
	A
1	SMITH, John Jan 1, 2009
2	Expiry Jan 1, 2012
3	DOE, Jane March 2, 2009
4	Expiry March 2, 2012
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MakeUpper()
' hiker95, 01/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=524737
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If Left(c, 6) <> "Expiry" Then
    c = UCase(Left(c, Application.Find(",", c, 1) - 1)) & Right(c, Len(c) - Application.Find(",", c, 1) + 1)
  End If
Next
Application.ScreenUpdating = True
End Sub

Then run the MakeUpper macro.

 

[kdr]

Oh, sorry! The name and 'expiry' are in column A2 and A3. The certificates and dates follow in columns B2 and B3 through AF2 and AF3. Can it be done without macros? (A little tinkering and I think I can figure it out if not)

 

[hiker95]

kdr,

I am not sure if you can do what you want with a formula.


Sample data before the updated macro:

Excel Workbook
	A	B
1
2	Smith, John	Jan 1, 2009
3	Expiry	Jan 1, 2012
4	Doe, Jane	Mar 2, 2009
5	Expiry	Mar 2, 2012
6
Sheet1

After the updated macro:

Excel Workbook
	A	B
1
2	SMITH, John	Jan 1, 2009
3	Expiry	Jan 1, 2012
4	DOE, Jane	Mar 2, 2009
5	Expiry	Mar 2, 2012
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub MakeUpperV2()
' hiker95, 01/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=524737
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
  If Left(c, 6) <> "Expiry" Then
    c = UCase(Left(c, Application.Find(",", c, 1) - 1)) & Right(c, Len(c) - Application.Find(",", c, 1) + 1)
  End If
Next
Application.ScreenUpdating = True
End Sub

Then run the MakeUpperV2 macro.

 

[kdr]

hiker95: I don't see your version alphabetizing...just capitalizing their last names? Jane Doe's name should be above John Smith's and her march dates should move along with her...

 

[hiker95]

kdr,

Now I understand.


Sample data before the macro (accounting for/calculating the last used column in your dataset):

Excel Workbook
	A	B
1
2	SMITH, John	Jan 1, 2009
3	Expiry	Jan 1, 2012
4	DOE, Jane	Mar 2, 2009
5	Expiry	Mar 2, 2012
6
Sheet1

Then we insert a column and create unique identifiers for each name:

Excel Workbook
	A	B	C
1
2	SMITH, John2	SMITH, John	Jan 1, 2009
3	SMITH, John3	Expiry	Jan 1, 2012
4	DOE, Jane4	DOE, Jane	Mar 2, 2009
5	DOE, Jane5	Expiry	Mar 2, 2012
6
Sheet1

Then we sort the entire dataset by column A:

Excel Workbook
	A	B	C
1
2	DOE, Jane4	DOE, Jane	Mar 2, 2009
3	DOE, Jane5	Expiry	Mar 2, 2012
4	SMITH, John2	SMITH, John	Jan 1, 2009
5	SMITH, John3	Expiry	Jan 1, 2012
6
Sheet1

Then we delete column A, and we have:

Excel Workbook
	A	B
1
2	DOE, Jane	Mar 2, 2009
3	Expiry	Mar 2, 2012
4	SMITH, John	Jan 1, 2009
5	Expiry	Jan 1, 2012
6
Sheet1

Is this what you are looking for?

 

[hiker95]

kdr,

Per the latest screenshots:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 01/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=524737
Dim LR As Long, LC As Long, a As Long
Application.ScreenUpdating = False
Columns(1).Insert
LR = Cells(Rows.Count, 2).End(xlUp).Row
LC = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
For a = 2 To LR Step 2
  Range("A" & a).FormulaR1C1 = "=RC[1]&ROW()"
  Range("A" & a + 1).FormulaR1C1 = "=R[-1]C[1]&ROW()"
  With Range("A" & a & ":A" & a + 1)
    .Value = .Value
  End With
Next a
Range(Cells(2, 1), Cells(LR, LC)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Columns(1).Delete
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.