Arrays: compare fields?

Hello,

I have a problem that I just cannot solve myself.
Please, if you have knowledge that can get me on track, post a
comment.

Here it goes..

I have two sheets, with rather big amounts of data.
They consist of imported txt-files, and can vary in row-size (the
columns are fixed and always the same)

After my last import, sheet one consist of roughly 5000 rows, and
sheet two of roughly 2000 rows.

They both consist of 26 columns. The columns consist of various data
types; text, integers, percent values, dates etc.

What I want to do is to compare the tables based on the value in the
leftmost columns in both tables (these are project-numbers in both
tables) and store the matching data on the same rows on a third sheet
(this will then have 52 columns).

First, I did this by looping, but this takes way to much time, so I
want to read both tables into two-dimensional arrays, and either build
the matched rows into a third array and write this third array into
sheet 3 (or alternatively, write the matched rows directly to sheet
3).

I cannot get this to work. It finishes, but the matches are incorrect.

Hope to get some replies; I have a deadline on this tomorrow - UGH!



Best regards
NorTor

NorTor.
Check out Chip Pearson's page. Should give you a start in Excel or VBA.
http://www.cpearson.com/excel/duplicat.htm

NickHK

"NorTor" <toa@nospam.movesubnett.no> wrote in message
news:beq811pidqk2da89fn06gtl6lg6psj48mc@4ax.com...
> Hello,
>
> I have a problem that I just cannot solve myself.
> Please, if you have knowledge that can get me on track, post a
> comment.
>
> Here it goes..
>
> I have two sheets, with rather big amounts of data.
> They consist of imported txt-files, and can vary in row-size (the
> columns are fixed and always the same)
>
> After my last import, sheet one consist of roughly 5000 rows, and
> sheet two of roughly 2000 rows.
>
> They both consist of 26 columns. The columns consist of various data
> types; text, integers, percent values, dates etc.
>
> What I want to do is to compare the tables based on the value in the
> leftmost columns in both tables (these are project-numbers in both
> tables) and store the matching data on the same rows on a third sheet
> (this will then have 52 columns).
>
> First, I did this by looping, but this takes way to much time, so I
> want to read both tables into two-dimensional arrays, and either build
> the matched rows into a third array and write this third array into
> sheet 3 (or alternatively, write the matched rows directly to sheet
> 3).
>
> I cannot get this to work. It finishes, but the matches are incorrect.
>
> Hope to get some replies; I have a deadline on this tomorrow - UGH!
>
>
>
> Best regards
> NorTor
>

Hi, here is the a little more info (See description below)

Here is the code I have so far:

*********************************************

cnt1 = sheets("sheet1").range("A65536").end(xlup).row
cnt2 = sheets("sheet2").range("A65536").end(xlup).row

Sheets("Sheet3").Select
Cells(3, 1).Activate
Dim myArray() As Variant
Dim dSett1() As Variant
Dim dSett2() As Variant

cnt1 = cnt1 + 1

ReDim dSett1(2 To cnt1, 1 to 26)
ReDim dSett2(2 To cnt2, 1 To 26)
ReDim myArray(3 To cnt1, 1 To 52)

For i = 3 To cnt1
dSett1(i) = Sheets("Sheet1").Cells(i, 1).Value
Next i

For i = 2 To cnt2
For j = 1 To 26
dSett2(i, j) = Sheets("Sheet2").Cells(i, j).Value
Next j
Next i

For i = 2 To cnt1
For k = 2 To cnt2
If dSett1(i,1) = dSett2(k, 1) Then
For j = 1 To 52
myArray(i, j) = ?
Next j
End If
Next k
Next i

..
..
..
Write myArray to Sheet3
..
..
..

*********************************************


Please help...( I want to do this in VBA)




On Thu, 17 Feb 2005 11:08:11 +0100, NorTor <toa@nospam.movesubnett.no>
wrote:

