Filtering / Sorting Issue

Soccerbet

New Member
Joined
Feb 27, 2014
Messages
2
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...?
 
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
ABCDEF
1HomeTeamAwayTeam
2ArsenalAston Villa
3LiverpoolStoke
4NorwichEverton
5SunderlandFulham
6SwanseaMan United
7West BromSouthampton
8West HamCardiff
9ChelseaHull
10Crystal PalaceTottenham
11Man CityNewcastle
12ChelseaAston Villa
13Aston VillaLiverpool
14EvertonWest Brom
15FulhamArsenal
16HullNorwich
17NewcastleWest Ham
18SouthamptonSunderland
19StokeCrystal Palace
20CardiffMan City
21TottenhamSwansea
22Man UnitedChelsea
23CardiffEverton
24
Sheet1


After the macro:


Excel 2007
ABCDEF
1HomeTeamAwayTeamHomeTeamAwayTeam
2ArsenalAston VillaArsenalAston Villa
3LiverpoolStokeFulhamArsenal
4NorwichEverton
5SunderlandFulham
6SwanseaMan United
7West BromSouthampton
8West HamCardiff
9ChelseaHull
10Crystal PalaceTottenham
11Man CityNewcastle
12ChelseaAston Villa
13Aston VillaLiverpool
14EvertonWest Brom
15FulhamArsenal
16HullNorwich
17NewcastleWest Ham
18SouthamptonSunderland
19StokeCrystal Palace
20CardiffMan City
21TottenhamSwansea
22Man UnitedChelsea
23CardiffEverton
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.

Code:
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top