concatenate cells and delete rows with a twist

**rudyrod** · 06-05-2009, 04:27 PM

I have a spreadsheet with three columns (check #, invoice #, vendor #). If a check pays multiple invoices, there is a separate row for each instance of invoice #. (ie if a check pays 10 invoices, there are 10 rows all with the same value for check # and vendor #, but different invoice #s). I need to concatenate the multiple rows to just one row per check #, with all the invoice #s in separate columns on the same row and delete the duplicate check # rows. However, I can only use 10 columns, so if there are more than 8 invoices matching the same check #, the 10th column will need to contain invoices 9-n concatenated and separated by a space.

I have over 5000 rows in the spreadsheet.

**rudyrod** · 06-05-2009, 04:36 PM

This is what I have come up with so far for the concatenation part:

Function CONCAT(Cells As Range, Optional Delimiter As String = "") As String
'Concatenates a Range of Cells with a Delimiter
    Dim CELL As Range
    For Each CELL In Cells
        If CELL.Value <> "" Then CONCAT = CONCAT & CELL.Value & Delimiter
    Next CELL
    CONCAT = Left(CONCAT, Len(CONCAT) - Len(Delimiter))
End Function

Usage is =ConCat (A1:A6, “ “) entered in a cell.

**rudyrod** · 06-08-2009, 09:16 AM

Well I soved it, here is the code:


Sub test()
    Dim r As Long
    Dim inv_cnt As Long
    Dim col_num As Long
    Dim sk As Long
         
    r = [a65536].End(xlUp).Row
    inv_cnt = 1
    col_num = 3
    sk = 1
     
    For i = 1 To r Step 1
    
        If Cells(sk, 1) = Cells(sk + 1, 1) Then
        
          If inv_cnt < 8 Then
              Cells(sk, (col_num + 1)) = Cells((sk + 1), 3).Value
          Else
              Cells(sk, 10) = Cells(sk, 10).Value & " " & Cells((sk + 1), 3).Value
          End If
          
          inv_cnt = inv_cnt + 1
          col_num = col_num + 1
          Rows(sk + 1).Delete
    
        Else
          inv_cnt = 1
          sk = sk + 1
          col_num = 3
        End If
        
    Next i
End Sub