Look up a string in a column and get all the corresponding values in another column in MS Excel 2010 -

question -

column 1  column 2 ------------------          ab b          pr c          ef          gh          jk c          xy          uv d          yz 

solution required

query  column 2 column 3 column 4 column 5           ab       gh       jk    uv   b         pr   c         ef       xy   d         yz 

i need excel formulas, no vba or programming.

tried index & match formulas gives me values 1 cell in adjacent column need references mentioned in table.

need in different sheet summary.

[this bill of material (bom) table]

total 48000 rows , 15 columns there in table,while 15000 rows unique.we need 2 columns out of 15 summary purpose.some cells referred 20 times or more need these values populated in different columns.

the sheet try data linked source file using ms query.

ps : apologies providing no information in original query.

this code create new sheet called rowstocolumns summerised data on it.

sub postrowstocolumns() dim x long, y long, myarr variant activesheet.copy after:=sheets(worksheets.count) activesheet.name = "rowstocolumns" range("a2:b" & range("a" & rows.count).end(xlup).row).sort range("a1") y = 2 x = 2 range("a" & rows.count).end(xlup).row     if range("a" & x).text <> range("a" & x).offset(-1, 0).text         myarr = application.transpose(range("b" & x).resize(worksheetfunction.countif(range("a2:a" & range("a" & rows.count).end(xlup).row), range("a" & x)), 1))         range("d" & y) = range("a" & x).text         if range("b" & x).resize(worksheetfunction.countif(range("a2:a" & range("a" & rows.count).end(xlup).row), range("a" & x)), 1).rows.count > 1             range("e" & y).resize(1, ubound(myarr)) = myarr         else             range("e" & y) = myarr         end if         y = y + 1     end if next columns("a:c").delete shift:=xltoleft end sub