>Hello,
>
>I have a problem that I just cannot solve myself.
>Please, if you have knowledge that can get me on track, post a
>comment.
>
>Here it goes..
>
>I have two sheets, with rather big amounts of data.
>They consist of imported txt-files, and can vary in row-size (the
>columns are fixed and always the same)
>
>After my last import, sheet one consist of roughly 5000 rows, and
>sheet two of roughly 2000 rows.
>
>They both consist of 26 columns. The columns consist of various data
>types; text, integers, percent values, dates etc.
>
>What I want to do is to compare the tables based on the value in the
>leftmost columns in both tables (these are project-numbers in both
>tables) and store the matching data on the same rows on a third sheet
>(this will then have 52 columns).
>
>First, I did this by looping, but this takes way to much time, so I
>want to read both tables into two-dimensional arrays, and either build
>the matched rows into a third array and write this third array into
>sheet 3 (or alternatively, write the matched rows directly to sheet
>3).
>
>I cannot get this to work. It finishes, but the matches are incorrect.
>
>Hope to get some replies; I have a deadline on this tomorrow - UGH!
>
>
>
>Best regards
>NorTor

NorTor,
Just use single dimension array for the names to match.
Once a match is found, get the values from the 2 WS, as you know the
offsets, i & k.

NickHK

"NorTor" <toa@nospam.movesubnett.no> wrote in message
news:89s81153qc2lplaasmdfhb2c1dnpeib33k@4ax.com...
> Hi, here is the a little more info (See description below)
>
> Here is the code I have so far:
>
> *********************************************
>
> cnt1 = sheets("sheet1").range("A65536").end(xlup).row
> cnt2 = sheets("sheet2").range("A65536").end(xlup).row
>
> Sheets("Sheet3").Select
> Cells(3, 1).Activate
> Dim myArray() As Variant
> Dim dSett1() As Variant
> Dim dSett2() As Variant
>
> cnt1 = cnt1 + 1
>
> ReDim dSett1(2 To cnt1, 1 to 26)
> ReDim dSett2(2 To cnt2, 1 To 26)
> ReDim myArray(3 To cnt1, 1 To 52)
>
> For i = 3 To cnt1
> dSett1(i) = Sheets("Sheet1").Cells(i, 1).Value
> Next i
>
> For i = 2 To cnt2
> For j = 1 To 26
> dSett2(i, j) = Sheets("Sheet2").Cells(i, j).Value
> Next j
> Next i
>
> For i = 2 To cnt1
> For k = 2 To cnt2
> If dSett1(i,1) = dSett2(k, 1) Then
> For j = 1 To 52
> myArray(i, j) = ?
> Next j
> End If
> Next k
> Next i
>
> .
> .
> .
> Write myArray to Sheet3
> .
> .
> .
>
> *********************************************
>
>
> Please help...( I want to do this in VBA)
>
>
>
>
> On Thu, 17 Feb 2005 11:08:11 +0100, NorTor <toa@nospam.movesubnett.no>
> wrote:
>
> >Hello,
> >
> >I have a problem that I just cannot solve myself.
> >Please, if you have knowledge that can get me on track, post a
> >comment.
> >
> >Here it goes..
> >
> >I have two sheets, with rather big amounts of data.
> >They consist of imported txt-files, and can vary in row-size (the
> >columns are fixed and always the same)
> >
> >After my last import, sheet one consist of roughly 5000 rows, and
> >sheet two of roughly 2000 rows.
> >
> >They both consist of 26 columns. The columns consist of various data
> >types; text, integers, percent values, dates etc.
> >
> >What I want to do is to compare the tables based on the value in the
> >leftmost columns in both tables (these are project-numbers in both
> >tables) and store the matching data on the same rows on a third sheet
> >(this will then have 52 columns).
> >
> >First, I did this by looping, but this takes way to much time, so I
> >want to read both tables into two-dimensional arrays, and either build
> >the matched rows into a third array and write this third array into
> >sheet 3 (or alternatively, write the matched rows directly to sheet
> >3).
> >
> >I cannot get this to work. It finishes, but the matches are incorrect.
> >
> >Hope to get some replies; I have a deadline on this tomorrow - UGH!
> >
> >
> >
> >Best regards
> >NorTor
>

