Share via

Transpose a large data set

Anonymous
2022-05-04T12:25:25+00:00

Greetings,

I have a large dataset, oriented in rows as shown below. The data extends for thousands of columns. A sample set is shown below.

Bin C62 A33 Q44
Shelf 3-a 1-d 5-r
Section 4 6 6

I need to transpose this data so it is a columnar orientation.

Bin Shelf Section
C62 3-a 4
A33 1-d 6
Q44 5-r 6

The column header Bin is in cell C3.

Is there a method to do this. This will be a job done monthly and the extent of the data can vary from month to month. Manual methods will take a great deal of time.

I thank you in advance for any suggestions.

Paul

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-05-04T12:35:32+00:00

    It's not much work manually:

    • Select the top left cell.
    • Press Ctrl+* (Ctrl+Shift+8) to select the dataset.
    • Copy it (Ctrl+C).
    • Select the top left cell of the destination range (on the same sheet or on another sheet).
    • Click the lower half of the Paste button on the Home tab of the ribbon and click Transpose. Keyboard shortcut: Alt+H, V, T.

    Remark: the original range should not contain more than 16384 rows, else the transposed range won't fit within the 16384 columns of a worksheet.

    9 people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-05-04T18:53:40+00:00

    I don't understand your comment. If I start with this:

    and perform the steps I described (which takes about 2 seconds), I get this result:

    I thought that was what you wanted, but apparently I'm mistaken.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2022-05-04T15:27:37+00:00

    Thank you for your response but that is not providing the configuration I need. Your solution is taking the 3 ROW headers and making then a repeating set of 3 header in one row and one data point in the row below.

    As I have shown, I need three columns, with the current row headers becoming three column headers and then the data below, extending for down several thousand rows. The final product should look like this:

    Bin Shelf Section
    C62 3-a 4
    A33 1-d 6
    Q44 5-r 6
    0 comments