Excel Matrix to Access Database Table Help?

Microsoft Excel 2003 (full product)
October 15, 2010 at 00:38:39
Specs: Windows XP
Hi All,

I am currently looking for a way to translate an inspection matrix (where inspections are planned by week using a dropdown menu (per week)) like this:

Reference | Description | WK 1| WK2| WK3| etc..
1 | pump | PIP | PIC |
2 | valve | PIM | IEC |FAP |

etc..

to a table structure in another sheet where it would show up like:

Reference|Description|Kind of inspection|WK
1 | Pump |PIP |1
1 | Pump |PIC |2
2 |Valve |PIM |1

etc.

I understand that this makes use of VB in a LOOP function. I have some understanding about Visual Basics am not a complete newby but this is over my head.

Could someone please help me with this?

Thanks in Advance


See More: Excel Matrix to Access Database Table Help?

Report •

#1
October 15, 2010 at 08:21:13
Please repost your table after following these instructions. It will make it easier for us to read and understand the output you are looking for.

Thanks!

DerbyDad03
Office Forum Moderator

To line up your data in your post, please use the pre tags found above the Reply box.

1 - Click the pre icon found above the Reply box.
2 - Enter your data between the tags.
3 - Click Preview Follow Up to see if you like the way it looks.
4 - If you need to fix the layout, fix it in the Message box below the Preview box.
5 - Click the "Check To Show Confirmation Page Again" box.
6 - Click either Confirm button to Preview the post again.

Repeat steps 4 – 6 as often as necessary until you like the way the post looks and then click Confirm.


Report •

#2
October 17, 2010 at 23:07:06
Hi All,

I am currently looking for a way to translate an inspection matrix (where inspections are planned by week using a dropdown menu (per week)) like this:

Reference | Description | WK 1| WK2| WK3| etc..
1 | pump | PIP | PIC |
2 | valve | PIM | IEC |FAP |

etc..


to a table structure in another sheet where it would show up like:

Reference|Description|Kind of inspection|WK
1 | Pump |PIP |1
1 | Pump |PIC |2
2 |Valve |PIM |1 

etc.

I understand that this makes use of VB in a LOOP function. I have some understanding about Visual Basics am not a complete newby but this is over my head.

Could someone please help me with this?

Thanks in Advance


Report •

#3
October 18, 2010 at 09:33:14
You used the pre tags, but you didn't arrange your data any differently.

The advantage of the pre tags is that you can use spaces between your data to line it up.

I'm still having a hard time determining what belongs in what column.

If done correctly, the pre tags will allow you line to up your data as follows, which makes it easier for us to read.

Reference   Description   Kind of inspection  WK
    1        Pump              PIP             1
    1        Pump              PIC             2
    2        Valve             PIM             1 


Please try your original data table again. I think I know how it should be laid out, but I don't want to work on code based on an incorrect assumption.


Report •

Related Solutions

#4
October 18, 2010 at 23:47:54
Hi All, (Corrected the way the tables look)

I am currently looking for a way to translate an inspection matrix (where inspections are planned by week using a dropdown menu (per week)) like this:


Reference|Description|WK 1|WK2|WK3|

      1  |  pump     |PIP |PIC|IEC|

      2  | valve     |PIM |IEC|FAP|


to a table structure in another sheet where it would show up like:


Ref|Descrip|Kindofinspection|WK|

 1 |  Pump |      PIP       |1 |

 1 |  Pump |      PIC       |2 |

 2 | Valve |      PIM       |1 |

I understand that this makes use of VB in a LOOP function. I have some understanding about Visual Basics am not a complete newby but this is over my head.

Could someone please help me with this?

Thanks in Advance


Report •

#5
October 19, 2010 at 07:28:10
Try this code.

Note: Row 1 of Sheets 1 & 2 must have column headings as noted in your examples since the code copies and pastes beginning with Row 2.

Sub TransposeTable()
'Find last row with data in Sheet1
  lastDataRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
    For rw = 2 To lastDataRow
'Initialize Week Column variable
     wkColumn = 3
'Count number of weeks to use in For-Next loop
     wkCount = WorksheetFunction.CountA(Sheets(1).Rows(rw)) - 2
'Loop through each data set
       For copyRow = 1 To wkCount
