Please help with this macro
 

Hi,

Could someone please help.
I've got 2 columns: A & B as per below.
I want to write a macro so that if the data in column A matches col B
then put them in the same line.
A B
Row 1 QQQQ SSSS
Row 2 WWW QQQQ
Row 3 EEEE UUUUU
Row 4 RRRR EEEEE
In this case A1 is the same as B2. I want to insert a row in Column A
so that in looks like the below. This will go on for each row.
A B
Row 1 SSSS
Row 2 QQQQ QQQQ
Row 3 WWW UUUUU
Row 4 EEEE EEEEE

Please help with this macro
 

If you started with:

HDR1 HDR2
QQQQ SSSS
WWW QQQQ
EEEE UUUUU
RRRR EEEE

And wanted to end with:

HDR1 HDR2
EEEE EEEE
QQQQ QQQQ
RRRR
SSSS
UUUUU
WWW

Column A and column B were sorted before the line-ups occurred, you could use
this...

Add a header to row 1 and try this macro that I've saved this from a few
previous posts:

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

'change the mycols to the number of columns that
'are associated with column B

myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

missk wrote:

Hi,

Could someone please help.
I've got 2 columns: A & B as per below.
I want to write a macro so that if the data in column A matches col B
then put them in the same line.
A B
Row 1 QQQQ SSSS
Row 2 WWW QQQQ
Row 3 EEEE UUUUU
Row 4 RRRR EEEEE
In this case A1 is the same as B2. I want to insert a row in Column A
so that in looks like the below. This will go on for each row.
A B
Row 1 SSSS
Row 2 QQQQ QQQQ
Row 3 WWW UUUUU
Row 4 EEEE EEEEE

--

Dave Peterson