i have create output xls file based on input xls file header. giving below exact requirement. hope clear. if not please let me know.
iput xls --> id version namelegacy projectnumber ownername language keywords ownersite external content relevance periodic coremap validto 1 1 mohan 1000 x eng abcd ab no ok no 5 2017-10-14 2018-10-14 2 2 shayam 1001 y eng efgh bc no ok yes 2 2017-10-14 2018-10-14 3 1 sourabh 1002 z eng ijkl cd yes no no 4 2017-10-14 2018-10-14 based on input xls file, need output xls file below. each row of input file have 12 row of output file , respective value filled in output file input file.
id version ibaname stringvalue integervalue floatvalue floatvaluewithunits boolvalue timevalue urlvalue referencevalue 1 1 namelegacy mohan 1 1 projectnumber 1000 1 1 ownername x 1 1 language eng 1 1 keywords abcd 1 1 ownersite ab 1 1 external no 1 1 content ok 1 1 relevance no 1 1 periodic 5 1 1 coremap 2017-10-14 1 1 validto 2018-10-14 2 2 namelegacy shayam 2 2 projectnumber 1001 2 2 ownername y 2 2 language eng 2 2 keywords efgh 2 2 ownersite bc 2 2 external no 2 2 content ok 2 2 relevance yes 2 2 periodic 2 2 2 coremap 2017-10-14 2 2 validto 2018-10-14 3 1 namelegacy sourabh 3 1 projectnumber 1002 3 1 ownername z 3 1 language eng 3 1 keywords ijkl 3 1 ownersite cd 3 1 external yes 3 1 content no 3 1 relevance no 3 1 periodic 4 3 1 coremap 2017-10-14 3 1 validto 2018-10-14 thanks in advance.
there few steps using vba. main purpose identify trying do.
it seems attempting sort through records identify format of information , place in column designated format. this, have created simple custom function..
this function takes in name of header string, , uses select/case find appropriate matches column target sheet needs. custom specific layout. feel free adapt or change it.
function find correct column on target
function colfinder(header string) long 'target column format = column # '"stringvalue" = 4 '"integervalue" = 5 '"floatvalue" = 6 '"floatvaluewithunits"= 7 '"boolvalue" = 8 '"timevalue" = 9 '"urlvalue" = 10 '"referencevalue" = 11 ' **** set values cases match desired format above **** 'default column 4 stringvalue if unknown select case header case "namelegacy" colfinder = 4 case "projectnumber" colfinder = 5 case "ownername" colfinder = 4 case "language" colfinder = 4 case "keywords" colfinder = 4 case "ownersite" colfinder = 10 case "external" colfinder = 8 case "content" colfinder = 11 case "relevance" colfinder = 8 case "periodic" colfinder = 5 case "coremap" colfinder = 9 case "validto" colfinder = 9 end select end function using few other functions finding last row & last column
last row function
function lastrow(sheet string) long lastrow = sheets(sheet).cells(rows.count, "a").end(xlup).row 'using cells() end function last column function
function lastcol(sheet string) long lastcol = sheets(sheet).cells(1, columns.count).end(xltoleft).column end function with these 3 functions, can run simple loop through source sheet, 1 row @ time, see column data coming from, , place on appropriate target.
this example using 2 worksheets in same workbook. change, extend address include other workbooks or sheets. simplest way show concept.
code loop through sheets
sub colstorows() dim trow long, scol long, srow long dim source string, target string dim tstring string source = "sheet1" target = "sheet2" 'set target row starting point trow = 2 'loop through source rows data, starting after header row srow = 2 lastrow(source) 'loop through each header in source row scol = 3 lastcol(source) 'set basic columns on target sheets(target).cells(trow, 1) = sheets(source).cells(srow, 1) sheets(target).cells(trow, 2) = sheets(source).cells(srow, 2) 'get header name temp string find format requires tstring = sheets(source).cells(1, scol) 'label column 3 of target sheets(target).cells(trow, 3) = sheets(source).cells(1, scol) 'find appropriate column using custom formula [colfinder(header string) long] sheets(target).cells(trow, colfinder(tstring)) = sheets(source).cells(srow, scol) 'advance next target row trow = trow + 1 next scol next srow end sub source sheet example 
target sheet example 
Comments
Post a Comment