I have an excel file with 37k+ lines and I'm trying to mash it all together. The output of the file looks as follows:
[TABLE="width: 1082"]
[tr]
[TD="width: 142"][/TD]
[TD="width: 168"][/TD]
[TD="width: 195"][/TD]
[TD="width: 136"][/TD]
[TD="width: 108"][/TD]
[TD="width: 90"][/TD]
[TD="width: 150"][/TD]
[TD="width: 93"][/TD]
[/TABLE]
[TABLE="width: 1082"]
[tr]
[TD="width: 142"]product_type[/TD]
[TD="width: 168"]attribute_1[/TD]
[TD="width: 195"]attribute_2[/TD]
[TD="width: 136"]answer_1[/TD]
[TD="width: 108"]answer_2[/TD]
[TD="width: 90"]answer_3[/TD]
[TD="width: 150"]attribute_3[/TD]
[TD="width: 93"]attribute_4[/TD]
[/TABLE]
product type = string
attribute_1,3 = string, 8 possible string outputs
attribute_2 = string, outputs = yes or no
answer_1 = string, outputs = yes, no or yes with warning
answer_2,3 = string, output = yes or no
attribute_4 = string, output = with warning, no warning
each product_type has 8 attribute_1's, each attribute_1 has an attribute_2, each attribute_1 has an answer_1,2,3 , each product_1 has 8 attribute_3's and each attribute_3 has an attribute_4
That is why the output is 37k lines of duplicates. What I need the program to do is list each product_type ONLY 8 times, with the associated attributes 1,2 and the questions. Then in one excel cell I need all of the attribute_3's associated to "with warning" and in a separate cell all of the attribute_3's associated to "no warning"
the final output needs to look as follows:
attribute_2, answer_1,2,3 will be the same for each attribute_1
[TABLE="width: 1082"]
[tr]
[TD="width: 142"]product_type(8 times for each type)[/TD]
[TD="width: 168"]attribute_1(1 attribute per type...for a total of 8 attributes)[/TD]
[TD="width: 195"]attribute_2 [/TD]
[TD="width: 136"]answer_1[/TD]
[TD="width: 108"]answer_2[/TD]
[TD="width: 90"]answer_3[/TD]
[TD="width: 150"]attribute_3("no warning" from attribute_4)[/TD]
[TD="width: 93"]attribute_3("with warning" from attribute_4[/TD]
[/TABLE]
I put this pretty generic because I didn't want to post what the actual output looks like...whoever decides to help out will receive the output.
here is code that does exactly what I explained above...but it duplicates itself over several times...you will have to run it to understand
Public StartCol As String
Public StartRow As Integer
Public TotalRows As Integer
Public ThisProduct As String
Public LastProduct As String
Public ThisProductABO As String
Public LastProductABO As String
Public ThisPersonABO As String
Public LastPersonABO As String
Public ThisWarnType As String
Public LastWarnType As String
Public ThisRow As Integer
Public ProductMasterRow As Integer
Public LastRow As Integer
Public Sub Button1_Click()
ActiveWorkbook.Sheets("Sheet1").Activate
Range("B2").Select
With Sheets("Sheet1")
'find the total number of rows in the range
TotalRows = .Range("B" & .Rows.Count).End(xlUp).Row
ThisRow = 0
LastRow = 0
ProductMasterRow = 0
For ThisRow = 0 To TotalRows
ThisPersonABO = ActiveCell.Offset(ThisRow, 6)
ThisProduct = ActiveCell.Offset(ThisRow, 0)
ThisProductABO = ActiveCell.Offset(ThisRow, 1)
ThisWarnType = ActiveCell.Offset(ThisRow, 7)
If ThisProduct = LastProduct Then
If ThisProductABO = LastProductABO Then
If ThisWarnType = "No Warning" Then
If ActiveCell.Offset(ProductMasterRow, 15) = "" Then
ActiveCell.Offset(ProductMasterRow, 15) = ActiveCell.Offset(ProductMasterRow, 15) & ThisPersonABO
Else
ActiveCell.Offset(ProductMasterRow, 15) = ActiveCell.Offset(ProductMasterRow, 15) & "," & ThisPersonABO
End If
ElseIf ThisWarnType = "With Warning" Then
If ActiveCell.Offset(ProductMasterRow, 16) = "" Then
ActiveCell.Offset(ProductMasterRow, 16) = ActiveCell.Offset(ProductMasterRow, 16) & ThisPersonABO
Else
ActiveCell.Offset(ProductMasterRow, 16) = ActiveCell.Offset(ProductMasterRow, 16) & "," & ThisPersonABO
End If
End If
Else
ProductMasterRow = ProductMasterRow + 1
ActiveCell.Offset(ProductMasterRow, 10) = ThisProduct
ActiveCell.Offset(ProductMasterRow, 11) = ThisProductABO
ActiveCell.Offset(ProductMasterRow, 12) = ActiveCell.Offset(ProductMasterRow, 2)
ActiveCell.Offset(ProductMasterRow, 13) = ActiveCell.Offset(ProductMasterRow, 3)
If ThisWarnType = "No Warning" Then
If ActiveCell.Offset(ProductMasterRow, 15) = "" Then
ActiveCell.Offset(ProductMasterRow, 15) = ActiveCell.Offset(ProductMasterRow, 15) & ThisPersonABO
Else
ActiveCell.Offset(ProductMasterRow, 15) = ActiveCell.Offset(ProductMasterRow, 15) & "," & ThisPersonABO
End If
ElseIf ThisWarnType = "With Warning" Then
If ActiveCell.Offset(ProductMasterRow, 16) = "" Then
ActiveCell.Offset(ProductMasterRow, 16) = ActiveCell.Offset(ProductMasterRow, 16) & ThisPersonABO
Else
ActiveCell.Offset(ProductMasterRow, 16) = ActiveCell.Offset(ProductMasterRow, 16) & "," & ThisPersonABO
End If
End If
End If
Else
If ProductMasterRow = 0 Then
ProductMasterRow = LastRow
Else
ProductMasterRow = ProductMasterRow + 1
End If
ActiveCell.Offset(ProductMasterRow, 10) = ThisProduct
ActiveCell.Offset(ProductMasterRow, 11) = ThisProductABO
ActiveCell.Offset(ProductMasterRow, 12) = ActiveCell.Offset(ProductMasterRow, 2)
ActiveCell.Offset(ProductMasterRow, 13) = ActiveCell.Offset(ProductMasterRow, 3)
If ThisWarnType = "No Warning" Then
If ActiveCell.Offset(ProductMasterRow, 15) = "" Then
ActiveCell.Offset(ProductMasterRow, 15) = ActiveCell.Offset(ProductMasterRow, 15) & ThisPersonABO
Else
ActiveCell.Offset(ProductMasterRow, 15) = ActiveCell.Offset(ProductMasterRow, 15) & "," & ThisPersonABO
End If
ElseIf ThisWarnType = "With Warning" Then
If ActiveCell.Offset(ProductMasterRow, 16) = "" Then
ActiveCell.Offset(ProductMasterRow, 16) = ActiveCell.Offset(ProductMasterRow, 16) & ThisPersonABO
Else
ActiveCell.Offset(ProductMasterRow, 16) = ActiveCell.Offset(ProductMasterRow, 16) & "," & ThisPersonABO
End If
End If
End If
LastPersonABO = ThisPersonABO
LastProduct = ThisProduct
LastProductABO = ThisProductABO
Next
End With
End Sub
Display More
here is code I started working on to take a different angle at the problem
Public StartCol As String
Public StartRow As Double
Public TotalRows As Double
Public ThisProduct As String
Public LastProduct As String
Public ThisProductABO As String
Public LastProductABO As String
Public ThisPersonABO As String
Public LastPersonABO As String
Public ThisWarnType As String
Public LastWarnType As String
Public ThisRow As Double
Public ProductMasterRow As Double
Public LastRow As Double
Public pcount As Double
Public x As Integer
Public n As Integer
Public y As Integer
Public ind As Boolean
Public abo() As String
Public temp As Double
Public Sub Button1_Click()
ActiveWorkbook.Sheets("Sheet1").Activate
Range("B2").Select
With Sheets("Sheet1")
'find the total number of rows in the range
TotalRows = .Range("B" & .Rows.Count).End(xlUp).Row
TotalRows = TotalRows - 1
ThisRow = 0
LastRow = 0
ProductMasterRow = 0
temp = 2
n = 1
ind = False
'acquire number of different product categories
For x = 0 To TotalRows
If ActiveCell.Offset(x, 0) <> ActiveCell.Offset(x + 1, 0) Then
pcount = pcount + 1
End If
Next
'MsgBox pcount
'MsgBox TotalRows
'here for testing this statement only
LastProduct = "RBC"
ReDim abo(0 To n)
For x = 0 To TotalRows
ThisProduct = ActiveCell.Offset(ThisRow, 0)
If ThisProduct = LastProduct Then
For ThisRow = 0 To TotalRows
ThisProductABO = ActiveCell.Offset(ThisRow, 1)
'MsgBox ThisProductABO
'this For searches array to see if existing value is in it, concatenating array
For y = 0 To n
MsgBox n
If ThisProductABO <> abo(y) And ThisProductABO <> "" Then
n = n + 1
Else
ind = True
Exit For
End If
' LastProduct = ThisProduct
Next
'as long as product doesn't exist in array, add the product
If ind = False Then
ReDim abo(0 To n)
abo(n - 1) = ActiveCell.Offset(ThisRow, 1)
End If
Next
Else
Exit For
End If
Next
MsgBox abo(0)
'For x = 0 To pcount - 1
' ActiveCell.Offset(10, temp) = abo(x)
' temp = temp + 1
'Next
'
' ThisPersonABO = ActiveCell.Offset(ThisRow, 6)
' ThisWarnType = ActiveCell.Offset(ThisRow, 7)
End With
End Sub
Display More