line up columns in Excel

[jvdub22]

Im writing a program that takes 2 or more recordsets and dumps them into columns in Excel. Im dumping the recordsets one at a time. Once in Excel I need to line up the matching cells and shift non matching cells into a new row. What I need help with is the matching and shifting of the cells. For example if my spreadsheet has 4 columns that looks like this:

Recordset1     Recordset 2     Recordset3     Recordset4
 
   b              a              b               a
 
   c               b              c               d
 
   d               d              d               f
 
   e               e              f               g
 
    f
 
My program needs to sort the columns so that my spreadsheet looks like this:
 
                   a                               a
 
   b               b               b                      
 
   c                               c               
 
   d               d               d               d
 
   e               e                                             
 
   f                               f               f
 
                                                   g

Im having trouble writing an excel macro to compare the columns to see if they should line up and how to do the shifting of the cells if they dont match. Any help would be appreciated.

[si_the_geek]

I think moving them around wouldnt be the easiest way, I would recommend only putting them into the sheet in the first place if they are appropriate.

Something a bit like this (not tested):

'Load recordsets here
 
Dim RowNo_l as Long
 
RowNo_l = 1
Dim GotValue_b as Boolean
Dim CurrValue_v as Variant
Do While Not(Recordset1.Eof) And Not(Recordset2.Eof) _
      And Not(Recordset3.Eof) And Not(Recordset4.Eof)
  RowNo_l = RowNo_l + 1
  GotValue_b = False       'find the "lowest" value in the recordsets
 
  If Not (Recordset1.Eof) Then
    If Not(GotValue_b) Then 
      CurrValue_v = Recordset1(0)  '(change to your field name/number)
    Else
      If (Recordset1(0) < CurrValue_v) Then  CurrValue_v = Recordset1(0)  
      GotValue_b = True
    End If    
  End If
'(repeat the above block of code for all 4 recordsets)
 
 
                                'put the appropriate data in
  If Not (Recordset1.Eof) Then
    If CurrValue_v = Recordset1(0) Then 
       objSheet.Cells(RowNo_l,1) = Recordset1(0)   'change this line (and same below) as apt
       Recordset1.MoveNext 
    End If
  End If
  If Not (Recordset2.Eof) Then
    If CurrValue_v = Recordset2(0) Then 
       objSheet.Cells(RowNo_l,2) = Recordset2(0)
       Recordset2.MoveNext 
    End If
  End If
  If Not (Recordset3.Eof) Then
    If CurrValue_v = Recordset3(0) Then 
       objSheet.Cells(RowNo_l,3) = Recordset3(0)
       Recordset3.MoveNext 
    End If
  End If
  If Not (Recordset4.Eof) Then
    If CurrValue_v = Recordset4(0) Then 
       objSheet.Cells(RowNo_l,4) = Recordset4(0)
       Recordset4.MoveNext 
    End If
  End If
Loop