'Determine next row on Sheet2 to paste data
         nxtPasteRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Reference Number and Discription to Sheet 2 Columns A & B
           Sheets(1).Range("A" & rw & ":B" & rw).Copy _
              Destination:=Sheets(2).Range("A" & nxtPasteRow)
'Copy Inspection Type to Column C
           Sheets(1).Cells(rw, wkColumn).Copy _
              Destination:=Sheets(2).Range("C" & nxtPasteRow)
'Place Week Number in Column D
           Sheets(2).Range("D" & nxtPasteRow) = copyRow
'Increment wkColumn varibale
             wkColumn = wkColumn + 1
'Loop
       Next
    Next
End Sub


Report •

#6
October 21, 2010 at 23:53:04
Hi DerbyDad03.

I managed to get things going with another type of code, below is the code/macro i'm using now. the only thing i can't get to work yet is to automatically update the table in Access.

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column < 16 Or ActiveCell.Column > 119 Then Exit Sub
If ActiveCell.Row < 10 Then Exit Sub
Teller = 2
Sheets("sheet1").Range("A2:E65536").ClearContents
For x = 10 To Range("C65536").End(xlUp).Row
For y = 16 To 119
If Cells(x, y).Value <> "" Then
Sheets("Sheet1").Cells(Teller, 1).Value = Cells(x, 1).Value
Sheets("Sheet1").Cells(Teller, 2).Value = Cells(x, 2).Value
Sheets("Sheet1").Cells(Teller, 3).Value = Cells(x, 3).Value
Sheets("Sheet1").Cells(Teller, 4).Value = Cells(x, 4).Value
Sheets("Sheet1").Cells(Teller, 5).Value = Cells(x, 5).Value
Sheets("Sheet1").Cells(Teller, 6).Value = Cells(x, 6).Value
Sheets("Sheet1").Cells(Teller, 7).Value = Cells(x, 7).Value
Sheets("Sheet1").Cells(Teller, 8).Value = Cells(x, 8).Value
Sheets("Sheet1").Cells(Teller, 9).Value = Cells(x, 9).Value
Sheets("Sheet1").Cells(Teller, 10).Value = Cells(x, 10).Value
Sheets("Sheet1").Cells(Teller, 11).Value = Cells(x, 11).Value
Sheets("Sheet1").Cells(Teller, 12).Value = Cells(x, 12).Value
Sheets("Sheet1").Cells(Teller, 13).Value = Cells(x, 13).Value
Sheets("Sheet1").Cells(Teller, 14).Value = Cells(x, 14).Value
Sheets("Sheet1").Cells(Teller, 15).Value = Cells(x, 15).Value
Sheets("Sheet1").Cells(Teller, 16).Value = Cells(x, y).Value
Sheets("Sheet1").Cells(Teller, 17).Value = y - 15
Teller = Teller + 1
End If
Next y
Next x
End Sub


Report •

#7
October 22, 2010 at 04:59:06
I don't know anything about Access so I can't offer any suggestions. You might want to try the Database forum at this site.

I added one more loop to your code to make a little easier to follow. You already used a number of For-Next loops in your code, so I'm not sure why you didn't use one in the middle of your code instead of repeating the .Value = .Value lines 15 times.

You should include comments in your code not only to make it easier for other to follow, but also so that when you go back and look at it next week, next month or even next year, you'll remember why you wrote what you wrote.

I see that you are using the Worksheet_Change event to trigger your code. That event uses Target as it's argument, so it's standard practice to refer to the Target, not the ActiveCell, in the body of the code.

e.g. If ActiveCell.Column < 16 should be If Target.Column < 16

Similar to posting data between the pre tags, you should post your code between the pre tags so that it retains the indents.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column < 16 Or Target.Column > 119 Then Exit Sub
  If Target.Row < 10 Then Exit Sub
   Teller = 2
   Sheets("sheet1").Range("A2:E65536").ClearContents
    For x = 10 To Range("C65536").End(xlUp).Row
     For y = 16 To 119
      If Cells(x, y).Value <> "" Then
       For colNum = 1 To 15
        Sheets("Sheet1").Cells(Teller, colNum).Value = Cells(x, colNum).Value
       Next
       Sheets("Sheet1").Cells(Teller, 16).Value = Cells(x, y).Value
       Sheets("Sheet1").Cells(Teller, 17).Value = y - 15
       Teller = Teller + 1
      End If
     Next y
    Next x
End Sub


Report •

Ask Question