how to take 3 cells vertically and make them 1 (or however many) horizontally?

nyioves,

Welcome to the Excel Forum.

What is in cells:
A1
A2
A3

A5
A6
A7

And, what should the new A1 look like?

And, what should the new A2 look like?


Even better:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

With the function indirect.

See the attached file.

Probably the function will get very slow on 120 K, while I used indirect.

nyioves,

If you have:
A1 = 1
A2 = 2
A3 = 3

A5 = 5
A6 = 6
A7 = 7

A9 = 9
A10 = 10
A11 = 11


After the below very fast macro using two arrays in memory you will have:

A1 = 1 2 3
A2 = 5 6 7
A3 = 9 10 11


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

nyioves,

If you have:
A1 = 1
A2 = 2
A3 = 3

A5 = 5
A6 = 6
A7 = 7

A9 = 9
A10 = 10
A11 = 11


After the below very fast macro using two arrays in memory you will have:

A1 = 1, B1 = 2, C1 = 3
A2 = 5, B2 = 6, C2 = 7
A3 = 9, B3 = 10, C3 = 11

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.

nyioves,

You are very welcome. Glad we could help.

you can just concat a1&a2&a3 in b1 then sort and delete all the unwanted stuff since the first cell always contains the word "item"

PS - this spreadsheet has over 120k lines all together

Not manually with 120k lines of raw data - very slooooooooooooooow.

In the future I would suggest that you post a workbook with before and after worksheets. This way, you will get a correct solution the first time.

nyioves,

The macro needs debuggin

I would think that the above presented macros all work with your original described raw data.


You need to post another workbook with the raw data in worksheet Sheet1, and, in worksheet Sheet2 the results you are looking for (manually formatted by you) for the three and four row groups.

Is there any chance that the groups will be larger than 3 or 4 rows?

yes there are some that are las\rger than 3 or 4. but is there anything that could be done about that? Also there is the headers that show up and break up the groups.

otherwise i just want each of the 3-4 line groups in one cell so i can text to columns them into fields. so check out sheet 2 it contains everything in one cell

nyioves,

This is the last try.

Please post another workbook with the following.

In Sheet1:

beginning in:

B1 put the results from A1:A3

B147 put the results from A147:A151

B157 put the results from A157:A160

B220 put the results from A220:A224

B240 put he results from A240:A244