VBA Code to Combine Six Columns Into Two on New Worksheet

[khutchins]

I am attempting to consolidate all data from columns K,M,O (excluding the header row) from sheet "Cost Center Prep" into Column A on "Allocation" Sheet within the same workbook.
Then consolidate all data from columns L,N,P (excluding the header row) from sheet "Cost Center Prep" into Column B on "Allocation" Sheet within the same workbook.

Has anyone successfully coded anything like this before? Any ideas would be most appreciated. I have scoured the forums, but have not been able to find anything that works!!!

 

[SirJB7]

Hi, khutchins!

If you define a dynamic named range as follows:
DataTable: =DESREF('Cost Center Prep'!$K$2;;;CONTARA('Cost Center Prep'!$L:$L)-1;6) ----> in english: =OFFSET('Cost Center Prep'!$K$2,,,COUNTA('Cost Center Prep'!$L:$L)-1,6)
then you can use this only formuila in A2 of target worksheet:
A2: =INDICE(DataTable;RESIDUO(FILA()-1+FILAS(DataTable)-1;FILAS(DataTable))+1;(ENTERO((FILA()-1+FILAS(DataTable)-1)/FILAS(DataTable))-1)*2+COLUMNA()) ----> in english: =INDEX(DataTable,MOD(ROW()-1+ROWS(DataTable)-1,ROWS(DataTable))+1,(INT((ROW()-1+ROWS(DataTable)-1)/ROWS(DataTable))-1)*2+COLUMN())

Then copy across thru coumn B and down as required, in this example thru row 22, in general until the 1st #REF error appears.

Just advise if any issue.

Regards!