Need Macro or Formula Help

mbocian · #1 03-24-2011, 06:11 AM

I am trying to take an exported report and reformat it so that it can be imported into another system. I cleaned up the spreadsheet as much as possible, but column B has the address and phone number in mutliple rows under each name (the name in Column A) This is an adress list that has approximately 4800 contacts in it. I need each address to be in one row, multiple columns. I am struggling to find a macro that will workk because the number of rows in between each entry is inconsistent. Can anyone help?? I attached a small sample of what the spreadsheet looks like. Thanks!

OTPM · #2 04-27-2011, 06:48 AM

Hi mbocian
I amnot sure there is a macro that could do what you want however there is a way you can do it using the basic search and replace commands in MS Word. If you follow the instructions below word for word it will do 98% of what you want and you will then need to do a little tidying up in Excel at the end of the process. Follow the steps in exactly the order shown:
1. Save the file as a text file (csv).
2. Open the text file with MS Word (any version will do).
3. Enter Ctrl+H (this will invoke the search and replace dialogue box).
Note: When entering in the search and replace boxes enter exactly what is between the single quote marks in each case only - take care to also enter a space where shown.
4. In the search box enter '^p^p' and in the replace with box enter '^p'. Then enter Alt+H and then click on the OK buton.
5. In the search box enter '^p"' and in the replace with box enter '^p'. Then enter Alt+H and then click on the OK buton.
6. In the search box enter '",,' and in the replace box enter ',,'. Then enter Alt+H and then click on the OK buton.
7. In the search box enter '"^t' and in the replace box enter '^t'. Then enter Alt+H and then click on the OK buton.
8. In the search box enter '",' and the the replace box enter '^t. Then enter Alt+H and then click on the OK buton.
9. In the search box enter '^t"' and in the replace box enter '^t'. Then enter Alt+H and then click on the OK buton.
10. In the serch box enter '"^p' and in the replace box enter '^p'. Then enter Alt+H and then click on the OK buton.
11. In the search box enter ', ' (remember the space here) and in the replace box enter '^t'. Then enter Alt+H and then click on the OK buton.
12. Save the file (do not change the format when you save it).
13. Open Excel and import the text file making sure you use "Tab" as the delimiter.
You will have some minor tidying up to do but this process will get you approximately 98% what you want.
Hope this helps.
TKHussar

macropod · #3 04-28-2011, 02:04 AM

Hi mbocian,

Here's a macro (sorry OTPM

) that takes your data, assumed to be on the first worksheet in the workbook and outputs it to the second worksheet:

Code:

Sub Demo()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Sht1LastRow As Long, Sht1ThisRow As Long, Sht1DataRow As Long
Dim Sht2 As Worksheet, Sht2ThisRow As Long, Sht2ThisCol As Long
With ActiveWorkbook
  Set Sht2 = .Sheets(2)
  Sht2ThisRow = Sht2.Cells.SpecialCells(xlCellTypeLastCell).Row
  With .Sheets(1)
    Sht1LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    For Sht1ThisRow = 1 To Sht1LastRow
      If Trim(.Range("A" & Sht1ThisRow).Value) <> "" Then
        Sht2ThisRow = Sht2ThisRow + 1
        Sht2.Range("A" & Sht2ThisRow).Value = .Range("A" & Sht1ThisRow).Value
        If .Range("C" & Sht1ThisRow).Value <> "" Then
          Sht2.Range("B" & Sht2ThisRow).Value = .Range("C" & Sht1ThisRow).Value
        End If
        Sht2ThisCol = 3
        If .Range("B" & Sht1ThisRow).Value = "" Then Sht1ThisRow = Sht1ThisRow + 1
        For Sht1DataRow = Sht1ThisRow To Sht1LastRow
          If .Range("B" & Sht1DataRow).Value = "" Then
            Sht1ThisRow = Sht1DataRow
            Exit For
          End If
          Sht2.Cells(Sht2ThisRow, Sht2ThisCol).Value = .Range("B" & Sht1DataRow).Value
          Sht2ThisCol = Sht2ThisCol + 1
        Next Sht1DataRow
      End If
    Next Sht1ThisRow
  End With
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

I haven't attempted to arrange the output data into anything consistent as I don't know what your requirements might be given that there is so much variability in the data.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
PMT Formula	OneOleGuy	Excel	4	03-05-2011 09:54 AM
Help with Formula	Corca	Excel	6	02-22-2010 09:40 PM
If formula	sixhobbits	Excel	1	10-02-2009 08:02 AM
Help for formula	dehann	Excel	5	05-01-2009 10:44 AM
Need help with a formula	tinkertron	Excel	11	04-16-2009 11:43 PM