Assistance appreciated for how to get started on a project

[jrevans778]

Hello! First, I want to say how much I appreciate this community... it's been a huge help with my Excel questions!

Input data example


[TABLE="width: 683"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]1/24/2016[/TD]
[TD]Paint, Standard Green[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/24/2016[/TD]
[TD]Shelf, H1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/24/2016[/TD]
[TD]Backer, H1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/14/2016[/TD]
[TD]Shelf, H1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/14/2016[/TD]
[TD]Backer, H1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/14/2016[/TD]
[TD]Paint, Standard Red[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/14/2016[/TD]
[TD]Towels, Small[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]James Jackson[/TD]
[TD]2/4/2016[/TD]
[TD]Towels, Large[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/4/2016[/TD]
[TD]Towels, Small[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/4/2016[/TD]
[TD]Towels, Med[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/4/2016[/TD]
[TD]Paint, Standard Red[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/4/2016[/TD]
[TD]Paint, Standard Green[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/5/2016[/TD]
[TD]Labels, SZ2, 120ct[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/5/2016[/TD]
[TD]Pen, black, 200ct[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]James Jackson[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]41[/TD]
[/TR]
</tbody>[/TABLE]




Desired output

[TABLE="width: 962"]
<tbody>[TR]
[TD][/TD]
[TD]Paint, Standard Green[/TD]
[TD]Paint, Standard Red[/TD]
[TD]Shelf, H1[/TD]
[TD]Backer, H1[/TD]
[TD]Towels, Small[/TD]
[TD]Towels, Med[/TD]
[TD]Towels, Large[/TD]
[TD]Labels, SZ2, 120ct[/TD]
[TD]Pen, black, 200ct[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]17[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]James Jackson[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]29[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]



The input data is posted from a report and the columns will always be the same but obviously the rest of the data will vary. I want to create a macro using vba to convert the input data to the desired format. I'm thinking I'd need to loop through the products per customer and total each in a variable but I'm not really sure how to start this.

ANY assistance would be GREATLY appreciated!

 

[JLGWhiz]

If I were to do it, I would first build my output sheet manually as per your illustation. List all products using a standard product name. Ideally, a product code would minimize the probablility of mismatched products due to differences in text structure. Database structure is critical in project d3evelopment. But it can be done without a code if the input data is prepared per a standard. Once the output sheet is built, I could then use the names in column A of the output sheet to find the data on the input sheet, Looping down the date column until it encounters "Total" which would tell me that all items for that name had been addressed. As the loop iterates, The code would copy the offset values in the quantiy column to the matching product column in the output sheet.
If you draw out your steps in a block diagram and then code to the diagram, it is easier to follow.

 

[wideboydixon]

Public Sub TransposeData()

Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long
Dim nextCol As Long
Dim thisCol As Long
Dim columnHeaders As Object
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet

Set columnHeaders = CreateObject("Scripting.Dictionary")
Set sourceSheet = ActiveSheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
Set targetSheet = Sheets.Add(After:=Sheets(Sheets.Count))

nextRow = 1
nextCol = 1
For thisRow = 2 To lastRow
    If sourceSheet.Cells(thisRow, "B").Value <> "Total" Then
        If sourceSheet.Cells(thisRow, "A").Value <> "" Then
            nextRow = nextRow + 1
            targetSheet.Cells(nextRow, "A").Value = sourceSheet.Cells(thisRow, "A").Value
        End If
        If columnHeaders.Exists(sourceSheet.Cells(thisRow, "C").Value) Then
            thisCol = columnHeaders(sourceSheet.Cells(thisRow, "C").Value)
        Else
            nextCol = nextCol + 1
            targetSheet.Cells(1, nextCol).Value = sourceSheet.Cells(thisRow, "C").Value
            columnHeaders.Add sourceSheet.Cells(thisRow, "C").Value, nextCol
            thisCol = nextCol
            targetSheet.Cells(nextRow, thisCol).Value = 0
        End If
        targetSheet.Cells(nextRow, thisCol).Value = targetSheet.Cells(nextRow, thisCol).Value + sourceSheet.Cells(thisRow, "D").Value
    End If
Next thisRow

targetSheet.Cells(nextRow + 1, "A").Value = "Total"
For thisCol = 2 To nextCol
    For thisRow = 2 To nextRow
        If targetSheet.Cells(thisRow, thisCol).Value = "" Then targetSheet.Cells(thisRow, thisCol).Value = 0
    Next thisRow
    targetSheet.Cells(nextRow + 1, thisCol).Value = Application.WorksheetFunction.Sum(targetSheet.Range(targetSheet.Cells(2, thisCol), targetSheet.Cells(nextRow, thisCol)))
Next thisCol

End Sub

WBD

 

[jrevans778]

Thank you!

I'm getting a type mismatch error on line:
targetSheet.Cells(nextRow, thisCol).Value = targetSheet.Cells(nextRow, thisCol).Value + sourceSheet.Cells(thisRow, "D").Value

Not exactly sure why, I'm sorry I'm not that great at VBA

 

[jrevans778]

I think I got it... thank you!

 

[wideboydixon]

Thank you!

I'm getting a type mismatch error on line:
targetSheet.Cells(nextRow, thisCol).Value = targetSheet.Cells(nextRow, thisCol).Value + sourceSheet.Cells(thisRow, "D").Value

Not exactly sure why, I'm sorry I'm not that great at VBA

There must be a non-numeric value somewhere ...

WBD