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

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

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
Temping with Staffmark
in Rock Hill, SC


"Dave Miller" wrote:

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

Glad to help.