3 Columns of data consolidated, help a guy

[Fordcd88]

I work in Info Assurance and trying to finish up my excel worksheet. This is one of the last steps. Using Excel 2010

Would much appreciate some help

I have 3 columns of Data (that could always be different in data and length), (Also some cells are left blank)

IA | CAT | Status |
--------------------------------------
ABCD-1 | I | Ongoing
--------------------------------------
ABCD-1 | II | Ongoing
--------------------------------------
ABCD-1 | II | Completed
--------------------------------------
ABCD-2 | |
--------------------------------------
DCBA-3 | II | Archived
--------------------------------------
ABCD-2 | III | Ongoing
--------------------------------------
ABCD-1 | II |
--------------------------------------
ABCD-2 | I | Ongoing


The outcome I need (6 Columns) in other cells for ONGOING only,

CAT I
1st column: Number of matches of IA
2nd Column: IA Name (ABCD-1)

CAT II
3rd column: Number of matches of IA
4th Column: IA Name (ABCD-1)

CAT III
5th column: Number of matches of IA
6th Column: IA Name (ABCD-1)





CAT I | CAT II | CAT III

1 ABCD-1 | 1 ABCD-1 | 1 ABCD-2

2 ABCD-2 | 1 DCBA-3 |




Thank you!

 

[NeonRedSharpie]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]IA[/TD]
[TD]CAT[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]ABCD-1[/TD]
[TD]I[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]ABCD-1[/TD]
[TD]II[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]ABCD-1[/TD]
[TD]II[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]ABCD-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DCBA-3[/TD]
[TD]II[/TD]
[TD]Archived[/TD]
[/TR]
[TR]
[TD]ABCD-2[/TD]
[TD]III[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]ABCD-1[/TD]
[TD]II[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ANCD-2[/TD]
[TD]I[/TD]
[TD]Ongoing[/TD]
[/TR]
</tbody>[/TABLE]




Do you mean six columns or a 3x(value) grid for your results?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]CAT I[/TD]
[TD]CAT II[/TD]
[TD]CAT III[/TD]
[/TR]
[TR]
[TD]ABCD-1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABCD-2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DCBA-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The results table populated using sample data.

 

[Fordcd88]

Note: I work for a fairly large cyber entity and will give credit where it is due. If the macro / code I use is yours (no matter how small a piece I add) I will contact you via PM and see if you want to be credited during the review of my final document. I won't credit you unless you give me authorization to do so. (Unfortunately the dept. I work in is scarce on those who know excel vb.)

Here is the table I currently have:

• Under IA (Information Assurance) is a control code.There are 157 different types which could vary from packet to packet. I deal with a bunch of packets every day and having to go through each one and manually count each item kills my productivity. (Even =COUNTIF doesn't do the outcome I need.) I need a list of the items.

• The CAT levels only go from I, II, III and some are without.

• Different Status options are: (Nothing in the cell, "Ongoing", "Completed", "Archived", etc. etc.)

• I only care about the Ongoing

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]IA[/TD]
[TD="align: center"]CAT[/TD]
[TD="align: center"]Status[/TD]
[/TR]
[TR]
[TD]AABB-1[/TD]
[TD]I[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]AABB-1[/TD]
[TD]II[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]AABB-1[/TD]
[TD]I[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]CCDD-2[/TD]
[TD]III[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]CCDD-2[/TD]
[TD]II[/TD]
[TD]Archived[/TD]
[/TR]
[TR]
[TD]EEFF-2[/TD]
[TD]III[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]FFGG-3[/TD]
[TD]II[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]HIJK-2[/TD]
[TD]I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HIJL-1[/TD]
[TD]I[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]HHJJ-2[/TD]
[TD][/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]HHJJ-2[/TD]
[TD]I[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]HHJJ-2[/TD]
[TD]II[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]HHJJ-2[/TD]
[TD]II[/TD]
[TD]Ongoing[/TD]
[/TR]
[TR]
[TD]HHJJ-2[/TD]
[TD]II[/TD]
[TD]Ongoing[/TD]
[/TR]
</tbody>[/TABLE]
























