Count rows betwen two numbers and add below - VB

[Tarwit]

I have two colums. Both include 8 digits numbers with shorter numbers between. I want to calculate how many rows between two 8 digits numbers have first one and the second one and then I need to add second column next to the first one but with adding missing rows (difference beetwen first one and second one). For example - fist column has 9 rows and second one has 12, so I need to paste second column next to the first with adding 3 extra rows below last row in first column.

[Reverend Jim]

Sounds like a homework assignment. What have you done so far?

[Hazuan Nazri]

i cant understand what do you want....please explain it clearly.

[Tarwit]

Hi, It's not a homework assignment, I hope it makes my life easier ;)

I give an example:
First column:
12345678
12578
69782
12547
36894
36548
16974
69836
78954
99789
12347965

Second column (this one is in seperate excel file):
12345678
36894
36548
16974
69836
78954
99789
12578
69782
12547
12578
69782
12547
12345678

I need to paste second column next to first one. Frist has 9 shorter numbers, second one has 12, so I need to paste it with adding 3 rows below the last row with shorter number.

Is it clear now?

[Reverend Jim]

So you want to end up with

12345678      12345678    
   12578         36894 
   69782         36548 
   12547         16974 
   36894         69836 
   36548         78954 
   16974         99789 
   69836         12578 
   78954         69782 
   99789         12547 
12347965         12578    
                 69782
                 12547      
              12345678                

[Tarwit]

Yes ;)!

[Tarwit]

but numbers with 8 digits should be in the same row 14

[Tarwit]

Sorry, but I begin to use VBA and I want/need to learn more. I will be apreaciated for any clues.

[Reverend Jim]

Then you'll have to post what the output is because I don't know what you want to do to line things up. While you are at it, can you please post the exact format of the two excel files?

[Tarwit]

I have two excel file with the same data but one could have more data assigned to 8digits numbers. All 8digits numbers have shorter numbers assigned to them.

 1.   12345678      12345678    **
 2.    12578         12578 
 3.    69782         69782 
 4.    12547         12547 
 5.    36894         36894 
 6.    36548         36548 
 7.    16974         16974 
 8.    69836         69836 
 9.    78954         78954 
 10.   99789         99789 
 11.                 12578
 12.                 69782
 13.                 12547
 14.  12345677      12345677 
 15.   99789         99789 
 16.   69836         69836 
 17.                 78954   
 18.  12345678      12345678 

**

It is something like that, two columns in saparate files. I need to paste second one (on right) next to first one but script should calculate how many rows are missing (between 8digits numbers) and add them before pasting.

[Reverend Jim]

That becomes a little more difficult. What are we guaranteed?

1. are there always the same or more numbers in column 2 than column 1?
2. are all of the numbers in a column unique?
3. are all of the 8-digit numbers always present in both columns?

[TnTinMN]

I am going try to rephrase your question and ask that you confirm whether or not it matches your ultimate goal.

You have two lists of of numbers. One of the lists is longer than other and it contains all the values in the shorter list.

You need to create a side by side comparison of the two lists showing a blank cells in the column representing the shorter list where a number exists in the longer list but not in the shorter list.

Does this sound like what you are after?

[Tarwit]

Reverend Jim,
answers to your questions:
1. in second one could be the same but when is more i need to add missing rows (or cells) below, all 8digits numbers should contain the same rows value even if they are blank in first column because in second appear more shorter numbers
2. yes they are unique
3. yes they are, i need to match the same numbers and the list below them

TnTinMN,
Yes, it sounds like what I need.

[Reverend Jim]

The first example you gave me had the columns

First column           Second column
12345678               12345678     
12578                  36894        
69782                  36548        
12547                  16974        
36894                  69836        
36548                  78954        
16974                  99789        
69836                  12578        
78954                  69782        
99789                  12547        
12347965               12578        
                       69782        
                       12547        
                       12345678     

I don't see how you can match these up. Column two violates the unique constraint as 12345678 appears twice, and 12347965 appears in column one but not column two. If, however, if we use the numbers in the second example, we can align them by

Dim col1() As Integer = {12345678, 12578, 69782, 12547, 36894, 36548, 16974, 69836, 78954, 99789, 12345677, 99789, 69836, 12345678}
Dim col2() As Integer = {12345678, 12578, 69782, 12547, 36894, 36548, 16974, 69836, 78954, 99789, 12578, 69782, 12547, 12345677, 99789, 69836, 78954, 12345678}

