I have two sheets in Excel with the following content:


Product    Quantity

Product1   10
Product2   15
Product3   20


Product    Price

Product1   120
Product2   235
Product3   350

Now, I want to merge these two sheets into Sheet3 as the following:


Product    Parameter   Value

Product1   Quantity    10
Product1   Price       120
Product2   Quantity    15
Product2   Price       235
Product3   Quantity    20
Product3   Price       350

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 ?


Just copy one below the other and Sort the first column.

If you require something more automated, use Power Query (aka Data --> Get & Transform in Excel 2016) to do that by creating a Query that uses the two tables for the Source.

When you add to either table, just Refresh the Query.

  • It would be useful if the downvoter added a comment as to the reason for the down vote. – Ron Rosenfeld May 7 '18 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.