Manipulating a spreadsheet

  • Thread starter Thread starter Tangier
  • Start date Start date
T

Tangier

Hi,

I have a challenge...

I have to take an existing spreadsheet, that looks something like
this:

Column A Column B Column C Column D
Employee Code1 Code2 Code3
John Doe EEW HGF NBD

The assumptions are that there is 1 row per employee. Each employee
may have several codes associated with him or her. In this case, there
are 3 codes associated with John Doe.

My task is to convert the spreadsheet so that the report will create a
new row for each code, per employee, and list the code's dimensions.
Each code has a dimension set, that can be stored in another
spreadsheet. So the spreadsheet will contain:

Code | Dimensions
EEW 1 2 3 4 ...

So for example, code EEW stands for

1 2 3 4 (each number is a separate dimension).

It would be nice if each number could be in a separate column. So the
final report should look like this:

John Doe 1 2 3 4
John Doe (whatever the dimension set is for HGF)
John Doe (whatever the dimension is for NBD)

Is this possible with Excel?
 
Logics only, not actual code:

dim wse as worksheet
dim wst as worksheet
set wse=sheets("employee")
set wst=sheets("code_table")

dim wsrpt as worksheet
set wsrpt=sheets("report")

wse.select
set rng=range(cells(2,"A"), cells(2,"A").end(xldown))
for each c in rng
nr_of_col=cells(c.row, columns.count).end(xltoleft).column-1
wsrpt.cells(rows.count,"A").end(xlup).offset(1,0).resize(nr_of_col,1)=c
cells(c.row,"B").resize(1,nr_of_col).copy
wsrpt.cells(rows.count,"B").end(xlup).offset(1,0).pastespecial (xltranspose)
next c

wsrpt.select
lrow=cells(rows.count,"B").end(xlup).row
set rng2=range("B2:B" & lrow)
for each c in rng2
c.offset(0,1)=application.worksheetfunction.vlookup(c, wst.table_range, 2)
v=split(c.offset(0,1)
for i=0 to ubound(v)
c.offset(0,2+i)=v(i)
next i
next c

columns("B:C").delete
 
Back
Top