I have a worksheet in Excel that needs to be 'sliced and diced', the logic applied needs to be able to be replicated.  The main worksheet contains all rows, there are duplicate rows, but I need to parse rows so that no row contains more then 5 duplicates, if there are more then five duplicates, then this needs to feed into another worksheet or 'cohort'.  I am at a loss at a script that can accomplish this, and certainly do not want to 'cut an paste' to achieve what I need.  I included an example
HelpEE.xlsx
Senior Director of Sales and Market DevelopmentAsked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
``````Sub parsecol()
Dim cel As Range
Dim count As Integer
Set cel = Sheets("Main Sheet").Range("A2")
Do While cel <> ""
If cel <> cel.Offset(-1) Then
count = 1
Else
count = count + 1
End If
On Error Resume Next
Sheets("Cohor " & Int((count - 1) / 5) + 1).Range("A" & Rows.count).End(xlUp).Offset(1) = cel
If Err Then
ActiveSheet.Name = ("Cohor " & Int(count / 5) + 1)
Sheets("Main Sheet").Select
Sheets("Cohor " & Int((count - 1) / 5) + 1).Range("A1") = "Company"
Sheets("Cohor " & Int((count - 1) / 5) + 1).Range("A" & Rows.count).End(xlUp).Offset(1) = cel
End If
On Error GoTo 0
Set cel = cel.Offset(1)
Loop
End Sub
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Older than dirtCommented:
Syed's answer is excellent (I didn't even understand the question!) but let me suggest the following changes. In my version I corrected his sheet name typo, added lines 5 and 28 to prevent flickering, and added line 18 so the Cohort sheets are added after Main Sheet.

``````Sub parsecol()
Dim cel As Range
Dim count As Integer

Application.ScreenUpdating = False

Set cel = Sheets("Main Sheet").Range("A2")
Do While cel <> ""
If cel <> cel.Offset(-1) Then
count = 1
Else
count = count + 1
End If
On Error Resume Next
Sheets("Cohort " & Int((count - 1) / 5) + 1).Range("A" & Rows.count).End(xlUp).Offset(1) = cel
If Err Then
ActiveSheet.Move After:=Sheets(Sheets.count)
ActiveSheet.Name = ("Cohort " & Int(count / 5) + 1)
Sheets("Main Sheet").Select
Sheets("Cohort " & Int((count - 1) / 5) + 1).Range("A1") = "Company"
Sheets("Cohort " & Int((count - 1) / 5) + 1).Range("A" & Rows.count).End(xlUp).Offset(1) = cel
End If
On Error GoTo 0
Set cel = cel.Offset(1)
Loop

Application.ScreenUpdating = True
End Sub
``````
0
EngineerCommented:
Thanks, Martin, the typo was intentional for testing and comparing with the original. It was intended to be corrected before posting.
Flickering: I wonder why I am never impressed with "No flickering" so I never consider it unless specifically asked for.
After Main sheet: It definitely looks better that way.
0
Older than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for Saqib Husain, Syed's comment #a40807428
Assisted answer: 250 points for Martin Liss's comment #a40807985

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Senior Director of Sales and Market DevelopmentAuthor Commented:
This was so helpful, thank you!  Apologies for the delay in getting back to you.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.