RE: Excel Row/Column Merge

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




I was given 15 separate sheets with group lists, some of the people on
group 1 also take part in up to four other groups.

My spreadsheet is set up as follows

Column A contains first name
Column B Surname
Column C Section
Then Columns D - S my various groups

So Tom Jones may have appear on rows 1 - 5 with one entry in each of
columns D F G K M
Hi

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




.

Relevant Pages

  • Re: Copy to Visible Cells only;Modify Code
    ... CommandButton1 with caption "Find Source Workbook" ... Private Sub CommandButton1_Click ... Dim wbSource As Workbook ...     Application.AutomationSecurity _ ...
    (microsoft.public.excel.programming)
  • Re: Create Right Click Option to Copy Cell Address
    ... I now have a right click menu generated in a regular workbook. ... Private Sub Workbook_BeforeClose ... Dim MyDataObj As New DataObject ... Dim ctrl As CommandBarControl ...
    (microsoft.public.excel.programming)
  • RE: Countdown timer
    ... Just replace your existing code in the Workbook code module with the code ... Notice that one Const is declared outside of the Sub codes. ... Dim RunTime As Variant ... 'NOTE: once DisplayTimeRemaining is called ...
    (microsoft.public.excel.programming)
  • Re: Delete VB code
    ... After you do the sheets copy the new workbook will be the activeworkbook. ... Sub DeleteVBA ... Dim Links As Variant ...
    (microsoft.public.excel.programming)
  • Re: workbook to access
    ... Sub createbk() ... Dim wbkNew As Workbook, srcbk As Workbook ... Dim lng As Long ...
    (microsoft.public.excel.programming)