Thank you Nick, code like this?

ReDim dSett1(3 To cnt1)

..
..
..

If dSett1(i) = dSett2(k, 1) Then
myArray(i,j) = ...
..
..
..


I cannot get them to match... even though there are several hundred
matching rows... does the formatting of the two columns to be compared
matter? Is there a way to make them always equally value-formats
before comparing?





On Thu, 17 Feb 2005 18:57:28 +0800, "NickHK" <TungCheWah@Invalid.com>
wrote:

>NorTor,
>Just use single dimension array for the names to match.
>Once a match is found, get the values from the 2 WS, as you know the
>offsets, i & k.
>
>NickHK
>
>"NorTor" <toa@nospam.movesubnett.no> wrote in message
>news:89s81153qc2lplaasmdfhb2c1dnpeib33k@4ax.com...
>> Hi, here is the a little more info (See description below)
>>
>> Here is the code I have so far:
>>
>> *********************************************
>>
>> cnt1 = sheets("sheet1").range("A65536").end(xlup).row
>> cnt2 = sheets("sheet2").range("A65536").end(xlup).row
>>
>> Sheets("Sheet3").Select
>> Cells(3, 1).Activate
>> Dim myArray() As Variant
>> Dim dSett1() As Variant
>> Dim dSett2() As Variant
>>
>> cnt1 = cnt1 + 1
>>
>> ReDim dSett1(2 To cnt1, 1 to 26)
>> ReDim dSett2(2 To cnt2, 1 To 26)
>> ReDim myArray(3 To cnt1, 1 To 52)
>>
>> For i = 3 To cnt1
>> dSett1(i) = Sheets("Sheet1").Cells(i, 1).Value
>> Next i
>>
>> For i = 2 To cnt2
>> For j = 1 To 26
>> dSett2(i, j) = Sheets("Sheet2").Cells(i, j).Value
>> Next j
>> Next i
>>
>> For i = 2 To cnt1
>> For k = 2 To cnt2
>> If dSett1(i,1) = dSett2(k, 1) Then
>> For j = 1 To 52
>> myArray(i, j) = ?
>> Next j
>> End If
>> Next k
>> Next i
>>
>> .
>> .
>> .
>> Write myArray to Sheet3
>> .
>> .
>> .
>>
>> *********************************************
>>
>>
>> Please help...( I want to do this in VBA)
>>
>>
>>
>>
>> On Thu, 17 Feb 2005 11:08:11 +0100, NorTor <toa@nospam.movesubnett.no>
>> wrote:
>>
>> >Hello,
>> >
>> >I have a problem that I just cannot solve myself.
>> >Please, if you have knowledge that can get me on track, post a
>> >comment.
>> >
>> >Here it goes..
>> >
>> >I have two sheets, with rather big amounts of data.
>> >They consist of imported txt-files, and can vary in row-size (the
>> >columns are fixed and always the same)
>> >
>> >After my last import, sheet one consist of roughly 5000 rows, and
>> >sheet two of roughly 2000 rows.
>> >
>> >They both consist of 26 columns. The columns consist of various data
>> >types; text, integers, percent values, dates etc.
>> >
>> >What I want to do is to compare the tables based on the value in the
>> >leftmost columns in both tables (these are project-numbers in both
>> >tables) and store the matching data on the same rows on a third sheet
>> >(this will then have 52 columns).
>> >
>> >First, I did this by looping, but this takes way to much time, so I
>> >want to read both tables into two-dimensional arrays, and either build
>> >the matched rows into a third array and write this third array into
>> >sheet 3 (or alternatively, write the matched rows directly to sheet
>> >3).
>> >
>> >I cannot get this to work. It finishes, but the matches are incorrect.
>> >
>> >Hope to get some replies; I have a deadline on this tomorrow - UGH!
>> >
>> >
>> >
>> >Best regards
>> >NorTor
>>
>

