I have some reference data (SSN, Last, First) which has multiple rows of most
items:

A B C
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-54-9876 Whacko Marie
123-54-9876 Whacko Marie

I need to consolidate this data onto a worksheet where I have only one of
each set, and, of course, no intervening blank rows.

Thinking Macro here, of course. A little help, if you please <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC

I thought this might work:

Sub ConsolidateRows()
Dim myCount, RowCount As Integer
Dim myCell, NewCell As Range
myCount = 0
RowCount = 7125
For Each myCell In Range("A7125", Range("A65536").End(xlUp))
With myCell
If .Value & .Offset(0, 1).Value & .Offset(0, 2).Value < .Offset(1,
0).Value & .Offset(1, 1).Value & .Offset(1, 2).Value Then
NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"
NewCell(RowCount, 5).FormulaR1C1 = "=RC[-4]"
NewCell(RowCount, 6).FormulaR1C1 = "=RC[-5]"
myCount = 0
RowCount = RowCount + 1
Else
myCount = myCount + 1
End If
End With
Next myCell
End Sub

....but, of course, it doesn't like the call to RowCount in

NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"

Am I close?
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

I have some reference data (SSN, Last, First) which has multiple rows of most
items:

A B C
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-54-9876 Whacko Marie
123-54-9876 Whacko Marie

I need to consolidate this data onto a worksheet where I have only one of
each set, and, of course, no intervening blank rows.

Thinking Macro here, of course. A little help, if you please <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC

I tried Filtering (Advanced Filter -- Unique rows), but, although I reduced
the number of rows a lot, I still had at least two of any row that had two or
more to start with.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

I thought this might work:

Sub ConsolidateRows()
Dim myCount, RowCount As Integer
Dim myCell, NewCell As Range
myCount = 0
RowCount = 7125
For Each myCell In Range("A7125", Range("A65536").End(xlUp))
With myCell
If .Value & .Offset(0, 1).Value & .Offset(0, 2).Value < .Offset(1,
0).Value & .Offset(1, 1).Value & .Offset(1, 2).Value Then
NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"
NewCell(RowCount, 5).FormulaR1C1 = "=RC[-4]"
NewCell(RowCount, 6).FormulaR1C1 = "=RC[-5]"
myCount = 0
RowCount = RowCount + 1
Else
myCount = myCount + 1
End If
End With
Next myCell
End Sub

...but, of course, it doesn't like the call to RowCount in

NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"

Am I close?
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

I have some reference data (SSN, Last, First) which has multiple rows of most
items:

A B C
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-54-9876 Whacko Marie
123-54-9876 Whacko Marie

I need to consolidate this data onto a worksheet where I have only one of
each set, and, of course, no intervening blank rows.

Thinking Macro here, of course. A little help, if you please <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC

When the "New" duplicate SSN row is sufficiently narrow, the pairs display
with the first row's cell displaying ######## and the second one the SSN. I
can detect no difference in the data in these two cells (including Format).
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

I tried Filtering (Advanced Filter -- Unique rows), but, although I reduced
the number of rows a lot, I still had at least two of any row that had two or
more to start with.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

I thought this might work:

Sub ConsolidateRows()
Dim myCount, RowCount As Integer
Dim myCell, NewCell As Range
myCount = 0
RowCount = 7125
For Each myCell In Range("A7125", Range("A65536").End(xlUp))
With myCell
If .Value & .Offset(0, 1).Value & .Offset(0, 2).Value < .Offset(1,
0).Value & .Offset(1, 1).Value & .Offset(1, 2).Value Then
NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"
NewCell(RowCount, 5).FormulaR1C1 = "=RC[-4]"
NewCell(RowCount, 6).FormulaR1C1 = "=RC[-5]"
myCount = 0
RowCount = RowCount + 1
Else
myCount = myCount + 1
End If
End With
Next myCell
End Sub

...but, of course, it doesn't like the call to RowCount in

NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"

Am I close?
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Birley" wrote:

I have some reference data (SSN, Last, First) which has multiple rows of most
items:

A B C
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-45-6789 Bozo Fred
123-54-9876 Whacko Marie
123-54-9876 Whacko Marie

I need to consolidate this data onto a worksheet where I have only one of
each set, and, of course, no intervening blank rows.

Thinking Macro here, of course. A little help, if you please <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC