Content of multiple lines, into one line.

[koenus]

Testfile.xlsx

Hi guys,

As you can see in the file, there are multiple models on the left. And multiple models above.
I want to copy the content of G2:R8 (so, all the o) to one line.

Can i do that with a formula, because i have 8000 option codes?

kind regards, and thansk in advance for all the help.
Koen

[zbor]

Where do you want your solution?

[arlu1201]

Can you please upload a sample output file showing how you want the output to be displayed?

[koenus]

Testfile incl solution.xlsx

Hi guys,

thanks for the quick respons. i uploaded a file, wich contains the solution like i had in mind.

regards,

[arlu1201]

There are multiple model nos in column A in your original data. Should all the O's in those rows be combined into 1 row?

What should the macro check for? Whether the entries in column C match from row to row and if yes, to combine the values in columns G to R?

[koenus]

Hi Arlu1201,

it's a bit hard to explain, but i'll give it a try.

I want all the O's from every model, with the same optioncode (column B) combined in one row.
As you can see in 'Solution, first step', All the O's of G3:R9 are combined in row 13. (because they all have the same option code.)
After that i simply can delete the empty rows.

Note that all the o's are allreay on the good spot (matching the model of column A with the models on the first row (in the file row 2).

Hopefully this explanation does the trick .

thanks for the help.
regards,

[arlu1201]

I think your explanation did do the trick.

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[koenus]

Wooow arlu1201,

Your code did the trick
I applied it on the large file (+8000 rows) and it combined all the right elements.
Thank you very much.

You really made my day!

regards,
koen

[arlu1201]

Am glad it worked.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[arlu1201]

Am glad it worked.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save