Formatting a spreadsheet

[cyb3rwolf]

forgive me, not that experienced with exel. I am using exel 2007. I have a
spread sheet that lists off names of companies and lists e-mail contacts for
that company. Column A lists the different companies, and column B lists the
e-mail addresses. All companies have at least 2 e-mail contacts, so columna
will have the same company name repeated for howmany e-mail contacts there
are for that company in column b. What i need to do is only have one line
for each company, with each of the columns after that having the different
e-mail addresses. (Column A would be the company, column b would be the first
e-mail contact, column c would be the second e-mail contact, etc.). Anybody
help me out in an easy way to accomplish this? It is a very large spread
sheet.

 

[dhstein]

I'm going to give you a method that assumes that there are a maximum of 2
email addresses per company. You can modify this procedure for whatever the
maximum email count is - you just have to use more columns.

Step 1:

Assume you have data starting in Row5

In Cell C5 enter the following formula: =B6

In Cell D5 enter the following formula: =IF(A5=A6,1,0)

Step 2:

Copy these formulas all the way down. You will now have rows that
contain either a 1 or a 0 in column D. The rows that have a 1 should be what
you want.
Copy and paste special values for columns C and D. Then sort on column D to
get just the data you want.

 

[JLatham]

And here is a solution that will move emails from same named companies and
eventually delete the rows with the entries where emails were copied up. The
list does need to be sorted by the company name.

To use the code: Make a copy of your workbook to use just in case this
turns out not to work out the way you want. Open that copy.
Press [Alt]+[F11] to open the Visual Basic editor (VBE). In the VBE use
it's menu to Insert | Module.
Copy the code below and paste it into the empty module presented to you.
Close the VB Editor.
Choose the sheet with the company/email lists on it.
From the Excel Menu choose: Tools | Macro | Macros and highlight the entry
for ReorganizeEmails and click the [Run] button.

It should work very quickly for you. If things look alright to you, you can
save the workbook over the original, or just keep the old one for a backup
copy.

Sub ReorganizeEmails()
'the sheet with the lists must
'be selected when you run this
Dim lastRow As Long
Dim columnOffset As Integer
Dim currentCompany As String
Dim outerLoop As Long
Dim innerLoop As Long

lastRow = ActiveSheet.Range("A" & _
Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For outerLoop = 1 To lastRow - 1
If Not IsEmpty(Range("A" & outerLoop)) And _
UCase(Trim(Range("A" & outerLoop))) <> _
currentCompany Then
currentCompany = _
UCase(Trim(Range("A" & outerLoop)))
columnOffset = 2
For innerLoop = outerLoop + 1 To lastRow
If UCase(Trim(Range("A" & innerLoop))) = _
currentCompany Then
Range("A" & outerLoop).Offset(0, columnOffset) = _
Range("A" & innerLoop).Offset(0, 1)
columnOffset = columnOffset + 1
'erase company name so we
'can delete the rows later
Range("A" & innerLoop) = ""
Else
'new company name
Exit For
End If
Next ' innerLoop
End If
Next ' outerLoop
'erase the entries we copied
'the emails from
For outerLoop = lastRow To 1 Step -1
If IsEmpty(Range("A" & outerLoop)) Then
Range("A" & outerLoop).EntireRow.Delete
End If
If ActiveCell.Row = 1 Then
Exit For
End If
Next
End Sub

 

[JLatham]

I should have mentioned that this code will work when there are any number of
emails for a company, from 1 to lots, as long as the list is sorted by
company name.

And here is a solution that will move emails from same named companies and
eventually delete the rows with the entries where emails were copied up. The
list does need to be sorted by the company name.

To use the code: Make a copy of your workbook to use just in case this
turns out not to work out the way you want. Open that copy.
Press [Alt]+[F11] to open the Visual Basic editor (VBE). In the VBE use
it's menu to Insert | Module.
Copy the code below and paste it into the empty module presented to you.
Close the VB Editor.
Choose the sheet with the company/email lists on it.
From the Excel Menu choose: Tools | Macro | Macros and highlight the entry
for ReorganizeEmails and click the [Run] button.

It should work very quickly for you. If things look alright to you, you can
save the workbook over the original, or just keep the old one for a backup
copy.

Sub ReorganizeEmails()
'the sheet with the lists must
'be selected when you run this
Dim lastRow As Long
Dim columnOffset As Integer
Dim currentCompany As String
Dim outerLoop As Long
Dim innerLoop As Long

lastRow = ActiveSheet.Range("A" & _
Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For outerLoop = 1 To lastRow - 1
If Not IsEmpty(Range("A" & outerLoop)) And _
UCase(Trim(Range("A" & outerLoop))) <> _
currentCompany Then
currentCompany = _
UCase(Trim(Range("A" & outerLoop)))
columnOffset = 2
For innerLoop = outerLoop + 1 To lastRow
If UCase(Trim(Range("A" & innerLoop))) = _
currentCompany Then
Range("A" & outerLoop).Offset(0, columnOffset) = _
Range("A" & innerLoop).Offset(0, 1)
columnOffset = columnOffset + 1
'erase company name so we
'can delete the rows later
Range("A" & innerLoop) = ""
Else
'new company name
Exit For
End If
Next ' innerLoop
End If
Next ' outerLoop
'erase the entries we copied
'the emails from
For outerLoop = lastRow To 1 Step -1
If IsEmpty(Range("A" & outerLoop)) Then
Range("A" & outerLoop).EntireRow.Delete
End If
If ActiveCell.Row = 1 Then
Exit For
End If
Next
End Sub

forgive me, not that experienced with exel. I am using exel 2007. I have a
spread sheet that lists off names of companies and lists e-mail contacts for
that company. Column A lists the different companies, and column B lists the
e-mail addresses. All companies have at least 2 e-mail contacts, so columna
will have the same company name repeated for howmany e-mail contacts there
are for that company in column b. What i need to do is only have one line
for each company, with each of the columns after that having the different
e-mail addresses. (Column A would be the company, column b would be the first
e-mail contact, column c would be the second e-mail contact, etc.). Anybody
help me out in an easy way to accomplish this? It is a very large spread
sheet.