Parsing Excel Spreadsheet

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
Kristin MehielSenior 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.

Saqib Husain, SyedEngineerCommented:
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
                Sheets.Add
                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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Martin LissOlder 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
                Sheets.Add
                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

Open in new window

0
Saqib Husain, SyedEngineerCommented:
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
Martin LissOlder 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
Kristin MehielSenior 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.