What I'd like it to look like: (Ongoing Only)
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Amount I[/TD]
[TD]CAT I[/TD]
[TD]Amount II[/TD]
[TD]CAT II[/TD]
[TD]Amount III[/TD]
[TD]CAT III[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]AABB-1[/TD]
[TD="align: center"]3[/TD]
[TD]HHJJ-2[/TD]
[TD="align: center"]1[/TD]
[TD]CCDD-2[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]HIJL-1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD]EEFF-2[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]HIJJ-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Note: I'm Wanting it to be Dynamically structured (Changes with different amounts of data). I usually have to go through between 100-600 IA Items per packet.

After I can get something like this to work I wanted to also be able to auto-copy info into a cell where its separated by comma for each control. This will allow me to copy and paste it into email allowing a much faster turn around.
(Ongoing Only)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CAT I: (x1) AABB, (x1) HIJL-1, (x1) HIJJ-2[/TD]
[/TR]
[TR]
[TD]CAT II: (x3) HHJJ-2[/TD]
[/TR]
[TR]
[TD]CAT III: (x1) CCDD-2, (x1) EEFF-2[/TD]
[/TR]
</tbody>[/TABLE]

 

[NeonRedSharpie]

I split it into three codes, just so it made more sense to me as I was building it. This will also allow for modifications later.

Sub dataConvert()

'
' This macro will gather the unique values in column A of "DATA"
' and place them into an array.  It will then loop through the array
' of unique values, and call the "dataConsolidation" macro.


'
' After the "dataConsolidation" macro, this will call the "dataPrint"
' macro.
'




Dim valueCheck As String
Dim rng As Range
Dim myarray, myunique
Dim i As Integer


ReDim myunique(1)


With ActiveWorkbook.Sheets("DATA")
    Set rng = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
    myarray = Application.Transpose(rng)
    For i = LBound(myarray) To UBound(myarray)
        If IsError(Application.Match(myarray(i), myunique, 0)) Then
            myunique(UBound(myunique)) = myarray(i)
            ReDim Preserve myunique(UBound(myunique) + 1)
        End If
    Next
End With


For i = LBound(myunique) To UBound(myunique)
    valueCheck = myunique(i)
    Call dataConsolidation(valueCheck)
Next


    Call dataPrint


End Sub

Above is the main function. You will call this to begin your macro.

Sub dataConsolidation(valueCheck As String)

'
' This macro will take a string as an input, and pass it
' through multiple checks.  At each check, if the values pass,
' it will place the value onto a new sheet in the correct format.
'
'


    Dim statusType As String
    
    Dim rowMin As Integer
    Dim rowMax As Long
    
    Dim rowNextI As Long
    Dim rowNextII As Long
    Dim rowNextIII As Long
    
    Dim counterI As Long
    Dim counterII As Long
    Dim counterIII As Long
    
    Dim dataSheet As Worksheet
    Dim results As Worksheet
    
    Set dataSheet = ActiveWorkbook.Sheets("DATA")
    Set results = ActiveWorkbook.Sheets("Results")


    'Determine the start and end value of the data
        ' NOTE: This assumes headers.  If not headers:
            'rowMin = 1
    rowMin = 2
    rowMax = dataSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Determine where to paste the values found
    With results
        rowNextI = .Cells(Rows.Count, "A").End(xlUp).Row + 1
        rowNextII = .Cells(Rows.Count, "C").End(xlUp).Row + 1
        rowNextIII = .Cells(Rows.Count, "E").End(xlUp).Row + 1
    End With
        
    ' Validate the status type.  This can change to any value
    statusType = "Ongoing"
    
    With results
        If .Cells(1, 1).Value = "" Then
            .Cells(1, 1).Value = "Amount I"
            .Cells(1, 2).Value = "CAT I"
            .Cells(1, 3).Value = "Amount II"
            .Cells(1, 4).Value = "CAT II"
            .Cells(1, 5).Value = "Amount III"
            .Cells(1, 6).Value = "CAT III"
        End If
    End With
    
    ' Begin the loop
    For x = rowMin To rowMax Step 1
    
        ' Count instances of CaseI, CaseII, and CaseIII
        With dataSheet
            If .Cells(x, 1).Value = valueCheck And _
                .Cells(x, 3) = statusType Then
            
                Select Case .Cells(x, 2).Value
                    Case "I"
                        counterI = counterI + 1
                    Case "II"
                        counterII = counterII + 1
                    Case "III"
                        counterIII = counterIII + 1
                    Case Else
                        'Do nothing
                End Select
            End If
        End With
    Next x
    
    ' Paste the values into "Results"
    With results
        Select Case counterI
            Case Is < 1
                'Do nothing
            Case Is > 0
                .Cells(rowNextI, 1).Value = counterI
                .Cells(rowNextI, 2).Value = valueCheck
        End Select
                
        Select Case counterII
            Case Is < 1
                'Do nothing
            Case Is > 0
                .Cells(rowNextII, 3).Value = counterII
                .Cells(rowNextII, 4).Value = valueCheck
        End Select


        Select Case counterIII
            Case Is < 1
                'Do nothing
            Case Is > 0
                .Cells(rowNextIII, 5).Value = counterIII
                .Cells(rowNextIII, 6).Value = valueCheck
        End Select
    End With


End Sub

Above is the consolidation macro. This will create your helper table show in your sample data set. You will need the worksheet "Results" to have this macro work.

Sub dataPrint()

'
' This macro will take the information gathered in the "Results" tab
' and place it into cells A1:A3 in the "CommaVal" tab.  It will place
' the values between commas, and allow for faster transportation
' of data.
'


    Dim results As Worksheet
    Dim commaVal As Worksheet
    
    Dim stringCatI As String
    Dim stringCatII As String
    Dim stringCatIII As String
    
    Dim rowI As Long
    Dim rowII As Long
    Dim rowIII As Long
    
    Set results = ActiveWorkbook.Sheets("Results")
    Set commaVal = ActiveWorkbook.Sheets("Comma Output")
    
    ' Qualify the leading string
    stringCatI = "CAT I: "
    stringCatII = "CAT II: "
    stringCatIII = "CAT III: "
    
    
    With results
        ' Determine the end row for the string
        rowI = .Cells(Rows.Count, "A").End(xlUp).Row
        rowII = .Cells(Rows.Count, "C").End(xlUp).Row
        rowIII = .Cells(Rows.Count, "E").End(xlUp).Row
    
        ' Begin to loop through the different columns
        For x = 2 To rowI Step 1
            ' If it's not the final row:
            If x <> rowI Then
                stringCatI = stringCatI & "(x" & .Cells(x, 1) & _
                                ") " & .Cells(x, 2) & ", "
            ' If it is the final row, remove the ending comma
            Else
                stringCatI = stringCatI & "(x" & .Cells(x, 1) & _
                                ") " & .Cells(x, 2)
            End If
        Next x
        
        For x = 2 To rowII Step 1
            If x <> rowII Then
                stringCatII = stringCatII & "(x" & .Cells(x, 3) & _
                                ") " & .Cells(x, 4) & ", "
            Else
                stringCatII = stringCatII & "(x" & .Cells(x, 3) & _
                                ") " & .Cells(x, 4)
            End If
        Next x
        
        For x = 2 To rowIII Step 1
            If x <> rowIII Then
                stringCatIII = stringCatIII & "(x" & .Cells(x, 5) & _
                                ") " & .Cells(x, 6) & ", "
            Else
                stringCatIII = stringCatIII & "(x" & .Cells(x, 5) & _
                                ") " & .Cells(x, 6)
            End If
        Next x
    
    End With
    
    ' Print the strings to the commaVal sheet
    With commaVal
        .Cells(1, 1).Value = stringCatI
        .Cells(2, 1).Value = stringCatII
        .Cells(3, 1).Value = stringCatIII
    End With
    
End Sub

Above is the final macro that converts the table into a comma string. You will need the worksheet "CommaVal" for this to work.

Test these out on a sample set, and let me know if it works. If you don't want to have to add new sheets, we can do that too. Just thought this was easier to read through. Hope this is what you were looking for.

 

[Fordcd88]

Exactly what I needed! Such a clean well working code.
Thanks!!