Join and Index Data

[pwill]

Hi can anyone help with the following code?

On Sheet2 I have the following
MyLetters in "E4:K4"
MyNumbers in "D5:D30"<strike></strike>
MyData in "E5:K30"
MyResults in "AN20:AT26"<strike></strike>

The code works fine but I want to change the results so that instead of Indexing every row with (MyLetters) within (MyData) into the Results, I want to index the Rows that only have 2 or more of (MyLetters) into the Results. In the example below (ResultsA) shows the results using every Row and (MyLetters) and ResultsB shows what the results would be if only indexing Rows with 2 or more MyLetters.(To get ResultsB I cleared all the Letters on rows(5,8,11,13,14,16,22,24,25,28 and ran the code again)

Could I add something like 'COUNTIF(Row, MyData) >= 2' to the code somewhere to achieve this?

[FONT=Verdana]
Sub IndexMyData()
[/FONT]
[FONT=Verdana]    Dim MyRow(26), InSht As Worksheet, OutSht As Worksheet
    Dim c As Integer
    Dim i As Long
    Dim j As Long
    Dim r As Long
    Dim MyResults
    Dim CustOrd
    Dim MyData
    Dim MyNums
    Dim MyLtrs
    
        Application.EnableEvents = False
    
        Set InSht = Sheet2
        Set OutSht = Sheet2
        Set MyResults = OutSht.Range("AN20:AT26")
    
        MyData = InSht.Range("E5:K30")
        MyNums = InSht.Range("D5:D30")
        MyLtrs = InSht.Range("E4:K4")
    
            For i = 1 To 26
                MyRow(i) = " " & Join(WorksheetFunction.Index(MyData, i, 0)) & " "
                CustOrd = CustOrd & MyNums(i, 1) & ","
            Next i
    
            MyResults.ClearContents
            For i = 1 To 7
            c = 0
        
            For j = 1 To 26
                If InStr(MyRow(j), " " & MyLtrs(1, i) & " ") > 0 Then
                    c = c + 1
                        MyResults.Cells(c, i) = MyNums(j, 1)
                        If c = 7 Then Exit For
                    End If
                Next j
            Next i
    
            With OutSht.Sort
                .SortFields.Clear
                    For i = 1 To 7
                        .SortFields.Add Key:=MyResults.Cells(i, 1), CustomOrder:=CVar(CustOrd)
                    Next i
                .SetRange MyResults
                .Orientation = xlLeftToRight
                .Apply
            End With
    
            Application.EnableEvents = True
    
End Sub[/FONT]

	A	B	C	D	E	F	G	H	I
1
2
3
4	MyLtrs	A	B	C	D	E	F	G						Results A
5	1	7	12	17	21	D	40	43			AN	AO	AP	AQ	AR	AS	AT	AU
6	2	5	10	A	19	C	38	41		20	1	2	2	4	9	11	13
7	3	8	13	18	22	30	41	44		21	17	7	13	11	12	17	19
8	4	4	9	14	18	B	37	40		22	20	10	21	19	18
9	5	9	14	19	23	31	42	45		23	25	17	24	26	25
10	6	3	8	13	17	25	36	39		24	26	26
11	7	10	A	20	24	32	43	46		25
12	8	2	7	12	16	24	35	38		26
13	9	11	16	21	25	E	44	47
14	10	1	6	11	A	23	34	37						Results B
15	11	12	17	22	B	34	45	F			AN	AO	AP	AQ	AR	AS	AT	AU
16	12	0	5	10	14	22	E	36		20	2	2	11	11	13	17	25
17	13	13	18	23	C	35	46	G		21	13	17	17	19	19	25
18	14	-1	4	9	13	21	32	35		22		26		26		26
19	15	14	19	24	28	36	47	50		23
20	16	-2	3	8	12	20	31	34		24
21	17	A	20	25	D	37	F	51		25
22	18	-3	2	7	11	19	30	E		26
23	19	16	21	B	30	38	G	52
24	20	-4	1	6	10	18	D	32
25	21	17	22	C	31	39	50	53
26	22	-5	0	5	9	17	28	31
27	23	18	23	28	32	40	51	54
28	24	-6	-1	4	8	16	C	30
29	25	19	24	D	E	41	52	55
30	26	-7	-2	3	7	A	B	D

<tbody>
</tbody>

_{^{<strike>
</strike>}}
Any help would be appreciated

Regards

pwill

 

[pwill]

Sorry, got the Sheet Columns wrong here

A

B

C

D

E

F

G

H

I

1

2

3

4

MyLtrs

A

B

C

D

E

F

G

<tbody>
</tbody>

They should be and "E4:K4" are MyLetters

	D	E	F	G	H	I	J	K	L
1
2
3
4	MyLtrs	A	B	C	D	E	F	G

<tbody>
</tbody>