NorTor,
Something like:

ReDim dSett1(2 To cnt1)
ReDim dSett2(2 To cnt2)

For i = 3 To cnt1
dSett1(i) = Sheets("Sheet1").Cells(i, 1).Value
Next i
For i = 2 To cnt2
dSett2(i) = Sheets("Sheet2").Cells(i,1).Value
Next i

For i = 3 To cnt1
For k = 2 To cnt2
If dSett1(i) = dSett2(k) Then
MatchCount=MatchCount+1
'Match found, so copy dSett1 and dSett2 data to new destination
With Sheets("Destination").Range("A" & MatchCount)
.Value=dSett1(i)
.Range("B:AA2").Value=Sheets("Sheet1").Range("B" & i &
":AA" & i).Value
.Range("AB2:BA2").Value=Sheets("Sheet2").Range("B" & k &
":AA" & k).Value
End With
End If
Next k
Next i

NickHk

"NorTor" <toa@nospam.movesubnett.no> wrote in message
news:r4u811doilvoftu027dgnip2ugddc0jn82@4ax.com...
> Thank you Nick, code like this?
>
> ReDim dSett1(3 To cnt1)
>
> .
> .
> .
>
> If dSett1(i) = dSett2(k, 1) Then
> myArray(i,j) = ...
> .
> .
> .
>
>
> I cannot get them to match... even though there are several hundred
> matching rows... does the formatting of the two columns to be compared
> matter? Is there a way to make them always equally value-formats
> before comparing?
>
>
>
>
>
> On Thu, 17 Feb 2005 18:57:28 +0800, "NickHK" <TungCheWah@Invalid.com>
> wrote:
>
> >NorTor,
> >Just use single dimension array for the names to match.
> >Once a match is found, get the values from the 2 WS, as you know the
> >offsets, i & k.
> >
> >NickHK
> >
> >"NorTor" <toa@nospam.movesubnett.no> wrote in message
> >news:89s81153qc2lplaasmdfhb2c1dnpeib33k@4ax.com...
> >> Hi, here is the a little more info (See description below)
> >>
> >> Here is the code I have so far:
> >>
> >> *********************************************
> >>
> >> cnt1 = sheets("sheet1").range("A65536").end(xlup).row
> >> cnt2 = sheets("sheet2").range("A65536").end(xlup).row
> >>
> >> Sheets("Sheet3").Select
> >> Cells(3, 1).Activate
> >> Dim myArray() As Variant
> >> Dim dSett1() As Variant
> >> Dim dSett2() As Variant
> >>
> >> cnt1 = cnt1 + 1
> >>
> >> ReDim dSett1(2 To cnt1, 1 to 26)
> >> ReDim dSett2(2 To cnt2, 1 To 26)
> >> ReDim myArray(3 To cnt1, 1 To 52)
> >>
> >> For i = 3 To cnt1
> >> dSett1(i) = Sheets("Sheet1").Cells(i, 1).Value
> >> Next i
> >>
> >> For i = 2 To cnt2
> >> For j = 1 To 26
> >> dSett2(i, j) = Sheets("Sheet2").Cells(i, j).Value
> >> Next j
> >> Next i
> >>
> >> For i = 2 To cnt1
> >> For k = 2 To cnt2
> >> If dSett1(i,1) = dSett2(k, 1) Then
> >> For j = 1 To 52
> >> myArray(i, j) = ?
> >> Next j
> >> End If
> >> Next k
> >> Next i
> >>
> >> .
> >> .
> >> .
> >> Write myArray to Sheet3
> >> .
> >> .
> >> .
> >>
> >> *********************************************
> >>
> >>
> >> Please help...( I want to do this in VBA)
> >>
> >>
> >>
> >>
> >> On Thu, 17 Feb 2005 11:08:11 +0100, NorTor <toa@nospam.movesubnett.no>
> >> wrote:
> >>
> >> >Hello,
> >> >
> >> >I have a problem that I just cannot solve myself.
> >> >Please, if you have knowledge that can get me on track, post a
> >> >comment.
> >> >
> >> >Here it goes..
> >> >
> >> >I have two sheets, with rather big amounts of data.
> >> >They consist of imported txt-files, and can vary in row-size (the
> >> >columns are fixed and always the same)
> >> >
> >> >After my last import, sheet one consist of roughly 5000 rows, and
> >> >sheet two of roughly 2000 rows.
> >> >
> >> >They both consist of 26 columns. The columns consist of various data
> >> >types; text, integers, percent values, dates etc.
> >> >
> >> >What I want to do is to compare the tables based on the value in the
> >> >leftmost columns in both tables (these are project-numbers in both
> >> >tables) and store the matching data on the same rows on a third sheet
> >> >(this will then have 52 columns).
> >> >
> >> >First, I did this by looping, but this takes way to much time, so I
> >> >want to read both tables into two-dimensional arrays, and either build
> >> >the matched rows into a third array and write this third array into
> >> >sheet 3 (or alternatively, write the matched rows directly to sheet
> >> >3).
> >> >
> >> >I cannot get this to work. It finishes, but the matches are incorrect.
> >> >
> >> >Hope to get some replies; I have a deadline on this tomorrow - UGH!
> >> >
> >> >
> >> >
> >> >Best regards
> >> >NorTor
> >>
> >
>

