+ Reply to Thread
Results 1 to 2 of 2

How to merge excel columns into multiple rows

  1. #1
    Registered User
    Join Date
    05-05-2018
    Location
    US
    MS-Off Ver
    2013
    Posts
    1

    How to merge excel columns into multiple rows

    I have two sheets in Excel with the following content:

    Sheet1

    Please Login or Register  to view this content.
    Sheet2

    Please Login or Register  to view this content.
    Now, I want to merge these two sheets into Sheet3 as the following:

    Sheet3

    Please Login or Register  to view this content.
    These numbers are just examples here. The real data is too big, with nearly 2000 rows. So I cannot copy and paste them manually. Besides, the data are dynamically changed from the web, so I need some formula to re-arrange them automatically every time.

    Which functions or formulas can I use to re-arrange these data as above ?

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to merge excel columns into multiple rows

    Hi,

    One process is

    1. Put the column labels: Product, Value Parameter, in A1:C1 on sheet3
    2. Copy column A:B values from sheet 1 to A2 and in C2 enter 'Quantity' in C2 and copy down
    3. Do the same as step 2 with data from sheet 2 copying it into the next available cell in column A

    Then sort the data with column A as the key.

    To automate it record a macro to do that then upload again so that we can tidy up and generalise the recorded macro so that it will work whatever the amount of data on sheets 1 & 2
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Remove duplicate rows in Excel, merge data in other columns
    By AndreaJean18 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2017, 08:12 PM
  2. [SOLVED] Merge multiple (and variable) rows and columns by repeating value
    By moreron in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2013, 02:19 AM
  3. [SOLVED] Merge multiple duplicate rows into columns - more than 65536 rows
    By a3des in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-05-2013, 09:18 AM
  4. [SOLVED] How to Merge multiple duplicate rows into columns
    By geiollex in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-04-2013, 03:03 PM
  5. Merge rows according to unique IDs in column A- with multiple columns
    By inbars in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-26-2013, 08:03 PM
  6. [SOLVED] Merge duplicate rows and merge the columns (approx 10 columns)
    By adfeddon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2012, 11:19 AM
  7. Merge rows with a few same columns in excel
    By xparo in forum Excel General
    Replies: 1
    Last Post: 08-26-2011, 05:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1