Transfering multiples cells of values into one cell

[caffeine_overdue]

Hi all,

I'm trying to solve a simple excel problem but am yet to find a solution.

I regularly perform a task that requires me to input many numerical variables into an excel spreadsheet. The data pertaining to the variables displayed in a different program and up until now I've manually entered the data into excel.
Unfortunately I am unable to alter the desired layout of the variables within excel, and this has presented a problem when I've tried to automate the process of data entry.

I can paste the variables into excel from their source program, but the variable order must be re-arranged. I'm comfortable to automate this process within excel, but the key issue is that I'm required to input up to 5 variables within one cell (this is shown in the attached excel document).

I've tried many different techniques over the past weeks but simply cannot find a way to automate this transfer of multiples cells of variables into one cell.

Anyone have any ideas?

Thank you for your help

First time poster, I apologies if my description of the issues is not clear! In the attached excel spreadsheet I've tried to clearly display the arrangement of the variables when they are posted into excel from the source program, and the desired output.

Any questions please do not hesitate to ask!

[Pete_UK]

Assuming you have pasted the data into columns B, C and D, as in your example, you can first of all put this string of letters in cell F1:

CEJGBSRAUDILKQFHNMPOT

This represents the ordering of the letters that you show in your example output. You can then put this formula in cell F5:

=IFERROR(FIND(B5,$F$1),"")

and copy this down to F25, which will generate the numerical ordering for each item in column B.

Then you can use this formula in cell H2:

=B$3&CHAR(10)&INDEX(B:B,MATCH(1,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(2,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(3,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(4,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(5,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(6,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(7,$F:$F,0))

(Don't try to type it all in - just copy it from here, and then paste it into the cell). Ensure that the cell is formatted to Wrap Text. Then you can copy that formula into I2 and J2.

Then you can use this formula in K2:

=B$3&CHAR(10)&INDEX(B:B,MATCH(8,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(9,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(10,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(11,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(12,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(13,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(14,$F:$F,0))

This is a very similar formula, it is just the numbers immediately after each MATCH term has been increased by 7. This formula can be copied into L2 and M2.

Then you can put this formula in cell N2:

=B$3&CHAR(10)&INDEX(B:B,MATCH(15,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(16,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(17,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(18,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(19,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(20,$F:$F,0))&CHAR(10)&INDEX(B:B,MATCH(21,$F:$F,0))

and copy this into O2:P2 to complete your table.

I've shown this in the attached file, where I have retained your original examples in column R onwards for comparison, but you can delete these if you are happy that the correct output is being generated.

In future you can just copy new data into columns B:D from B3 onwards, and a new table of output data will be generated automatically.

Hope this helps.

Pete

[caffeine_overdue]

Hi Pete,

This is amazing, thank you so much for taking the time to explain how this can be done!

Greatly appreciated

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete