#1
|
|||
|
|||
Need Macro or Formula Help
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! |
#2
|
|||
|
|||
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 |
#3
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
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 |