excel - Return headers if column contains certain string -


i have sheet called input. top row, a1:o1 contains parent, , rows underneath (of varying length) contains urls. of urls shared between parents, , want return list of urls, , parents are. have tried concatenate(if(index(match formula becomes large. similar questions i've seen looking 1 output, number. open vba solutions, have very minimal understanding create own code.

example:
news --- celebrity ---- finance
cnn------complex --------forbes
forbes---cnn

i want return cnn news celebrity, forbes new finance, complex celebrity. don't mind how output formatted.

since have data in a:o, assuming column q blank. in column q, make list of unique values (so in example, q1 "cnn", q2 "complex" , q3 "forbes". can use "remove duplicates" list of unique urls). code loop through used range column o (from row 2 last used row) , put "answer" in column r.

sub test() dim headerrange range, uniquename string, integer, totalnames integer, lastheadcol integer, lastrow integer, cel range dim replacestring string  lastrow = usedrange.rows.count ' find last used row lastheadcol = cells(1, 1).end(xltoright).column 'find last column totalnames = cells(1, 17).end(xldown).row 'find out how many unique names there = 1 totalnames     uniquename = cells(i, 17).value 'get unique name check in each column     replacestring = uniquename  'start off "answer" unique name     each cel in range(cells(2, 1), cells(lastrow, lastheadcol)) ' each cell in range, starting @ a2         if cel.value = uniquename 'if cell's value unique name             replacestring = replacestring & " " & cells(1, cel.column).value 'add name string             cells(i, 17).offset(0, 1).value = replacestring ' update "answer"         end if     next cel  next  end sub 

there may issues - such let's block of urls ends @ row 90, have unrelated data in row 99, it's going set range go down 99 - if case, can change "lastrow"

lastrow = cells(1,1).end(xldown).row 

does help?

edit: if, in future, have code ends in column other o, can replace "17"s in code above "lastheadcol + 2". vba last used column header (say column e, 5th column) , add urls , such in column g (7th column, aka 5 + 2). technically better way code, since relies less on "hard-coded" (aka "magic numbers").


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -