vba replacement for index match array

**eculver** · 11-04-2015, 05:27 PM

Hello all, I made a non vba spreadsheet that uses an array to organize some information the way I need it organized. It works as I intended, however because it is using multiple arrays, it is WAY too slow. I am trying to make a VBA version to do the same thing, but am not as well versed in VBA and cannot get anything to work correctly. I attached an example spreadsheet with the non VBA code I need to translate to VBA in cell D4. This code was originally in D4:D60 as well as I4:I60, which is when it started to process slowly. I have a pitiful attempt at the VBA that runs when I hit the "organize" button, but gives me an undefined error because I'm not sure how to translate this:

{=" " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($1:$1))),"") &  " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($1:$1))),"EMPTY") &  " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($2:$2))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($2:$2))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($3:$3))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($3:$3))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($4:$4))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($4:$4))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($5:$5))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($5:$5))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($6:$6))),"") & " " &IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($6:$6))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($7:$7))),"") & " " &IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($7:$7))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($8:$8))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($8:$8))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($9:$9))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($9:$9))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($10:$10))),"") & " " &IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($10:$10))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($11:$11))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($11:$11))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($12:$12))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($12:$12))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($13:$13))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($13:$13))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($14:$14))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($14:$14))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($15:$15))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($15:$15))),"") & " " & IFERROR(INDEX(IMPORTED!$E$2:$E$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($16:$16))),"") & " " & IFERROR(INDEX(IMPORTED!$C$2:$C$9999, SMALL(IF(B4=IMPORTED!$A$2:$AC$9999, ROW(IMPORTED!$Z$2:$Z$999)-ROW(IMPORTED!$Z$2)+1), ROW($16:$16))),"") & " "}

The purpose of this is to look for matches to the FCH column, and write them (divided by spaces) in the corresponding column from information on another sheet in the workbook, and there could be up to 32 matches. Any assistance is appreciated.