excel - How to convert a table to multiple columns -

i have tons of spreadsheets formatted first table (but lot more records , different number of records). need make take right information format second table importing access. can done? thanks.

..........part 1 part 2 part 3 part 4
test 1..5
test 2.............x.........5
test 3..2.........x..................x
test 4.......................x.........x
test 5..x..............................2

test 1 part 1 5
test 2 part 2 x
test 2 part 3 5
test 3 part 1 2
test 3 part 2 x
test 3 part 4 x
test 4 part 3 x
test 4 part 4 x
test 5 part 1 x
test 5 part 4 2

here's 1 way it. use each loops ranges.

dim workingrange1 range, workingrange2 range  set workingrange1 = sheets("sheet1").usedrange set workingrange2 = range("putstuffhere")   = 0 workingrange1.rows.count - 2   j = 0 workingrange1.columns.count - 2     if not isempty(workingrange1.cells(i+1, j+1))       workingrange2.offset(0, 0) = workingrange1.cells(i+1, 1)       workingrange2.offset(0, 1) = workingrange1.cells(1, j+1)       workingrange2.offset(0, 2) = workingrange1.cells(i+1, j+1)     end if     set workingrange2 = workingrange2.offset(1,0)   next j next