Organize multiple rows and columns of data into a database format

smd71092

New Member
Joined
Jul 1, 2015
Messages
16
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!
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

smd71092

New Member
Joined
Jul 1, 2015
Messages
16
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

Well-known Member
Joined
Feb 18, 2015
Messages
506
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Jul 1, 2015
Messages
16

ADVERTISEMENT

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

New Member
Joined
Jul 1, 2015
Messages
16
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

Well-known Member
Joined
Feb 18, 2015
Messages
506
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

start Visual Basic Environment
insert->module
paste this code in the screen that pops up
Code:
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

New Member
Joined
Jul 1, 2015
Messages
16
start Visual Basic Environment
insert->module
paste this code in the screen that pops up
Code:
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

Well-known Member
Joined
Feb 18, 2015
Messages
506
Office Version
  1. 365
Platform
  1. Windows
Sorry, I forgot to mention a few remarks.

You have to edit the line
Code:
Set shtIn = ThisWorkbook.Worksheets("Sheet1")
change 'Sheet1' to the name of your 'Before' sheet

likewise in the next line
Code:
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,129,357
Messages
5,635,797
Members
416,882
Latest member
ericvrealty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top