Comparing every row of sheet 2 with all rows of sheet 1

[Hardyman]

I have trouble trying to write a short program for this tedious work.</SPAN>
The rows contain the number indicated by Ys. For example In Sheet 1 Row 01 has number 02,03 and 06.</SPAN>
What I wanted to do is to compare the rows of numbers in sheet 2 with every 5 rows of numbers in sheet 1. Actually sheet 2 has as many as 200 over rows but I have only shown 9 rows here. If any one number (not necessary all 3 numbers) of sheet 2 matches at least one number in every row of sheet 1, that row in sheet 2 is then inserted in sheet 3.</SPAN>
Row 05 of sheet 2 has at least one number match a number of every 5 rows of sheet 1. So are Row 06 and Row 09 of sheet 2. </SPAN>
Please help.</SPAN>
Sheet1

*	A	B	C	D	E	F	G	H	I	J	K
1	*	01	02	03	04	05	06	07	08	09	10
2	Row01	*	Y	Y	*	*	Y	*	*	*	*
3	Row02	*	*	Y	Y	*	*	*	Y	*	*
4	Row03	*	Y	*	*	*	*	*	*	Y	Y
5	Row04	*	*	*	*	Y	*	Y	*	*	Y
6	Row05	Y	*	*	*	*	Y	*	Y	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 71px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Sheet2

*	A	B	C	D	E	F	G	H	I	J	K
1	*	01	02	03	04	05	06	07	08	09	10
2	Row01	*	Y	*	*	Y	*	*	Y	*	*
3	Row02	Y	*	*	Y	*	*	Y	*	*	*
4	Row03	Y	*	*	*	*	Y	*	*	Y	*
5	Row04	*	Y	Y	*	*	*	Y	*	*	*
6	Row05	*	*	*	Y	*	Y	*	*	*	Y
7	Row06	*	*	Y	*	*	*	*	Y	*	Y
8	Row07	*	Y	*	*	Y	*	*	*	Y	*
9	Row08	*	*	Y	*	*	*	Y	Y	*	*
10	Row09	Y	*	Y	*	*	*	*	*	*	Y

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 49px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Sheet3

*	A	B	C	D	E	F	G	H	I	J	K
1	*	01	02	03	04	05	06	07	08	09	10
2	Row05	*	*	*	Y	*	Y	*	*	*	Y
3	Row06	*	*	Y	*	*	*	*	Y	*	Y
4	Row09	Y	*	Y	*	*	*	*	*	*	Y

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 52px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"><COL style="WIDTH: 26px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[hurgadion]

Hi,
try using the macro (in Sheet2) for Your Data (Your Example):

Sub FindRows()
Dim x&, i&, j&, k&

ReDim tbl(1 To 5)

tbl(1) = "2,3,6"
tbl(2) = "3,4,8"
tbl(3) = "2,9,10"
tbl(4) = "5,7,10"
tbl(5) = "1,6,8"

x = 1
For i = 2 To 10
ReDim tbls(1 To 5)
   
   For j = 1 To 5
      tbla = Split(tbl(j), ",")
      For k = 0 To 2
        If Cells(i, CSng(tbla(k)) + 1).Value = "Y" Then
          tbls(j) = 1
          Exit For
        End If
      Next k
   Next j
   
If Application.Sum(tbls) = 5 Then
  x = x + 1
  WorkSheets("Sheet3").Range("B" & i & ":K" & i).Copy Range("M" & x)
End If
  
Next i
End Sub

Best regards.

 

[Hardyman]

hurgadion, thank you for replying. Initially I got variable not defined tbla. I corrected it by adding Dim tbla and ran the code in sheet 2. Nothing happened.

 

[hurgadion]

OK,
I understand rather, where is the mistake, try change the line

WorkSheets("Sheet3").Range("B" & i & ":K" & i).Copy Range("M" & x)

on the line

Range("B" & i & ":K" & i).Copy WorkSheets("Sheet3").Range("A" & x)

Best regards.

 

[Hardyman]

hurgadion, perfect. It worked. Thanks for your immense help.