Dim i As Integer = 0

For Each num As Integer In col2
    Dim item As New ListViewItem
    If i < col1.Count AndAlso col1(i) = num Then
        item.Text = col1(i)
        item.SubItems.Add(num)
        i += 1
    Else
        item.Text = ""
        item.SubItems.Add(num)
    End If
    ListView1.Items.Add(item)
Next

That shows how to take the numbers and align them using a listview as output. Do you know how to get at the Excel data from vb.net?

[tinstaafl]

It seems to me the 8 digit numbers are just headers not data.

It sounds to me you want add the extra rows to the shorter column to make the rows the same as the longer column.

Perhaps we should see some of your code to get a better understanding.

[TnTinMN]

Sorry, but I begin to use VBA and I want/need to learn more. I will be apreaciated for any clues.

I am going to assume that since you mentioned VBA that you are looking for Excel macro help.

If this is the case, this thread should be probably moved to the VB6 forum as that is the most appropriate one that I can think of.

TnTinMN,
Yes, it sounds like what I need.

Here is a basic macro with minimal error checking. It asks you to select two ranges and it compares the first column in each range to produce a result array. It then asks you for a place to copy the results to in a worksheet.

Private Sub CompareColumns()
    Dim Col1 As Range
    Dim Col2 As Range

    On Error Resume Next

    Set Col1 = Application.InputBox("Select 1st column", , ActiveCell.Address, _
                                        , , , , vbString)
    If Col1 Is Nothing Then Exit Sub

    Set Col2 = Application.InputBox("Select 2nd column", , ActiveCell.Address, _
                                        , , , , vbString)
    If Col2 Is Nothing Then Exit Sub

    On Error GoTo 0 ' cancel resume next

    'Determine the longer list

        Dim shortCol As Range
        Dim LongCol As Range

        If Col1.Rows.Count < Col2.Rows.Count Then
            Set shortCol = Col1
            Set LongCol = Col2
        Else
            Set shortCol = Col2
            Set LongCol = Col1
        End If

    Dim LongColMatched() As Boolean ' Used to indicate matched value used if duplicates exist
    ReDim LongColMatched(0 To LongCol.Rows.Count - 1)

    ' declare a result array
        Dim result() As Variant
        ReDim result(0 To LongCol.Rows.Count - 1, 0 To 1)

    ' Fill the result array with the long column values
        Dim j As Integer
        For j = 1 To LongCol.Rows.Count
            result(j - 1, 1) = LongCol.Cells(j, 1).Value
        Next j
        Dim i As Integer

    ' search for matches
        Dim target As Range
        Dim MatchFound As Boolean

        For i = 1 To shortCol.Rows.Count

            Set target = shortCol.Cells(i, 1)
            MatchFound = False ' used to signal that no match found

            ' Search for an unused matched value
            For j = 0 To LongCol.Rows.Count - 1
                If result(j, 1) = target.Value And (Not LongColMatched(j)) Then
                    result(j, 0) = target.Value
                    LongColMatched(j) = True
                    MatchFound = True
                    Exit For
                End If
            Next j
            If Not MatchFound Then
                If MsgBox("Value: " & CStr(target.Value) & " not found in longer list." & _
                        vbCrLf & "Continue?", vbYesNo) = vbNo Then
                  Exit Sub
                End If
            End If
        Next i

    ' get a replace to put the results
        Dim ResultRange As Range
        On Error Resume Next
        Set ResultRange = Application.InputBox("Select Destination", , ActiveCell.Address, _
                                            , , , , vbString)

        ' copy the results to the destination
        If Not ResultRange Is Nothing Then ResultRange.Resize(LongCol.Rows.Count, 2) = result

End Sub

[Reverend Jim]

I'll move this thread to vb-4-5-6 if the OP requests it.

[Tarwit]

TnTinMN! - It is exacly what I need!!! Thanks a lot.
Sorry, maybe I wasn't clear in my posts, it was hard to describe.
Thanks alot for all your help.

[Tarwit]

Hi again,
after testing I have two extra questions:
1. Is it possible always use - for column 1 - column A from 1st sheet, and for column 2- Coulmn A from second sheet?
2. Is it possible to get below result If I will have some more data like in two sheets below.

Sheet1

Sheet 2

[TnTinMN]

Yes, but the sheet names must be known to hardcode a range.

Also, will there ever be any discontinuities (empty cells) is the column data. This is not a problem, it needs to be accounted for in computing the range to process.