+ Reply to Thread
Results 1 to 9 of 9

Auto-Creating Tables from raw data?

Hybrid View

  1. #1
    Registered User
    Join Date
    07-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    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?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Auto-Creating Tables from raw data?

    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?

  3. #3
    Registered User
    Join Date
    07-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Auto-Creating Tables from raw data?

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

  4. #4
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Auto-Creating Tables from raw data?

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

  5. #5
    Registered User
    Join Date
    07-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Auto-Creating Tables from raw data?

    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.

  6. #6
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Auto-Creating Tables from raw data?

    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!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Auto-Creating Tables from raw data?

    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?

  8. #8
    Registered User
    Join Date
    07-12-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Auto-Creating Tables from raw data?

    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??

  9. #9
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Auto-Creating Tables from raw data?

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1