Consulting

Results 1 to 5 of 5

Thread: Organizing Data with many categories

  1. #1

    Organizing Data with many categories

    Hello - I hope someone can help.

    I have a large amount of data. The format is simple. I have a name column, a score column, a category column. This is just an example.

    All i want to do is put each category (and corresponding score and name) into new columns. That's it. Please see attachment of exactly what I am looking for.

    Thank you very much!
    Attached Files Attached Files

  2. #2

  3. #3
    Thanks ranman. When I change the category to a string of letters - a word- rather than a letter, it doesn't seem to work. Also, note that my category names are not in alphabetical order. Lastly, is it possible to a put a blank column after "Name Score Category" before the macro moves on to the next category. Thanks.

  4. #4
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    nope, the loop is based on letters. Youd have to change the FOR 65 TO 90 loop to allow strings.

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    try this:
    Sub blah()
    Dim Uniques As New Collection
    If Not ActiveSheet.AutoFilter Is Nothing Then ActiveSheet.AutoFilter.Range.AutoFilter
    Set SourceData = Range("A1").CurrentRegion
    SourceData.AutoFilter
    On Error Resume Next
    For Each cll In SourceData.Columns(3).Offset(1).Resize(SourceData.Rows.Count - 1).Cells
      Uniques.Add cll.Value, cll.Value
    Next cll
    On Error GoTo here
    With SourceData
      DestColm = 6
      Application.ScreenUpdating = False
      For Each itm In Uniques
        .AutoFilter Field:=3, Criteria1:=itm
        SourceData.Copy Cells(1, DestColm)
        DestColm = DestColm + 3
      Next itm
    End With
    SourceData.AutoFilter
    here:
    Application.ScreenUpdating = True
    End Sub
    The above code is in the attached. On sheet2 there is a button, click it to run the above code to satisfy yourself that it works then adapt it for your needs.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •