Consolidate Rows?

[Dave Birley]

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 Birley]

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 Birley]

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

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

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 Birley]

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

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

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

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 Miller]

Dave,

This worked for me:

1. Open the sheet you want to pull the unique records from.
2. Select the Data you would like to use as the table and Create a
Named Range called tblSSN
3. Save and close this sheet.
4. Open a new sheet; Open VBE and set a reference to Microsoft DAO
3.6 (or whatever version you have)
5. Paste the code below into a module and run it.

Regards,

David Miller

'*****************************************************************************************************************************

Function SelectDistinct()
Dim dbs As DAO.Database, _
rst As DAO.Recordset, _
SQL, sFile As String

sFile = "I:\ExcelDB.xls"
Set Db = OpenDatabase(sFile, False, False, "Excel 8.0;HDR=No;")

SQL = "SELECT DISTINCT * " & _
"FROM `" & sFile & "`.tblSSN tblSSN"

Set rst = Db.OpenRecordset(SQL)

ActiveSheet.Range("A1").CopyFromRecordset rst

Set rst = Nothing
Set dbs = Nothing
End Function

 

[Dave Birley]

Thanks, Dave -- very elegant, and this is something to add to my collection
of "new Toys". It is easy for me to know what I want to do -- all programming
is essentially the same. However learning to select the right tool, or even
find its name is the drag. I've been messing with data here for over a week,
and the supervisor of my assignment only shows up for a couple of hours a
week. Mostly just guessing what she wants!

 

[Dave Miller]

Glad to help.