If you can live with storing the arrays in variants, this code is quicker to
execute:

Dim dSett1 As Variant
dSett1 = Worksheets("Sheet1").Range("A1:A3").Value

The resulting array is 2-dim so you would address the elements as dSett1(1,i)
instead of dSett1(i)


On Fri, 18 Feb 2005 10:17:40 +0800, "NickHK" <TungCheWah@Invalid.com> wrote:

>NorTor,
>Something like:
>
> ReDim dSett1(2 To cnt1)
> ReDim dSett2(2 To cnt2)
>
> For i = 3 To cnt1
> dSett1(i) = Sheets("Sheet1").Cells(i, 1).Value
> Next i
> For i = 2 To cnt2
> dSett2(i) = Sheets("Sheet2").Cells(i,1).Value
> Next i
>
> For i = 3 To cnt1
> For k = 2 To cnt2
> If dSett1(i) = dSett2(k) Then
> MatchCount=MatchCount+1
> 'Match found, so copy dSett1 and dSett2 data to new destination
> With Sheets("Destination").Range("A" & MatchCount)
> .Value=dSett1(i)
> .Range("B:AA2").Value=Sheets("Sheet1").Range("B" & i &
>":AA" & i).Value
> .Range("AB2:BA2").Value=Sheets("Sheet2").Range("B" & k &
>":AA" & k).Value
> End With
> End If
> Next k
> Next i
>
>NickHk
>
>"NorTor" <toa@nospam.movesubnett.no> wrote in message
>news:r4u811doilvoftu027dgnip2ugddc0jn82@4ax.com...
>> Thank you Nick, code like this?
>>
>> ReDim dSett1(3 To cnt1)
>>
>> .
>> .
>> .
>>
>> If dSett1(i) = dSett2(k, 1) Then
>> myArray(i,j) = ...
>> .
>> .
>> .
>>
>>
>> I cannot get them to match... even though there are several hundred
>> matching rows... does the formatting of the two columns to be compared
>> matter? Is there a way to make them always equally value-formats
>> before comparing?
>>
>>
>>
>>
>>
>> On Thu, 17 Feb 2005 18:57:28 +0800, "NickHK" <TungCheWah@Invalid.com>
>> wrote:
>>
>> >NorTor,
>> >Just use single dimension array for the names to match.
>> >Once a match is found, get the values from the 2 WS, as you know the
>> >offsets, i & k.
>> >
>> >NickHK
>> >
>> >"NorTor" <toa@nospam.movesubnett.no> wrote in message
>> >news:89s81153qc2lplaasmdfhb2c1dnpeib33k@4ax.com...
>> >> Hi, here is the a little more info (See description below)
>> >>
>> >> Here is the code I have so far:
>> >>
>> >> *********************************************
>> >>
>> >> cnt1 = sheets("sheet1").range("A65536").end(xlup).row
>> >> cnt2 = sheets("sheet2").range("A65536").end(xlup).row
>> >>
>> >> Sheets("Sheet3").Select
>> >> Cells(3, 1).Activate
>> >> Dim myArray() As Variant
>> >> Dim dSett1() As Variant
>> >> Dim dSett2() As Variant
>> >>
>> >> cnt1 = cnt1 + 1
>> >>
>> >> ReDim dSett1(2 To cnt1, 1 to 26)
>> >> ReDim dSett2(2 To cnt2, 1 To 26)
>> >> ReDim myArray(3 To cnt1, 1 To 52)
>> >>
>> >> For i = 3 To cnt1
>> >> dSett1(i) = Sheets("Sheet1").Cells(i, 1).Value
>> >> Next i
>> >>
>> >> For i = 2 To cnt2
>> >> For j = 1 To 26
>> >> dSett2(i, j) = Sheets("Sheet2").Cells(i, j).Value
>> >> Next j
>> >> Next i
>> >>
>> >> For i = 2 To cnt1
>> >> For k = 2 To cnt2
>> >> If dSett1(i,1) = dSett2(k, 1) Then
>> >> For j = 1 To 52
>> >> myArray(i, j) = ?
>> >> Next j
>> >> End If
>> >> Next k
>> >> Next i
>> >>
>> >> .
>> >> .
>> >> .
>> >> Write myArray to Sheet3
>> >> .
>> >> .
>> >> .
>> >>
>> >> *********************************************
>> >>
>> >>
>> >> Please help...( I want to do this in VBA)
>> >>
>> >>
>> >>
>> >>
>> >> On Thu, 17 Feb 2005 11:08:11 +0100, NorTor <toa@nospam.movesubnett.no>
>> >> wrote:
>> >>
>> >> >Hello,
>> >> >
>> >> >I have a problem that I just cannot solve myself.
>> >> >Please, if you have knowledge that can get me on track, post a
>> >> >comment.
>> >> >
>> >> >Here it goes..
>> >> >
>> >> >I have two sheets, with rather big amounts of data.
>> >> >They consist of imported txt-files, and can vary in row-size (the
>> >> >columns are fixed and always the same)
>> >> >
>> >> >After my last import, sheet one consist of roughly 5000 rows, and
>> >> >sheet two of roughly 2000 rows.
>> >> >
>> >> >They both consist of 26 columns. The columns consist of various data
>> >> >types; text, integers, percent values, dates etc.
>> >> >
>> >> >What I want to do is to compare the tables based on the value in the
>> >> >leftmost columns in both tables (these are project-numbers in both
>> >> >tables) and store the matching data on the same rows on a third sheet
>> >> >(this will then have 52 columns).
>> >> >
>> >> >First, I did this by looping, but this takes way to much time, so I
>> >> >want to read both tables into two-dimensional arrays, and either build
>> >> >the matched rows into a third array and write this third array into
>> >> >sheet 3 (or alternatively, write the matched rows directly to sheet
>> >> >3).
>> >> >
>> >> >I cannot get this to work. It finishes, but the matches are incorrect.
>> >> >
>> >> >Hope to get some replies; I have a deadline on this tomorrow - UGH!
>> >> >
>> >> >
>> >> >
>> >> >Best regards
>> >> >NorTor
>> >>
>> >
>>
>