Filtering / Sorting Issue

[Soccerbet]

Hey I have data like this:

[TABLE="width: 199"]
<tbody>[TR]
[TD]HomeTeam[/TD]
[TD]AwayTeam[/TD]
[/TR]
[TR]
[TD]Arsenal[/TD]
[TD]Aston Villa[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]Stoke[/TD]
[/TR]
[TR]
[TD]Norwich[/TD]
[TD]Everton[/TD]
[/TR]
[TR]
[TD]Sunderland[/TD]
[TD]Fulham[/TD]
[/TR]
[TR]
[TD]Swansea[/TD]
[TD]Man United[/TD]
[/TR]
[TR]
[TD]West Brom[/TD]
[TD]Southampton[/TD]
[/TR]
[TR]
[TD]West Ham[/TD]
[TD]Cardiff[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD]Hull[/TD]
[/TR]
[TR]
[TD]Crystal Palace[/TD]
[TD]Tottenham[/TD]
[/TR]
[TR]
[TD]Man City[/TD]
[TD]Newcastle[/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD]Aston Villa[/TD]
[/TR]
[TR]
[TD]Aston Villa[/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD]Everton[/TD]
[TD]West Brom[/TD]
[/TR]
[TR]
[TD]Fulham[/TD]
[TD]Arsenal[/TD]
[/TR]
[TR]
[TD]Hull[/TD]
[TD]Norwich[/TD]
[/TR]
[TR]
[TD]Newcastle[/TD]
[TD]West Ham[/TD]
[/TR]
[TR]
[TD]Southampton[/TD]
[TD]Sunderland[/TD]
[/TR]
[TR]
[TD]Stoke[/TD]
[TD]Crystal Palace[/TD]
[/TR]
[TR]
[TD]Cardiff[/TD]
[TD]Man City[/TD]
[/TR]
[TR]
[TD]Tottenham[/TD]
[TD]Swansea[/TD]
[/TR]
[TR]
[TD]Man United[/TD]
[TD]Chelsea[/TD]
[/TR]
[TR]
[TD]Cardiff[/TD]
[TD]Everton[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to sort or filter the list so that I have a list that contains all matches where for eksampel Arsenal is EITHER a home team OR an away team. But this seems to be a contraditory condition when I filter, because when I isolate Arsenal as a Home Team I cannot also filter it so they are the away team...... Any thoughts on this issue...?

 

[hiker95]

Soccerbet,

How about a macro solution using two arrays in memory, instead of sorting/filtering?

Sample raw data (just using columns A, and, B):

Excel 2007
	A	B	C	D	E	F
1	HomeTeam	AwayTeam
2	Arsenal	Aston Villa
3	Liverpool	Stoke
4	Norwich	Everton
5	Sunderland	Fulham
6	Swansea	Man United
7	West Brom	Southampton
8	West Ham	Cardiff
9	Chelsea	Hull
10	Crystal Palace	Tottenham
11	Man City	Newcastle
12	Chelsea	Aston Villa
13	Aston Villa	Liverpool
14	Everton	West Brom
15	Fulham	Arsenal
16	Hull	Norwich
17	Newcastle	West Ham
18	Southampton	Sunderland
19	Stoke	Crystal Palace
20	Cardiff	Man City
21	Tottenham	Swansea
22	Man United	Chelsea
23	Cardiff	Everton
24
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F
1	HomeTeam	AwayTeam			HomeTeam	AwayTeam
2	Arsenal	Aston Villa			Arsenal	Aston Villa
3	Liverpool	Stoke			Fulham	Arsenal
4	Norwich	Everton
5	Sunderland	Fulham
6	Swansea	Man United
7	West Brom	Southampton
8	West Ham	Cardiff
9	Chelsea	Hull
10	Crystal Palace	Tottenham
11	Man City	Newcastle
12	Chelsea	Aston Villa
13	Aston Villa	Liverpool
14	Everton	West Brom
15	Fulham	Arsenal
16	Hull	Norwich
17	Newcastle	West Ham
18	Southampton	Sunderland
19	Stoke	Crystal Palace
20	Cardiff	Man City
21	Tottenham	Swansea
22	Man United	Chelsea
23	Cardiff	Everton
24
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetArsenal()
' hiker95, 02/27/2014, ME760768
Dim a As Variant, o As Variant
Dim i As Long, ii As Long, n As Long
Application.ScreenUpdating = False
Columns("E:F").ClearContents
With Cells(1, 5).Resize(, 2)
  .Value = Cells(1, 1).Resize(, 2).Value
  .Font.Bold = True
End With
a = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
n = Application.CountIf(Columns("A:B"), "Arsenal")
ReDim o(1 To n, 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
  If a(i, 1) = "Arsenal" Then
    ii = ii + 1
    o(ii, 1) = a(i, 1)
    o(ii, 2) = a(i, 2)
  ElseIf a(i, 2) = "Arsenal" Then
    ii = ii + 1
    o(ii, 1) = a(i, 1)
    o(ii, 2) = a(i, 2)
  End If
Next i
Range("E2").Resize(UBound(o, 1), UBound(o, 2)) = o
Columns("E:F").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetArsenal macro.


If there are any other columns in your data set, what is the last used column?

I can adjust the macro to write the results three columns to the right of the last used column.