the title of question sounds conundrum let me simplify it.
column a
item_1
sub_items
sub_item_1.1
sub_item_1.2
sub_item_1.3
item_2
sub_items
sub_item_2.1
sub_item_2.2
sub_item_2.3
what trying achieve means of formula or macro each cell below "sub_items" put item number above "sub_items" rest of cells below until reached new item number.
desired output
column column b
item_1 item_1
sub_items item_1
sub_item_1.1 item_1
sub_item_1.2 item_1
sub_item_1.3 item_1
item_2 item_2
sub_items item_2
sub_item_2.1 item_2
sub_item_2.2 item_2
sub_item_2.3 item_2
the values against item_1 , sub_items can blank, fine trying each item number against corresponding sub item. appreciated. lot.
try this:
sub test() dim itemtitle string dim itemno integer dim lastrow long dim cel range, looprange range dim ws worksheet set ws = activesheet itemtitle = "item_" 'your items have @ least amount, starts. dim currentitem string ws lastrow = .usedrange.rows.count set looprange = .range(.cells(1, 1), .cells(lastrow, 1)) each cel in looprange cel.select if left(cel.value, 5) = itemtitle currentitem = cel.value end if cel.offset(0, 1).value = currentitem next cel end end sub note: assuming items start "item_". if that's not so, change itemtitle definition whatever might be. should work amount of items, whether know how many or not.
edit: ok, since items aren't "item_1", have new code:
sub test_with_array() dim itemtitle string dim itemno integer dim lastrow long dim cel range, looprange range dim ws worksheet, itemnows worksheet dim itemnumbers() variant application.screenupdating = false application.calculation = xlmanual set ws = activesheet set itemnows = sheets("item numbers") 'change whatever sheet has unique ids 'now, let's find out how many unique item ids have. assuming they're listed in column without blank spaces. dim numberofids integer itemnows numberofids = evaluate(worksheetfunction.counta(.range(.cells(1, 1), .cells(.cells(1, 1).end(xldown).row, 1)))) debug.print "there " & numberofids & " unique ids." redim itemnumbers(1 numberofids) 'set array size fit number of unique ids itemnumbers() = .range(.cells(1, 1), .cells(.cells(1, 1).end(xldown).row, 1)) end dim integer = lbound(itemnumbers) ubound(itemnumbers) 'this checks make sure have ids debug.print itemnumbers(i, 1) next dim currentitem string ws.activate ws 'now, let's add item numbers respective entry lastrow = .usedrange.rows.count set looprange = .range(.cells(1, 1), .cells(lastrow, 1)) each cel in looprange cel.select = lbound(itemnumbers) ubound(itemnumbers) 'loop through array of item numbers, looking match debug.print "looking " & itemnumbers(i, 1) & " in string " & cel.value debug.print strcomp(cel.value, itemnumbers(i, 1)) if strcomp(cel.value, itemnumbers(i, 1)) = 0 currentitem = cel.value end if cel.offset(0, 1).value = currentitem next next cel end application.screenupdating = true application.calculation = xlautomatic end sub you mentioned list of unique ids. if put these in second worksheet in document, call "item numbers" (or else, sure change code reflect it), , list items in continuous list in column a, above should work. please let me know tweaks/errors have if any.
edit 2:
per note can use "sub_items" delimiter speak, try code below:
sub test() dim itemno integer dim lastrow long dim cel range, looprange range dim ws worksheet application.screenupdating = false application.calculation = xlcalculationmanual set ws = activesheet dim currentitem string ws lastrow = .usedrange.rows.count set looprange = .range(.cells(1, 1), .cells(lastrow, 1)) each cel in looprange cel.select if cel.value = "sub_items" currentitem = cel.offset(-1, 0).value cel.offset(-1, 1).value = currentitem end if cel.offset(0, 1).value = currentitem next cel end application.screenupdating = true application.calculation = xlcalculationautomatic end sub meta: i'm not sure on how editing/code addition okay, if can make post better best practices, let me know!
Comments
Post a Comment