Extracting One of each entry from large data set

R

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
165
Office Version
  1. 2019
Platform
  1. Windows
Hi All,
I have a schedule with a bunch of information on it. It contains Team names in both Home team and away team columns as well as the group and the division those teams are playing in.
I want to extract a list of teams on the schedule as well as the group and division they are in into another sheet. I only want one entry for each team.
Here is the schedule sheet
uploaderIDcountergroupthisDivisiongameStarthomeTeamawayTeamVenuefieldNumbergameType
11Boys U12One2020-11-01 10:00Markham SC BluePickering FC GrayMonarch Park4Regular
12Boys U12One2020-11-01 10:00Brampton Elite GrayMarkham SC BlueMississauga3Regular
13Boys U12One2020-11-01 10:00SC Toronto NavyBrampton Elite GrayMonarch Park4Regular
14Boys U12One2020-11-01 11:00Pickering FC GraySC Toronto NavyMississauga4Regular
15Boys U12One2020-11-01 11:00Brampton Elite GrayMarkham SC BlueMississauga4Regular
16Boys U12One2020-11-01 12:00SC Toronto NavyPickering FC GrayMississauga1Regular
17Boys U12One2020-11-01 12:00Pickering FC GrayBrampton Elite GrayMississauga4Regular
18Boys U12One2020-11-01 12:00Markham SC BlueSC Toronto NavyMississauga2Regular
19Boys U12One2020-11-01 13:00SC Toronto NavyMarkham SC BlueMississauga4Regular
110Boys U12One2020-11-01 14:00Pickering FC GrayBrampton Elite GrayPickering SC1Regular
111Boys U12One2020-11-02 12:00Markham SC BluePickering FC GrayMississauga4Regular
112Boys U12One2020-11-02 12:00Brampton Elite GrayMarkham SC BlueWhitby SC2Regular
113Boys U12One2020-11-03 12:00SC Toronto NavyBrampton Elite GrayDownsview Park4Regular
114Boys U12One2020-11-04 12:00Pickering FC GraySC Toronto NavyMonarch Park4Regular
115Boys U12One2020-11-04 12:00Brampton Elite GrayMarkham SC BlueMonarch Park3Regular
116Boys U12One2020-11-05 10:00SC Toronto NavyPickering FC GrayMississauga4Regular
117Boys U12One2020-11-05 10:00Pickering FC GrayBrampton Elite GrayMississauga1Regular
118Boys U12One2020-11-05 10:00Markham SC BlueSC Toronto NavyMississauga4Regular
119Boys U12One2020-11-08 10:00SC Toronto NavyMarkham SC BlueMississauga4Regular
120Boys U12One2020-11-08 10:00Pickering FC GrayBrampton Elite GrayMississauga4Regular
121Boys U12One2020-11-09 10:00Markham SC BluePickering FC GrayMississauga1Regular
122Boys U12One2020-11-10 11:00Brampton Elite GrayMarkham SC BlueMississauga2Regular
123Boys U12One2020-11-15 10:00SC Toronto NavyBrampton Elite GrayMonarch Park3Regular
124Boys U12One2020-11-15 10:00Pickering FC GraySC Toronto NavyMonarch Park4Regular
125Boys U12One2020-11-15 10:00Brampton Elite GrayMarkham SC BlueDownsview Park4Regular
126Boys U12One2020-11-16 10:00SC Toronto NavyPickering FC GrayWhitby SC2Regular


and here is the sheet I am looking to import them into. I know I don't have all the information, but once the teams are populated I can adapt the info from there.

uploaderIDcountclubgenderageGroupgrouprosterteamNamedivisioncoachFirstNamecoachEmail
11Markham SCBoysU12Boys U12Markham SC Boys U12Markham SC BlueOneMatthew
12Markham SCBoysU12Boys U12Markham SC Boys U12Markham SC GreenTwoMatthew
13Markham SCBoysU12Boys U12Markham SC Boys U12Markham SC YellowThreeMatthew
14Markham SCGirlsU12Girls U12Markham SC Girls U12Markham SC RedOneSally
15Markham SCGirlsU12Girls U12Markham SC Girls U12Markham SC OrangeTwoSally
16Markham SCGirlsU12Girls U12Markham SC Girls U12Markham SC YellowThreeSally
17Markham SCBoysU14Boys U14Markham SC Boys U14Markham SC BlueOneMatthew
18Markham SCBoysU14Boys U14Markham SC Boys U14Markham SC GreenTwoMatthew
19Markham SCBoysU14Boys U14Markham SC Boys U14Markham SC YellowThreeMatthew
110Brampton EliteBoysU12Boys U12Brampton Elite Boys U12Brampton Elite GrayOneBryce
111Brampton EliteBoysU12Boys U12Brampton Elite Boys U12Brampton Elite YellowTwoBryce
112Brampton EliteBoysU12Boys U12Brampton Elite Boys U12Brampton Elite WhiteThreeSteve
113SC TorontoBoysU12Boys U12SC Toronto Boys U12SC Toronto NavyOnePaul
114SC TorontoBoysU12Boys U12SC Toronto Boys U12SC Toronto GrayTwoAndew
115SC TorontoBoysU12Boys U12SC Toronto Boys U12SC Toronto RedThreeSally
116Pickering FCBoysU12Boys U12Pickering FC Boys U12Pickering FC GrayOneCraig
117Pickering FCBoysU12Boys U12Pickering FC Boys U12Pickering FC BlueTwoCraig
118Pickering FCBoysU12Boys U12Pickering FC Boys U12Pickering FC YellowThreeCraig
119Pickering FCBoysU12Boys U12Pickering FC Boys U12Pickering FC WhiteThreeCraig


So I would be looking to pull the Team name, Division and Group from the 1st page and fill those in, in the 2nd sheet. Obviously each team will play more than one game, so I only want to get one input for each team.

any help would be greatly appreciated.
 
Last edited by a moderator:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hello,

How is this for starters?

VBA Code: 
Sub TEAM_NAMES()
    Application.ScreenUpdating = False
    Range("L1").Value = "TEAMS"
    Range("L2").Formula = "=F2&D2&C2"
    Range("L2").Copy
    Range("L3:L" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
    
    
    Columns("L:L").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("M1" _
        ), Unique:=True
        
    For MY_ROWS = 2 To Range("M" & Rows.Count).End(xlUp).Row
        MY_TEAM = Range("M" & MY_ROWS).Value
        Range("A1:L1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=12, Criteria1:=MY_TEAM
        With Sheets("Sheet2")
            .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("F" & Rows.Count).End(xlUp).Value
            .Range("I" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("D" & Rows.Count).End(xlUp).Value
            .Range("F" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("C" & Rows.Count).End(xlUp).Value
        End With
        Selection.AutoFilter
    Next MY_ROWS
    Columns("L:M").ClearContents
    Application.ScreenUpdating = True
End Sub

I have assumed your data on both sheets starts in cell A1, also assumed columns L & M are available on source sheet.
You will need to change name of Sheet2 to your destination sheet name.

Is this of any use?
 
Upvote 0
onlyadrafter said:
Hello,

How is this for starters?

VBA Code: 
Sub TEAM_NAMES()
    Application.ScreenUpdating = False
    Range("L1").Value = "TEAMS"
    Range("L2").Formula = "=F2&D2&C2"
    Range("L2").Copy
    Range("L3:L" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
  
  
    Columns("L:L").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("M1" _
        ), Unique:=True
      
    For MY_ROWS = 2 To Range("M" & Rows.Count).End(xlUp).Row
        MY_TEAM = Range("M" & MY_ROWS).Value
        Range("A1:L1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=12, Criteria1:=MY_TEAM
        With Sheets("Sheet2")
            .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("F" & Rows.Count).End(xlUp).Value
            .Range("I" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("D" & Rows.Count).End(xlUp).Value
            .Range("F" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("C" & Rows.Count).End(xlUp).Value
        End With
        Selection.AutoFilter
    Next MY_ROWS
    Columns("L:M").ClearContents
    Application.ScreenUpdating = True
End Sub

I have assumed your data on both sheets starts in cell A1, also assumed columns L & M are available on source sheet.
You will need to change name of Sheet2 to your destination sheet name.

Is this of any use?
Click to expand...
Thanks so much. it is certainly pulling team names, but there are some duplicates. I have headers so my data starts on row 2 not row 1.
 
Upvote 0
onlyadrafter said:
Hello,

How is this for starters?

VBA Code: 
Sub TEAM_NAMES()
    Application.ScreenUpdating = False
    Range("L1").Value = "TEAMS"
    Range("L2").Formula = "=F2&D2&C2"
    Range("L2").Copy
    Range("L3:L" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
   
   
    Columns("L:L").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("M1" _
        ), Unique:=True
       
    For MY_ROWS = 2 To Range("M" & Rows.Count).End(xlUp).Row
        MY_TEAM = Range("M" & MY_ROWS).Value
        Range("A1:L1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=12, Criteria1:=MY_TEAM
        With Sheets("Sheet2")
            .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("F" & Rows.Count).End(xlUp).Value
            .Range("I" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("D" & Rows.Count).End(xlUp).Value
            .Range("F" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("C" & Rows.Count).End(xlUp).Value
        End With
        Selection.AutoFilter
    Next MY_ROWS
    Columns("L:M").ClearContents
    Application.ScreenUpdating = True
End Sub

I have assumed your data on both sheets starts in cell A1, also assumed columns L & M are available on source sheet.
You will need to change name of Sheet2 to your destination sheet name.

Is this of any use?
Click to expand...
This works great, but if I run it from a button on a different sheet it does not work. I assume I need to reference the working sheet. I tried a couple of things, but could not make it work.
Sorry.
 
Upvote 0
Hello,

no problem, try

VBA Code: 
Sub TEAM_NAMES()
    Application.ScreenUpdating = False
    MY_SHEET = ActiveSheet.Name
    Sheets("Sheet1").Select 'CHANGE SHEET1 TO YOUR SHEET NAME
    Range("L1").Value = "TEAMS"
    Range("L2").Formula = "=F2&D2&C2"
    Range("L2").Copy
    Range("L3:L" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
    Columns("L:L").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("M1" _
        ), Unique:=True
    For MY_ROWS = 2 To Range("M" & Rows.Count).End(xlUp).Row
        MY_TEAM = Range("M" & MY_ROWS).Value
        Range("A1:L1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=12, Criteria1:=MY_TEAM
        With Sheets("Sheet2")
            .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("F" & Rows.Count).End(xlUp).Value
            .Range("I" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("D" & Rows.Count).End(xlUp).Value
            .Range("F" & .Rows.Count).End(xlUp).Offset(1, 0).Value = Range("C" & Rows.Count).End(xlUp).Value
        End With
        Selection.AutoFilter
    Next MY_ROWS
    Columns("L:M").ClearContents
    Sheets(MY_SHEET).Select
    Application.ScreenUpdating = True
End Sub

this will return you to the sheet you were in. You will need to change Sheet1 to your tab name with the source data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
122
etaf
etaf
M
  • Solved
Formula Help.
Replies
3
Views
105
Fluff
Fluff
C
  • Question
Incorrect date
2 3
Replies
25
Views
516
awoohaw
awoohaw
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
251
SaraWitch
SaraWitch
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
379
kevin9999
K

Forum statistics

Threads
1,215,427
Messages
6,124,831
Members
449,190
Latest member
rscraig11

Share this page

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