Aug 14 2024 02:08 AM - edited Aug 14 2024 02:09 AM
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?
Process would look like this
Step one : Insert new rows inbetween rows in column 6 : row 1 and 2, and row 2 and 3
Step 2 : drag value of cell 1 and 2 from column 11 in to the respective cell in column 6
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.
Aug 14 2024 04:59 AM
@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?)