Re: Excel Row/Column Merge

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Sat, 9 Jun 2007 13:12:01 -0700, Billy Liddel
<BillyLiddel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:


You can do this with a macro. I can't think how to do this in one go yet but
this macro will add to the person first line in stages so keep running it
until the the number of changes stays constant. You'll be told this.

Sub t()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp, count As Long, inf
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
count = 0
'copy data to top row of each person
For i = 2 To nr
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i + 1, 1) & Cells(i + 1, 2) & Cells(i + 1, 3)

If c = d Then
For j = 4 To nc
If IsEmpty(Cells(tmp, j)) Then
Cells(tmp, j) = Cells(i + 1, j)
count = count + 1
End If
Next j
End If
Next i
inf = MsgBox("Changes Made: " & count, vbDefaultButton1, "Changes made to
sheet")
End Sub

When you are happy use the next sub to delete the dupilcate rows. Only once
will do.

Sub DelDupes()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
'copy data to top row of each person
For i = nr To 2 Step -1
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i - 1, 1) & Cells(i - 1, 2) & Cells(i - 1, 3)
If c = d Then
Range(Cells(i, 1), Cells(i, nc)).Delete
End If
Next i
End Sub

OPen the VB Editor (ALT + F11), >Insert >Module and copy the code into the
Module. the code will work on the active sheet in the workbook. Place the
cursor into Sub T and press F5 to run the code.
Do not run the DelDupes until you are happy with the results. Perhaps you
can copy the workbook and use this.

Regards
Peter

Hello Peter

Thank you for taking the time to, reply I wish I could say that I
understood all your hard work.

I have run this twice, each time the first formula runs to 1067
changes and then remains static.

Then I run the second macro.

This merges some of the duplicated names but not all of them. The
lines that are not merged are identical.

I don't know where I am going wrong.


.

Relevant Pages

  • Re: How can my macro run faster ?
    ... I'd keep all those "Sub/End Sub" statements. ... ' SortDossierOrder Macro ... Dim wks As Worksheet ... Dim cLastRow As Long ...
    (microsoft.public.excel.newusers)
  • Re: Add rows/form fields on doc w/ multiple tables & calculate
    ... ' Macro written 12/01/2003 by Charles Kyle Kenyon ... Dim oTemplate As Template ... .CorrectInitialCaps = True ... Sub InsertRowAboveMe() ...
    (microsoft.public.word.vba.general)
  • Re: condition formula in Word 2003
    ... It includes a macro to reset the address. ... Sub AutoNew() ... Dim SettingsFile As String ...
    (microsoft.public.word.docmanagement)
  • insert rows--->protected sheet
    ... I have a protected sheet in Excel 2007, and I would like to write a macro ... Dim ChangeProtection As Boolean ... Sub InsertRowsAndFillFormulas_caller ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Form refresh code
    ... Your version of the InserrtAddress macro also unlocked the field with the ... string variable name in the protect/unprotect commands. ... Public Sub InsertAddressFromOutlook() ... Dim strCode As String, strAddress As String ...
    (microsoft.public.word.docmanagement)