Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Condensing Rows (2k and 2003)

    Hi, I was hoping someone could help me out with a macro I'm attempting to write (unsuccessful so far). I have a list of data that is about 100 items long with repetitive data. I want to condense the data to unique values and at the same time, put the amount of times they were repeated. I only need to check the first column for uniqueness. I took a look at Hans' code in post 455059 and I wasn't really able to make anything out of it for what I need (i got lost in the middle of it). I've attached an example that shows in sheet 1 what I start with and sheet 2 what I want to end with. This was the macro Hans had written earlier:

    Sub condense()
    ' Declarations
    Dim lngRow As Long
    Dim lngCurRow As Long
    Dim lngMaxRow As Long
    Dim lngTempRow As Long
    Dim lngCol As Long
    Dim lngMaxCol As Long
    Const lngMinRow As Long = 5

    ' Determine last row and column
    With ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
    lngMaxRow = .Row
    lngMaxCol = .Column
    End With

    ' Loop backwards
    lngRow = lngMaxRow
    Do While lngRow > lngMinRow
    lngCurRow = lngRow
    ' Find cell with top border
    Do While Cells(lngCurRow, 1).Borders(xlEdgeTop).LineStyle = xlLineStyleNone
    lngCurRow = lngCurRow - 1
    Loop
    ' Concatenate values
    For lngCol = 1 To lngMaxCol
    For lngTempRow = lngCurRow + 1 To lngRow
    If Cells(lngTempRow, lngCol) <> "" Then
    If Cells(lngCurRow, lngCol) <> "" Then
    Cells(lngCurRow, lngCol) = _
    Cells(lngCurRow, lngCol) & Chr(10)
    End If
    Cells(lngCurRow, lngCol) = _
    Cells(lngCurRow, lngCol) & Cells(lngTempRow, lngCol)
    End If
    Next lngTempRow
    Next lngCol
    ' Delete superfluous rows
    If lngRow > lngCurRow Then
    Range((lngCurRow + 1) & ":" & lngRow).Delete
    End If
    lngRow = lngCurRow - 1
    Loop

    ' Set vertical alignment to top
    Cells(lngMinRow, 1).CurrentRegion.VerticalAlignment = xlVAlignTop

    End Sub
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Condensing Rows (2k and 2003)

    You can get what you're looking for by using Subtotals and telling it to count the occurrences at each change in Column A. You'll need to add a label at the top of the column.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Condensing Rows (2k and 2003)

    that doesn't condense anything
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Condensing Rows (2k and 2003)

    oh but yes, it does...perhaps just not in the way you were looking for?

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Condensing Rows (2k and 2003)

    If you don't want all those hidden lines of data inbetween the condensed data, you can use the GO TO feature to select Visible Cells Only. Then you can copy the visible data and paste it into a blank cell below the list. Also, you can use Find and Replace to remove the word " Count". Select the values in the A Column, choose Edit | Replace and Find " Count" and Replace with (leave blank).
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condensing Rows (2k and 2003)

    A pivot table is another option (Data | PivotTable and PivotChart Report...). See attached workbook.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Condensing Rows (2k and 2003)

    well subtotals aren't working for me, i keep getting 0 as the total count of anything.. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    i'll try the pivot table out
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Condensing Rows (2k and 2003)

    pivot table definitely the way to go, thanks <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •