i have two sheets with the same data, but each sheet has additional
data as well....i need to match up the 2 sheets using the matching
data.

so its like this....

Sheet 1

A B C D E
1 1 4 6 3 2
2 4 5 3 6 7

Sheet 2

A B C D E
1 4 5 3 6 7
2 1 4 6 3 2

so i need to take the range in sheet 2 A1:E1
and find the row that matchs it in sheet 1......
in the example, the result would be row 2......

how would i go about doing that?

The simplest solution would certainly be to insert a third sheet where
you insert the following formula in A1:

=Sheet1!A1=Sheet2!A1

Replace Sheet1 & Sheet2 with your sheet names. Then copy and paste this
formula into the remaining cells. If you have lots of data, you may
want to set an AutoFilter on the top row to filter for TRUE values.

If this solution does not meet your needs, let me know. There are other
ways to solve this.

Regards,
Steve

Nigel schrieb:

i have two sheets with the same data, but each sheet has additional
data as well....i need to match up the 2 sheets using the matching
data.

so its like this....

Sheet 1

A B C D E
1 1 4 6 3 2
2 4 5 3 6 7

Sheet 2

A B C D E
1 4 5 3 6 7
2 1 4 6 3 2

so i need to take the range in sheet 2 A1:E1
and find the row that matchs it in sheet 1......
in the example, the result would be row 2......

how would i go about doing that?

yeah i think i would need a little bit more complex of a solution...its
going to a be a bit of code amongst a whole script.....
i need to pull data off another sheet for each matching range....

On Nov 19, 10:17 pm, wrote:
The simplest solution would certainly be to insert a third sheet where
you insert the following formula in A1:

=Sheet1!A1=Sheet2!A1

Replace Sheet1 & Sheet2 with your sheet names. Then copy and paste this
formula into the remaining cells. If you have lots of data, you may
want to set an AutoFilter on the top row to filter for TRUE values.

If this solution does not meet your needs, let me know. There are other
ways to solve this.

Regards,
Steve

Nigel schrieb:

i have two sheets with the same data, but each sheet has additional
data as well....i need to match up the 2 sheets using the matching
data.

so its like this....

Sheet 1

A B C D E
1 1 4 6 3 2
2 4 5 3 6 7

Sheet 2

A B C D E
1 4 5 3 6 7
2 1 4 6 3 2

so i need to take the range in sheet 2 A1:E1
and find the row that matchs it in sheet 1......
in the example, the result would be row 2......

how would i go about doing that?

Hey Nigel,

Sorry about not getting back to you earlier. The macro below might be
of help to you. It cats each row in two ranges, copies them into arrays
arrCat and arrCatComp, and finally compares them to each other. The
result is stored in colMatches as a collection of arrays where the
variable i is the row of Sheet1 that matches row j in Sheet2.

Option Explicit

Public Sub match()
Dim wb As Excel.Workbook, ws As Excel.Worksheet, wsComp As
Excel.Worksheet, _
rng As Excel.Range, rngComp As Excel.Range, cl As Excel.Range,
valRange As String
Dim i As Integer, j As Integer
Dim colMatches As VBA.Collection, arrCat() As String, arrCatComp() As
String

Set colMatches = New VBA.Collection
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set wsComp = wb.Sheets("Sheet2")
valRange = "A1:E2" ' This assumes that you know the range
Set rng = ws.Range(valRange)
Set rngComp = wsComp.Range(valRange)

arrCat = doCat(rng)
arrCatComp = doCat(rngComp)

For i = 0 To UBound(arrCatComp)
For j = 0 To UBound(arrCat)
If (arrCat(i) = arrCatComp(j)) Then colMatches.Add Array(i + 1, j +
1)
Next j
Next i

''do something with each match
'For i = 1 To colMatches.Count
' Debug.Print "i: " & colMatches.Item(i)(0); " / j: " &
colMatches.Item(i)(1)
'Next i

Set wb = Nothing
Set ws = Nothing
Set wsComp = Nothing
Set rng = Nothing
Set rngComp = Nothing
End Sub

Private Function doCat(ByVal rng As Excel.Range) As String()
Dim row As Integer, cnt As Integer, arr() As String

cnt = rng.Rows.Count
ReDim arr(cnt - 1)
For row = 0 To cnt - 1
'Debug.Print "row: " & row + 1
arr(row) = cat(rng.Rows(row + 1))
Next row

doCat = arr
End Function

Private Function cat(ByVal rng As Excel.Range) As String
Dim catStr As String, col As Integer

col = 1
catStr = rng.Cells(1, col)
For col = 2 To rng.Columns.Count
catStr = catStr & rng.Cells(rng.row, col)
Next col

cat = catStr
End Function

Regards,
Steve


Nigel schrieb:

yeah i think i would need a little bit more complex of a solution...its
going to a be a bit of code amongst a whole script.....
i need to pull data off another sheet for each matching range....

On Nov 19, 10:17 pm, wrote:
The simplest solution would certainly be to insert a third sheet where
you insert the following formula in A1:

=Sheet1!A1=Sheet2!A1

Replace Sheet1 & Sheet2 with your sheet names. Then copy and paste this
formula into the remaining cells. If you have lots of data, you may
want to set an AutoFilter on the top row to filter for TRUE values.

If this solution does not meet your needs, let me know. There are other
ways to solve this.

Regards,
Steve

Nigel schrieb:

i have two sheets with the same data, but each sheet has additional
data as well....i need to match up the 2 sheets using the matching
data.

so its like this....

Sheet 1

A B C D E
1 1 4 6 3 2
2 4 5 3 6 7

Sheet 2

A B C D E
1 4 5 3 6 7
2 1 4 6 3 2

so i need to take the range in sheet 2 A1:E1
and find the row that matchs it in sheet 1......
in the example, the result would be row 2......

how would i go about doing that?