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.

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

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

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?

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

Yes ;)!

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

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

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?

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.

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?

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?

Reverend Jim,
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.

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)
i += 1
Else
item.Text = ""
End If
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?

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.

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
``````
commented: excellent! well done! +0
commented: Likewise +12

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

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.

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.