Add cell from other column into a column by cell

Copper Contributor

Column 6 and Column 11, I want first cell in column 11 to be mapped inbetween of the 1st and 2nd cell in column 6, then second cell in column 11 to be mapped inbetween of the 2nd and 3rd cell in column 6, and so on. Is there any idea how to do this?

irfan1495_0-1723626011803.png


Process  would look like this 
Step one : Insert new rows inbetween rows in column 6 : row 1 and 2, and row 2 and 3

irfan1495_1-1723626258389.png

 

Step 2 : drag value of cell 1 and 2 from column 11 in to the respective cell in column 6 

irfan1495_2-1723626398017.png


This would be manual, but is there an automatic way by using formula on excel to address this issue?
Please help, thank you. 

Also, if you need the excel file do tell.

1 Reply

@irfan1495  yes the file would help.  also there are a few more questions like:

is this a 1 time thing or something you need to do often? (may affect strategy)

what version of excel do you have?   (i.e. can we use the new excel functions)

will this be using online excel or shared with others or others need to do this? (i.e. avoid macros)

if you just need a list of column 6 with column 11 interwoven and you have the new excel then 

=TOCOL(CHOOSECOLS( [table], 6, 11 ))

but if you need to have all rest of the table around it then it gets more tricky.  

First off, in-cell formula can NOT insert rows and manipulate other cells so using in-cell functions would be creating a new table (similar to the new column using the formula above).  You could then copy and special paste-values over the original data.  Another option includes macros (VBA) but those don't work in the online Excel and not good if you are sharing the workbook and need others to do it also.  Power Query is another strong option but again will output to a new table.

You should also mention where the data is coming from with respect to is it from another sheet, manually entered, downloaded and what is next (I'm guessing you want to be able to manually fill is those new rows?)