search a value and copy corresponding column and paste into row alternately

[ravitej.141]

Dear Experts

I want search a in the column a and paste the corresponding values in the next colum b and paste in a row alternately. please find the image above and help me.

thanks in advance.
6.png
Regards
Ravi

[mrice]

Try this

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

[canapone]

Hi, a formula approach using array INDEX

in E2 to be confirmed with control+shift+enter before to be copied across

=IFERROR(INDEX($B$2:$B$1000,SMALL(IF($A$2:$A$1000=$D2,ROW($A$2:$A$1000)-1),COLUMN(B$1)/(2*MOD(COLUMN(A$1),2)))),"")

Regards

[newdoverman]

Your image is not accessible. It would be better to upload an Excel workbook that demonstrates the problem.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic