Excel - Separating data into worksheets based on a column value

[KiwiRiccardo]

There might be another way of going about this but I am having a mental block.

I have a worksheet that contains data for five teams. The team is indicated by a column in the worksheet.

What I need to do is sepatate out and group each team data onto individual worksheets within the workbook.

I need this to be able to be included within a macro.

Obviously this could be done with a filter by team name but I am hoping that there will be a smarter way of doing this and there will not always be data for every team so I believe that this will cause a problem when running the macro.

Grateful for any advice.

Cheers

KiwiRiccardo

 

[SkipVought]

Kiwi,

Sounds like a homework problem to me.

What's the business case for this scenerio?

If this is a data table, then your worksheet design is screwed up too, having similar data in multiple columns. If your table were notmalized, this could easily be done via the PivotTable Wizard.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KiwiRiccardo]

Skip,

I think the data table is okay.

I had thought of using a pivot table using "Team" and then expanding the totals of the pivot table to create the sepatate sheets.

I do want to automate the process with a macro as I will not be the end user but I do not think that this will work as there will not always be data for every team in the table.

This is because the data table is based on monthly results with specific criteria. - result that the pivot will change each month thus making a macro difficult to apply.

 

[SkipVought]

What is the reason for separating each team to a sheet?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KiwiRiccardo]

The reason is simply that is what the business have requested the data in that format.

Going forward I do not want to get involved in the production of this report so I am trying to automate the process.

I was hoping that there might be a formula that could be used that would select rows based on the team column.

 

[SkipVought]

Formulas won't work because, you don't know how many rows you have.

Can the source data be modified? As I stated before, without using macros, what you want can be accomplished with PivotTables, if the source data is properly organized.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KiwiRiccardo]

I can be sure that I will not have any more that a few hundred rows in each team sheet so if a formula was down to the 1000th row (for example) this would resolve.

Agree totally that a pivot table would produce the info, but is will not produce the 100% pukka result I was hoping for.

 

[SkipVought]

What do you mean by, "...will not produce the 100% pukka result..."

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rdodge]

There is one way that I can think of:

Setup your worksheets with each of them being the same as the team names (if this is feasable cause certain characters are not allowed in the worksheet name)

HeaderRow is row 3
Data starts on row 4
Team name is in Column A

Then use the following VBA Code with the above situation:

Dim I as Long, F as Long, LR As Long, DataWSHT as Worksheet
Dim TeamWSHT as WorkSheet

Set DataWSHT = ThisWorkbook.Worksheets("Data&quot
F = 4 'Assuming your data starts on row 4 of the Data Worksheet.

If WS.Range("A4&quot.Value = "" or IsEmpty(WS.Range("A4&quot) Then
Exit Sub
Else
LR = WS.Range("A3&quot.End(xlDown).Row
End If

For I = 2 to Thisworkbook.Worksheets.Count Step 1
Thisworkbook.Worksheets(I).Range("4:65536&quot.ClearContents
Next I

For I = F to LR
If IsEmpty(Thisworkbook.Worksheets(DataWSHT.Range("A" & I).Value).Range("A4&quot) Then
DataWSHT.Range(I & ":" & I).Copy Thisworkbook.Worksheets(DataWSHT.Range("A" & I).Value).Range("A4&quot
Else
DataWSHT.Range(I & ":" & I).Copy Thisworkbook.Worksheets(DataWSHT.Range("A" & I).Value).Range("A3&quot.End(xlDown).Offset(1,0)
End If
Next I

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[KiwiRiccardo]

English east end slang. "Pukka" = the best, excellent, great etc... Think I have been in England too long.

 

[KiwiRiccardo]

rdodge,

Excellent, I will give this a hack and post an update next week.

Thanks.

5.30pm and beer o'clock.

Thanks for the replies Skip

 

[KenWright]

It can be done with formulas, despite not knowing how many rows, as long as you don't mind catering for a notional maximum, and the fact that it will all be array based.

I'm literally diving out the door for a works meal at the moment but have knoked up a dummy workbook for you. Will try and post it on the web when I get back tonight, or at least post the formulas.

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SkipVought]

Of course it can be done with formulas, if you load up each sheet with some notional maximum number of rows. At some point, you may begin to see some performance degredation.

AND, what happens if a number of unforseen factors change and instead of 1000 rows, cuz you never had more than 150, you exceed the limit.

SOMEONE has got to ADD MORE ROWS!

And in the mean time, you have not been getting the right answers!

Oh , well!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rdodge]

Formula writing is a very time expensive route to take, especially if you have the Calculation Mode set to Automatic. That's one reason why I provided a VBA code version of it. That way, depending on the user's needs, could setup a command button. Watch it, there's 2 ways of doing this, Forms toolbar and the Control Toolbox toolbar. The Forms toolbar allows you to assign macros to it very easy. The Control Toolbox toolbar method, you must click on View Code, then type in the name of the Macro name. Either way works, just the Forms one is easier to work with in this case. The user could also somehow have the code triggered by some event in Excel, or even decide to run it manually from the macro list when one clicks on the macro run button (Visual Basic Toolbar). Any of these ways will work depending on the need.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[SkipVought]

KiwiRiccardo,

Roger makes alot of sense!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Agree to an extent, and depending on the number of array formulas and how big the ranges are, it can slow a workbook down to a crawl. That having been said, a formula based solution is at least automatic, which unless tied to a change event, a vba solution isn't, and on top of that, many people prefer a non-vba solution ( Probably because they don't understand them though to be honest :-> ).

I agree 100% with Skip about losing data or having to add rows if you really cannot define the limits correctly, but if however, the limits really can be bounded such as a defined number of teams playing a defined number of games per season, then I think a formula based solution is at least a viable option. Only the OP is really in a position to judge the suitability of all the options offered in light of his own circumstances, but again he needs to take note of the caveats mentioned by all.

Assuming that the situation could be as above, one way would be as follows ( Have given up trying to post it on the web, as I am being completely useless at doing so :-< )

With initlally two sheets, first named Data Sheet and second named Team01, and with the assumptions as follows:-

Total of 5 teams
Max of 996 rows of results data all on Data Sheet
5 Sheets of results, one for each team.

'Data Sheet' sheet:-
--------------------
Headers in row 1, first set of data in row 4 carrying on down to row 1000. Col A has a tag of either:-

Team01 Team02 Team03 Team04 Team05 against each row of data.

Four results per row in the range B4:E1000

First this needs a helper column - Col H. In H4 put the following and copy down to H1000

=IF(A4=&quot;&quot;,&quot;&quot;,$A4&&quot;_&quot;&COUNTIF($A$4:$A4,A4))


'Team01' Sheet:-
----------------
A4 put a 1, A5 put a 2 and then fill down to A1000.
Format the cells with Conditional formatting such that if the cell to the right = &quot;&quot; then make the font of the cell in Col A white. (Select A4:A1000, then do Format / Cond Format / =B4=&quot;&quot; and hit the format button and choose a white font).

In cell A1 put Team01

Select B4:B1000 and in the formula bar, paste the following:-

=IF(ISERROR(INDEX('Data Sheet'!$B$4:$B$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0))),&quot;&quot;,INDEX('Data Sheet'!$B$4:$B$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0)))

and then array enter using CTRL+SHIFT+ENTER.

Select C4:C1000 and in the formula bar, paste the following:-

=IF(ISERROR(INDEX('Data Sheet'!$C$4:$C$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0))),&quot;&quot;,INDEX('Data Sheet'!$C$4:$C$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0)))

and then array enter using CTRL+SHIFT+ENTER.

Select D41000 and in the formula bar, paste the following:-

=IF(ISERROR(INDEX('Data Sheet'!$D$4:$D$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0))),&quot;&quot;,INDEX('Data Sheet'!$D$4:$D$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0)))

and then array enter using CTRL+SHIFT+ENTER.

Select E4:E1000 and in the formula bar, paste the following:-

=IF(ISERROR(INDEX('Data Sheet'!$E$4:$E$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0))),&quot;&quot;,INDEX('Data Sheet'!$E$4:$E$1000,MATCH($A$1&&quot;_&quot;&$A$4:$A$1000,'Data Sheet'!$H$4:$H$1000,0)))

and then array enter using CTRL+SHIFT+ENTER.

