lineup equal values by inserting empty cells

[uffe1909]

I am trying to line up 2 sets of values wherer the 2 sets might contain the
same values. so for example:
set 1 set 2
1 2
2 5
3 7
5 10

The result should be
set 1 set 2
1
2 2
3
5 5
7
10
Any suggestions on how to get this result?

[Dave Peterson]

I put headers in row 1 and ran this:


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

uffe1909 wrote:
>
> I am trying to line up 2 sets of values wherer the 2 sets might contain the
> same values. so for example:
> set 1 set 2
> 1 2
> 2 5
> 3 7
> 5 10
>
> The result should be
> set 1 set 2
> 1
> 2 2
> 3
> 5 5
> 7
> 10
> Any suggestions on how to get this result?

--

Dave Peterson

[uffe1909]

Yes; this does the trick.
Thanks Dave

"Dave Peterson" wrote:

> I put headers in row 1 and ran this:
>
>
> 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
>
> uffe1909 wrote:
> >
> > I am trying to line up 2 sets of values wherer the 2 sets might contain the
> > same values. so for example:
> > set 1 set 2
> > 1 2
> > 2 5
> > 3 7
> > 5 10
> >
> > The result should be
> > set 1 set 2
> > 1
> > 2 2
> > 3
> > 5 5
> > 7
> > 10
> > Any suggestions on how to get this result?
>
> --
>
> Dave Peterson
>