Organize multiple rows and columns of data into a database format

[smd71092]

Hello all, I have some data in my Excel file that is formatted as in the picture here http://imgur.com/a/wqcc4 (the actual file is much larger but follows this exact format in the BEFORE pic including the empty row in between each of the "Items").

Is there any formula or macro that would be able to output this into a database friendly format (what is in the AFTER pic)? What I want is to have the customer name in the first column, the item name in the second column, whether or not the customer liked/disliked or had no preference to the item in the third column, how many of the item they bought in the fourth column and finally how many times the customer visited the store to buy Itemx.

I am familiar with the OFFSET function but couldn't think of a way for it to work here so it seems like this would have to be a macro. However, if at all possible I would prefer a formula no matter how complex it may be. Any help at all would be appreciated!

 

[smd71092]

This is a duplicate thread but please do not delete this version of it, this is clearer as I have been able to include pictures of the data.

 

[ask2tsp]

However, if at all possible I would prefer a formula no matter how complex it may be

Why is this so important?

A macro to do this would be fairly straighforward. I doubt if it is possible with formula's.

 

[Aladin Akyurek]

This is a duplicate thread but please do not delete this version of it, this is clearer as I have been able to include pictures of the data.

But that requires retyping everything from your imgur into Excel. Is that what "helping" should mean?

 

[smd71092]

I am just scared of macros lol I don't know how to edit them as easily as I can with formulas where I can think through what the formula is doing. But yes at this point I would love a macro to do it if possible.

 

[smd71092]

But that requires retyping everything from your imgur into Excel. Is that what "helping" should mean?

Hmm ok so I guess it would be better to have both that imgur link and have the data written out in table format here? Sorry I tried uploading a sanitized Excel file and it didn't let me but I thought visualizing it would help. I didn't even think about people having to type all that information back into Excel to work with it.

 

[ask2tsp]

start Visual Basic Environment
insert->module
paste this code in the screen that pops up

Sub convert()
    Dim shtIn   As Worksheet
    Dim shtOut  As Worksheet
    Dim inRow   As Long
    Dim inCol   As Long
    Dim outRow  As Long
    
    Dim customer    As String
    Dim itemCode    As String
    Dim preference  As String
    Dim amount      As Long
    Dim visits      As Long
    
    Set shtIn = ThisWorkbook.Worksheets("Sheet1")
    
    Set shtOut = ThisWorkbook.Worksheets("dBase")
    shtOut.Range("A2:E" & shtOut.Range("A2").End(xlDown).Row).ClearContents
    outRow = 2
    
    inCol = 1
    customer = shtIn.Cells(1, inCol + 1)

    While customer > ""
        inRow = 2
        itemCode = shtIn.Cells(inRow, 1)
        
        While itemCode > ""
            preference = shtIn.Cells(inRow, inCol + 1)
            If preference > "" Then
                amount = shtIn.Cells(inRow + 2, inCol + 1)
                visits = shtIn.Cells(inRow + 1, inCol + 1)
                
                shtOut.Cells(outRow, 1) = customer
                shtOut.Cells(outRow, 2) = itemCode
                shtOut.Cells(outRow, 3) = preference
                shtOut.Cells(outRow, 4) = amount
                shtOut.Cells(outRow, 5) = visits
                outRow = outRow + 1
            End If
            
            inRow = inRow + 4
            itemCode = shtIn.Cells(inRow, 1)
        Wend
        inCol = inCol + 2
        customer = shtIn.Cells(1, inCol + 1)
    Wend
End Sub

 

[smd71092]

start Visual Basic Environment
insert->module
paste this code in the screen that pops up

Sub convert()
    Dim shtIn   As Worksheet
    Dim shtOut  As Worksheet
    Dim inRow   As Long
    Dim inCol   As Long
    Dim outRow  As Long
    
    Dim customer    As String
    Dim itemCode    As String
    Dim preference  As String
    Dim amount      As Long
    Dim visits      As Long
    
    Set shtIn = ThisWorkbook.Worksheets("Sheet1")
    
    Set shtOut = ThisWorkbook.Worksheets("dBase")
    shtOut.Range("A2:E" & shtOut.Range("A2").End(xlDown).Row).ClearContents
    outRow = 2
    
    inCol = 1
    customer = shtIn.Cells(1, inCol + 1)

    While customer > ""
        inRow = 2
        itemCode = shtIn.Cells(inRow, 1)
        
        While itemCode > ""
            preference = shtIn.Cells(inRow, inCol + 1)
            If preference > "" Then
                amount = shtIn.Cells(inRow + 2, inCol + 1)
                visits = shtIn.Cells(inRow + 1, inCol + 1)
                
                shtOut.Cells(outRow, 1) = customer
                shtOut.Cells(outRow, 2) = itemCode
                shtOut.Cells(outRow, 3) = preference
                shtOut.Cells(outRow, 4) = amount
                shtOut.Cells(outRow, 5) = visits
                outRow = outRow + 1
            End If
            
            inRow = inRow + 4
            itemCode = shtIn.Cells(inRow, 1)
        Wend
        inCol = inCol + 2
        customer = shtIn.Cells(1, inCol + 1)
    Wend
End Sub

Hmm does my cursor have to select a specific cell or setup a certain way? I have a type mismatch error when I run this.

 

[ask2tsp]

Sorry, I forgot to mention a few remarks.

You have to edit the line

Set shtIn = ThisWorkbook.Worksheets("Sheet1")

change 'Sheet1' to the name of your 'Before' sheet

likewise in the next line

Set shtOut = ThisWorkbook.Worksheets("dBase")

change 'dBase' to the name of your 'After' sheet.

Further assumption:
The 'Before' sheet layout starts in cel A1. So in the example B1=Customer1, A2=item1,etc.
If that is not the case, tell me and I can adjust the code.

I have a type mismatch error when I run this.

Which line is highlited when this occurs?

[/QUOTE]