Monday 18 March 2019

Cartesian product in Excel 2013

I have an excel in this format.


+----------+-------+---------+--------+--------+
| TicketId | PubId | PubName | Model1 | Model2 |
+----------+-------+---------+--------+--------+
| 1234 | 12 | abc | xyz1 | pqr1 |
| 1235 | 34 | iuy | fgy6 | piu8 |
+----------+-------+---------+--------+--------+

and I am targeting to create a table like the one given below.


+----------+---------+-------+
| TicketId | Display | Value |
+----------+---------+-------+
| 1234 | PubId | 12 |
| 1234 | PubName | abc |
| 1234 | Model1 | xyz1 |
| 1234 | Model2 | pqr1 |
| 1235 | PubId | 34 |
| 1235 | PubName | iuy |
| 1235 | Model1 | fgy61 |
| 1235 | Model2 | piu8 |
+----------+---------+-------+

I know it is a cartesian product or cross apply (in terms of SQL) but I want to do it in the excel.


Is there a way to do this type of data representation through excel ?

No comments:

Post a Comment

How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files. Is it possible to do this in a quick way rather than doing it m...