Format Col A the same as the Data Sheet with the cell becoming whitre if the corresponding cell in B is = &quot;&quot;

Now simply right click the Team01 sheet tab, select move or copy, choose create a copy and then do this four times, so that you have one sheet for each of 5 teams. On Team02's sheet make the cell A1 = Team02, make Team03's sheet A1 = Team03 and so on.

The Team01 / 02 / 03 bit can be changed for other tean=m names, but whatever is the team identifier in Col A on the Data Sheet, must also be in cell A1 on their team's sheet.

Now, as soon as data is updated on the 'data sheet', it will flow through to the relevant sheets. If there are only 75 results for a team, then you will see a list from 1 to 75 and the appropriate results against it.

Note Skip and Ronalds caveats:-

Any more than 996 results on Data Sheet and it bombs!!
Any more than 5 teams, you need to add more sheets!!

Personally though, I'd be as happy with any of them as long as they worked and didn't crawl. Ron's routine would certainly work for me.

Regards
Ken................


----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SkipVought]

kiwi,

Lots of good ideas to choose from!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Dsuperc]

Let me know if this works, I have a couple others.
Email be at Dsuperc@hotmail.com


Sub FanOut()
Dim ColHead As String
Dim ColHeadCell As Range
Dim iCol As Integer
Dim iRow As Long 'row index on Fan Data sheet
Dim lRow As Integer 'row index on individual destination sheet
Dim NewWB As Workbook
Dim Dsheet As Worksheet 'destination worksheet
Dim Fsheet As Worksheet 'fan data worksheet (assumed active)

Again:
ColHead = InputBox(&quot;Enter Column Heading&quot;, &quot;Identify Column&quot;, [H1].Value)
If ColHead = &quot;&quot; Then Exit Sub
Set ColHeadCell = Rows(1).Find(ColHead, lookat:=xlWhole)
If ColHeadCell Is Nothing Then
MsgBox &quot;Heading not found in row 1&quot;
GoTo Again
End If

Set Fsheet = ActiveSheet
Set NewWB = Workbooks.Add

iCol = ColHeadCell.Column
'loop through values in selected column
For iRow = 2 To Fsheet.Cells(65536, iCol).End(xlUp).Row
If Not SheetExists(CStr(Fsheet.Cells(iRow, iCol).Value)) Then
Set Dsheet = NewWB.Worksheets.Add(after:=NewWB.Worksheets(NewWB.Worksheets.Count))
Fsheet.Rows(1).Copy Destination:=Dsheet.Rows(1)
Dsheet.Name = CStr(Fsheet.Cells(iRow, iCol).Value)
Else
Set Dsheet = Worksheets(CStr(Fsheet.Cells(iRow, iCol).Value))
End If
lRow = Dsheet.Cells(65536, iCol).End(xlUp).Row
Fsheet.Rows(iRow).Copy Destination:=Dsheet.Rows(lRow + 1)
Next iRow

End Sub

Function SheetExists(SheetId As Variant) As Boolean

' This function checks whether a sheet (can be a worksheet,
' chart sheet, dialog sheet, etc.) exists, and returns
' True if it exists, False otherwise. SheetId can be either
' a sheet name string or an integer number. For example:

' If SheetExists(3) Then Sheets(3).Delete

' deletes the third worksheet in the workbook, if it exists.
' Similarly,

' If SheetExists(&quot;Annual Budget&quot Then Sheets(&quot;Annual Budget&quot.Delete

' deletes the sheet named &quot;Annual Budget&quot;, if it exists.

Dim Sh As Object
On Error GoTo NoSuch
Set Sh = Sheets(SheetId)
SheetExists = True
Exit Function
NoSuch:
If Err = 9 Then SheetExists = False Else Stop

End Function

 

[Dsuperc]

P.S. No praises to me it was done by somebody else.

 

[xlbo]

A pivot table is blatently the best idea here

a: It can be based on a dynamic range name - therefore, sourcedata never need be updated

2: There is a feature called &quot;Show Pages&quot; - choose this and the PT will automatically create a 1 page report for each unique item in the Page Field. If you stick &quot;Team&quot; in the page field, you will then get a report for each team.

Totally dynamic, very quick and pretty easy to code....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244