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
Comments
Post a Comment