Microsoft Excel Expert Please Help!?

Question

I need to rearrange an excel list for applicants to Univ. of Rhode Island (interning in Admissions Office). We have about 13,000 names to organize. Right now the list has the applicant's first name, last name, and email in one row. then parent 1 (of that applicant) first name, last name, and email in the second row.

I need to make it so that 1 row consists of the applicant, the parent, and the email addresses.

So that if you look at row 1, column A is applicant 1's first name. column B is applicant 1's last name. column C is applicant 1's email. column D is applicant 1's parent first name. column E is that parent's last name. Column F is that same parent's email etc.

and then row 2 is the next applicant's name, then parents in the following columns.


Is there any way that I can do this on Excel that would be easier than just copy and pasting 13,000+ names?


Any help would be greatly appreciated! Thank you!!!

Answer 1

1. from your worksheet, press Alt + F11 (open VBE)

2. menu: Insert > Module

3. Write (or just Copy/paste) the below VBA coding into your module

4. press Alt+F11 (Back to Sheet, where your list is in)

5 Press Alt + F8 > Select Macro Name (that is the same name

of the Sub Procedures that you have written in VBA Module)

6. Press RUN button.



The New List is generated in a New Sheet.



Here is the coding



'----- start of coding ----------------

Sub ReOrganizeYourData()

' by siti Vi / [e-mail]

' jakarta, indonesia, nov 06, 2009

'-------------------------------------

Dim RefDat As Range

Dim NewSht As Worksheet

Dim NewDat As Range

Dim n As Long, r As Long

Dim nRow As Long, nCol As Integer



Set RefDat = ActiveSheet.UsedRange.Offset(1, 0)

nRow = RefDat.Rows.Count

nCol = RefDat.Columns.Count

Set RefDat = RefDat.Resize(nRow - 1, nCol)



Set RefDat = Application.InputBox( _

Prompt:="Select your Old Data (Excluding the Row Heading)", _

Title:="Sigma Kappa - Re-Org Your Data", Default:=RefDat.Address, Type:=8)

Application.ScreenUpdating = False



Set NewSht = ThisWorkbook.Sheets.Add

Set NewDat = NewSht.Cells(3, 2)

NewSht.Name = "NewData_" & Sheets.Count



NewDat(0, 1) = "Appl_1stName"

NewDat(0, 2) = "Appl_LastName"

NewDat(0, 3) = "Appl_Email"

NewDat(0, 4) = "Parent_1stName"

NewDat(0, 5) = "Parent_LastName"

NewDat(0, 6) = "Parent_Email"



For r = 1 To nRow - 1

If r Mod 2 > 0 Then

n = n + 1

NewDat(n, 0) = n

RefDat(r, 1).Resize(1, nCol).Copy

NewDat(n, 1).PasteSpecial xlPasteValuesAndNumberFormats

RefDat(r + 1, 1).Resize(1, nCol).Copy

NewDat(n, nCol + 1).PasteSpecial xlPasteValuesAndNumberFormats

End If

Next r

Columns("B:H").EntireColumn.AutoFit

Application.CutCopyMode = False

Application.ScreenUpdating = True

MsgBox "Data Re-Org was Done, ( " & n & " ) Records.", 64, "Sigma Kappa"

Cells(2, 2).Activate

End Sub

'------- end of coding -----

Answer 2

First:

Select column A

Select from the menu Data\ Text to Columns

--Select "Delimited" and Next

--Select the "Space" check-box and Finish



This will split all the First names, Last names and Emails into columns A, B and C. You may have to scroll through all the data and correct anomalies like People with a two word last names e.g. Van Hussen etc.



2nd

In D1 type TRUE

In D2 type FALSE

Select D1 and D2 and Drag\Copy them down column D for all the 1300+ names you have. This will put True\False in alternating rows in column D.



Select Column D

Select from the menu Data\ Filter\ Autofilter

This will put a drop-down list in D1

Select from the drop-down list "TRUE"

This will hide all the "False" rows



Select all the visible data and copy

Select Cell A1 on a blank sheet and select Paste Special: Values



Back to the D1 drop-down list select "False"

This will hide all the "TRUE" rows

Select all the data and copy

Select D1 on the sheet you pasted to previously and Paste Special: Values



Done

Answer 3

I don't know if it can be easier than this .. it is the only way i know so far. Insert a column on where the Parents' name is to be inserted, now, click on the cell and type = and click on the Parent's name below. Now, both the cells have the same content. Click and drag on the copy handler in the cell and drag it down to copy the formula to the following rows. After completing, press Ctrl+A to copy all the cells in the worksheet and go to Format - Cells and change the cell types to General or Text so that all the formulae became plain texts. The next step is, to delete the rows containing the Parents' names one by one or Hold Ctrl and click on the row handlers (?), (the bar where row numbers are displayed, you click those when you resize the cells) to selct them in groups, right click on one selected row and choose delete. Good Luck.