football form table from results

[Peterfc2]

http://www.blueswayedviews.co.uk/peterfc2/capture1.jpg

Look at the link please.

What I want to happen is it to fill the data in the yellow area.
i.e. Tom has played 3 game giving W W L. (I've typed this data in to show I want.)
Anyone know how this can be done please. Tried index match but failed to sort it out.

 

[Momentman]

You know XFD is the last column in excel 2007, am sure they didn't have that many columns in excel 2000 (Maybe 256), check what the last column is by scrolling to the extreme right and replacing the XFD in the code with the one you found

 

[Peterfc2]

You know XFD is the last column in excel 2007, am sure they didn't have that many columns in excel 2000 (Maybe 256), check what the last column is by scrolling to the extreme right and replacing the XFD in the code with the one you found

That's sorted it! IV is last column in Excel2000. Thank you very much Momentman!

 

[Momentman]

So does the code run fully now and gives you the expected result?

 

[Peterfc2]

So does the code run fully now and gives you the expected result?

Yes! Thank You!

 

[Peterfc2]

Hi

Sub fillintheresults()
    Range("L2:XFD50").ClearContents
    
    lastrow = Range("B1").End(xlDown).Row
    lastrow2 = Range("K2").End(xlDown).Row
    
    For i = 2 To lastrow2
        For j = 2 To lastrow
            lastcol = Range("xfd" & i).End(xlToLeft).Column
            If Cells(j, 2).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 8).Value
            End If
            If Cells(j, 6).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 9).Value
            End If
        Next j
    Next i
End Sub

Thought of an update to the above. Can the original be altered to make it do home form only and away form only ( Two different codes.)

Any one help please?

 

[Momentman]

Yes, we should be able to do that, how do you want the output to appear?

 

[Peterfc2]

Similar as before will do nicely. (Nov 8th, 2013, 06:11 PM posting)

 

[Momentman]

Something like dis. This is the output Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Home				Away		HimeRes	AwayRes
2	tom	2		0	sum		W	L		tom	W	W	W
3	pat	2		2	sum		D	D		pat	D
4	tom	3		2	pat		W	L		sum
5	pot	4		0	tom		W	L		pot	W	D
6	tom	3		2	pat		W	L
7	pot	1		1	sum		D	D
8										tom	L
9										Pat	L	L
10										sum	L	D	D
11										pot

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

This is the macro I used

Sub fillintheresults()
    Range("L2:XFD50").ClearContents
    
    lastrow = Range("B1").End(xlDown).Row
    lastrow2 = Range("K2").End(xlDown).Row
    
    For I = 2 To lastrow2
        For j = 2 To lastrow
            lastcol = Range("xfd" & I).End(xlToLeft).Column
            
            found = Range("k8:k11").Find(Cells(j, 6), Range("K8")).Row
            
            If Cells(j, 2).Value = Cells(I, 11).Value Then
                Cells(I, lastcol + 1).Value = Cells(j, 8).Value
            ElseIf Cells(j, 6).Value = Cells(I, 11).Value Then
                lastcol3 = Range("XFD" & found).End(xlToLeft).Column
                Cells(found, lastcol3 + 1).Value = Cells(j, 9).Value
            End If
        Next j
    Next I
End Sub

 

[Peterfc2]

Hi, My slight altered code for 2000.

Sub latestf_Button2_Click()

    Range("L2:IV2000").ClearContents
    
    lastrow = Range("B1").End(xlDown).Row
    lastrow2 = Range("K2").End(xlDown).Row
    
    For i = 2 To lastrow2
        For j = 2 To lastrow
            lastcol = Range("iv" & i).End(xlToLeft).Column
            
            found = Range("k8:k11").Find(Cells(j, 6), Range("K8")).Row
            
            If Cells(j, 2).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 8).Value
            ElseIf Cells(j, 6).Value = Cells(i, 11).Value Then
                lastcol3 = Range("iv" & found).End(xlToLeft).Column
                Cells(found, lastcol3 + 1).Value = Cells(j, 9).Value
            End If
        Next j
    Next i
End Sub

ends at

  found = Range("k8:k11").Find(Cells(j, 6), Range("K8")).Row

Can you check it please?

 

[Peterfc2]

Sub latestf_Button2_Click()

    Range("L2:IV2000").ClearContents
    
    lastrow = Range("B1").End(xlDown).Row
    lastrow2 = Range("K2").End(xlDown).Row
    
    For i = 2 To lastrow2
        For j = 2 To lastrow
            lastcol = Range("iv" & i).End(xlToLeft).Column
            
            found = Range("k34:k55").Find(Cells(j, 6), Range("K34")).Row
            
            If Cells(j, 2).Value = Cells(i, 11).Value Then
                Cells(i, lastcol + 1).Value = Cells(j, 8).Value
            ElseIf Cells(j, 6).Value = Cells(i, 11).Value Then
                lastcol3 = Range("iv" & found).End(xlToLeft).Column
                Cells(found, lastcol3 + 1).Value = Cells(j, 9).Value
            End If
        Next j
    Next i
End Sub

Sorted!
Once I worked out where to display the data it was fine.

found = Range("k34:k55").Find(Cells(j, 6), Range("K34")).Row

Thanks again Momentman.