Auto-Creating Tables from raw data?

Hi there,

I'm incredibly lazy and have a set of data on users interactions with various materials. What I want to do, if possible, is have a data set pasted into the spreadsheet, which then populates different tables. See the attached file to make things clearer.

The tables would have a list of names in them, as well as the figure for that data set (gold, iron etc) HOWEVER: I want excel to NOT put in names into a particular table if that particular name has 0 interactions with the material. So I cant use a vlookup, can anyone advise on how to do this?

Do you want the data on the Diamond Data worksheet cleared everytime you insert new Temp_Data or add the new data to the existing data?

Yes its new data on the nice looking sheet every time i paste data into the other sheet.

Sorry LOL so overwrite the existing data on the nice looking sheet?

Yes ideally it would over write it, but i can delete the data on the nice looking sheet each time i do the report if required.

Alrighty,

I've attached your original workbook. It now includes two macros that are dependent upon eachother.
The first is the FormatReport macro which runs the ResetReport macro and then populates the Materials Report. The other is the Reset Report macro which clears the existing report on the first worksheet in the workbook.

To run the code, make sure to enable macros when opening the workbook. Then select the Populate Report button at the top left of the Diamond Data worksheet.

To view the code Press Alt+F8 and then select the FormatReport macro, select the Step Into option.
Visual Basic will open and show you the coding for both macros. Anything that appears in green is a comment meant to help you understand the code so that you may alter it to fit your workbook.

Let me know if you have any questions!

Thanks!

Never Mind i have figured that out now (just needed to restart after changing security options). I have a few questions, though. It seems that, wonderful as this is, it destroys the % formulas on the "diamond data" page, is there any way to prevent this? Also, theres a lot of hidden rows on the "temp_data" page that I cant unhide?

HI there,

Thanks for the reply, Macro is one area where my knowledge really is lacking. I am trying to get this to work but cannot figure out how to run it. I've changed my macro secureity, but when i click the button it just makes "resize pegs" appear at the corners, as if i was editing the actual button. Doubleclicking opens visual basic script, and righ-clicking on the button doesnt make any "run macro" open appear. Please advise??

Hello there,

Also, theres a lot of hidden rows on the "temp_data" page that I cant unhide?

Add the following line of code:

Sheets(2).UsedRange.AutoFilter

Before the end sub and last line of code, so that the entire code look like so:

Sub FormatReport()
'declare variables
Dim x As Long, y As Long, z As Long, a As Long, b As Long, c As Range, col As Long, rw As Long
Dim t As Long, u As Long, v As Long, LR As String, i As Long, rng As Range

ResetReport 'run the reset report macro that clears the current report

a = 6   'set a to row 6 as this is the empty row below the first three categories
b = 9   'set b to row 9 as this is the empty row below the second categories

With Sheets(2)  'with the second worksheet in the workbook
    .Select 'select the workbook
    LR = .Range("A6555").End(xlUp).Row  'set LR equal to the last row in column A that contains a value
                
    'set the below variables to the number of cells in each
    'defined columns whose value was greater than 0
    x = WorksheetFunction.CountIf(.Range("B1:B" & LR), ">0")
    y = WorksheetFunction.CountIf(.Range("c1:c" & LR), ">0")
    z = WorksheetFunction.CountIf(.Range("d1:d" & LR), ">0")
    t = WorksheetFunction.CountIf(.Range("e1:e" & LR), ">0")
    u = WorksheetFunction.CountIf(.Range("f1:f" & LR), ">0")
    v = WorksheetFunction.CountIf(.Range("g1:g" & LR), ">0")
        
    'the below two lines variables will be used to know how many rows to insert below the first
    'and second line of categories
    x = WorksheetFunction.Max(x, y, z)  'reset x to the larger value of x, y, z
    y = WorksheetFunction.Max(t, u, v)  'reset y to the larger value of t, u , v
    
    With Sheets(1)  'with the first worksheet in the workbook
        .Rows("6:6").Copy   'copy row 6
        .Rows("6:" & x + 6).Insert shift:=xlDown    'insert the number of rows defined by x above
            b = b + x + 1   'reset b (set originally as row 9) to the new row where the second categories start
        .Rows(b & ":" & b).Copy 'copy row b
        .Rows(b & ":" & b + t).Insert shift:=xlDown 'insert the number of rows defined by t above
    End With    'end with the first worksheet in the workbook
    
    For i = 2 To 7  'loop through numbers 2 through 7, i will represent the current number in the loop
                    '2 through 7 represent the column number 2 being B and 7 being G
            
        With .UsedRange 'with the used range in the second worksheet in the workbook
            .AutoFilter 'autofilter the range
            .AutoFilter Field:=i, Criteria1:="<>0"  'filter the current column number (i) in the loop
                                                    'to all values that are not equal to 0
        End With    'end with the usedrange
        
        For Each c In .Range("A2:A" & LR).Cells 'loop through cells A2 to A and the last Row
            If c.RowHeight > 0 Then 'if the row is not hidden then
                If rng Is Nothing Then  'if no range have been assigned to the variable rng then
                    Set rng = c 'define rng as cell c
                Else: Set rng = Union(rng, c)   'if is has been defined then add c to the existing range
                
                End If
            End If
        Next c  'move to next cell in the loop
            rng.Select  'select the rng (defined range)
                Selection.Copy  'copy rng
                
                    'the below statement will tell where to paste the values copied
                    'based on the current column (i) in the i loop
                    Select Case i   'if the current i in the loop is...
                        Case 2  '2 (column B) Diamond then
                            col = 1 'set col equal to 1 (column A in the Diamond Date worksheet)
                            rw = 6  'set rw to 6 then row to paste to in the Diamond Data worksheet
                        Case 3
                            col = 5
                            rw = 6
                        Case 4
                            col = 9
                            rw = 6
                        Case 5
                            col = 1
                            rw = b
                        Case 6
                            col = 5
                            rw = b
                        Case 7
                            col = 9
                            rw = b
                        End Select
                    
                    'paste the copied values into the first worksheet in the workbook
                    'into cell rw (defined above as a row number) and column (col defined above)
                    Sheets(1).Cells(rw, col).PasteSpecial xlPasteValues
                        
            Set rng = Nothing   'clear the rng's assigned to rng
            
        'loop through cell's from row 2 to the last row in the current column (i) in the i loop
        For Each c In .Range(.Cells(2, i), .Cells(LR, i)).Cells
            If c.RowHeight > 0 Then 'if the row is not hiddent then
                If rng Is Nothing Then  'if no range have been assigned to the variable rng then
                    Set rng = c     'define rng as cell c
                Else: Set rng = Union(rng, c) 'if is has been defined then add c to the existing range
                
                End If
            End If
        Next c  'move to next cell in the new c loop
            rng.Select  'select the rng (defined range)
                Selection.Copy  'copy rng
                    
                    'paste the copied values into the first worksheet in the workbook
                    'into cell rw (defined above as a row number) and one over from the col(column defined above)
                    Sheets(1).Cells(rw, col + 1).PasteSpecial xlPasteValues
            Set rng = Nothing   'clear the rng's assigned to rng
    Next i  'move to next column (i) in the i loop
            
End With

Application.CutCopyMode = False 'undo the copy mode

Sheets(2).UsedRange.AutoFilter

Sheets(1).Select    'select the first worksheet in the workbook (in this case Diamond Data)

End Sub

As for the formula what was the formula in the percentage column supposed to be?

Thanks!