That worked really well. Now I have a new problem. I solved it but it, is not efficient.
Ex: If i had a column titled Animals that has unique values of Pony, Cat, Dog...
I need a new worksheet with 3 columns titled Pony, Cat, Dog and all of the original values listed below.
Reason: I filldown a formula to return a 1 or 0 using the value in the first cell of the column so I can use a Pivot table to analyze the data.
Column1[Animal, Dog, Dog Cat, Cat, Pony, Dog]
Becomes
Column1[dog, 1, 1, 0, 0, 0, 1]
Column2[cat, 0, 0, 1, 1, 0, 0]
Column3[pony, 0, 0, 0, 0, 1, 0]
Code below prompts the user to input 3 columns, the 2 of interest in this problem are "StatusColumn" and "Causedbycolumn"
Is there a more efficient way of doing this? My solution below is childish, but it works.
Code:
Private Sub CommandButton3_Click()
Dim ControlColumn As Range
Dim StatusColumn As Range
Dim CausedBycolumn As Range
Dim LR As Long
Dim Cl As Range
Dim i As Long, Col As Long
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Statuscount As Integer, Causedbycount As Integer, Offset As Integer
'Select the three columns of interest
Set Ws1 = Sheets("POAM Entries destination")
Set Ws2 = Sheets("Workspace")
Sheets("POAM entries destination").Select
Set ControlColumn = Application.InputBox(prompt:="Select Control Column", Title:=" Control Column", Default:="A1", Type:=8)
Set StatusColumn = Application.InputBox(prompt:="Select Status Column", Title:="Status Column", Default:="A1", Type:=8)
Set CausedBycolumn = Application.InputBox(prompt:="Select Caused By Column", Title:="Caused by Column", Default:="A1", Type:=8)
'Display total number of columns in Cell (for debugging)
LR = Ws1.UsedRange.Rows.Count
'From Mr Excel -
Col = StatusColumn.Column
With CreateObject("scripting.dictionary")
For i = 2 To 3
If i = 3 Then Col = CausedBycolumn.Column
For Each Cl In Ws1.Range(Ws1.Cells(2, Col), Ws1.Cells(LR, Col))
If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
Next Cl
Ws2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).resize(, .Count) = .keys
Ws2.Range("A" & i).Value = .Count
.RemoveAll
Next i
End With
Ws2.Select
Ws2.Range("A2").Activate
Statuscount = ActiveCell.Value
Ws2.Range("A3").Activate
Causedbycount = ActiveCell.Value
Offset = Statuscount - 1
MsgBox "The value of offset is " & Offset, vbInformation
Ws2.Select
Ws2.Rows(1).EntireRow.Copy
Sheets("Workspace2").Select
Sheets("Workspace2").Rows(1).Select
Sheets("Workspace2").Paste
For i = 2 To Statuscount
LR = Ws1.UsedRange.Rows.Count
Ws2.Select
StatusColumn.Copy
Ws2.Cells(1, i).PasteSpecial Paste:=xlPasteValues
Next i
For j = (Statuscount + 1) To (Statuscount + Causedbycount - 1)
LR = Ws1.UsedRange.Rows.Count
Ws2.Select
CausedBycolumn.Copy
Ws2.Cells(1, j).PasteSpecial Paste:=xlPasteValues
Next j
Sheets("Workspace2").Rows(1).EntireRow.Copy
Ws2.Rows(1).Select
Ws2.Paste
End Sub