Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have some data with product prices relative to tariff code :

+==============+========+========+========+========+
| product code | code10 | code20 | code30 | code40 |
|==============+========+========+========+========|
| product1     | 12.3   | 12.4   | 12.5   | 12.6   |
+--------------+--------+--------+--------+--------+
| product2     | 13.3   | 13.4   | 13.5   | 13.6   |
+--------------+--------+--------+--------+--------+
| product3     | 14.3   | 14.4   | 14.5   | 14.6   |
+--------------+--------+--------+--------+--------+

I want to get a table with three columns : product code, price and tariff code

+==============+=======+=============+
| product code | price | tariff code |
|==============+=======+=============+
| product1     | 12.3  | code10      |
+--------------+-------+-------------+
| product1     | 12.4  | code20      |
+--------------+-------+-------------+
| product1     | 12.5  | code30      |
+--------------+-------+-------------+
| product1     | 12.6  | code40      |
+--------------+-------+-------------+
| product2     | 13.3  | code10      |
+--------------+-------+-------------+
| product2     | 13.4  | code20      |
+--------------+-------+-------------+
| ........     | ....  | ......      |
+--------------+-------+-------------+
| product3     | 14.6  | code40      |
+--------------+-------+-------------+

I tried to acheive this with a custom paste without success.
Is there anyway to do this with Calc ?

share|improve this question
    
Can you use pivot tables? –  Dave Jul 21 at 9:37
    
@DaveRook: Since Pivot tables aren't trivial to use, it would be worth an answer how to solve this using pivot tables. –  tohuwawohu Jul 21 at 9:53
    
I'm open to any solution, but if there's no quite simple way to do this in Calc, I would make a small script to end up with this result –  user139970 Jul 21 at 9:59

1 Answer 1

up vote 2 down vote accepted

In the comments of the OP, you mention using a script. This VBa does it, saving the results to another sheet.

Even if your machine doesn't support VBa, the script may give you an idea so you can port it over in your preferred language.

Remember, with VBa, undo usually doesn't work, so save a copy of the file first as a back up!!

Sub DoSomeMagic()

Call CreateHeadings

Dim row As Integer
row = 2

Dim sheet2row As Integer
sheet2row = 2

Do While (Worksheets("Sheet1").Range("A" & row).Value <> "")


Dim col As Integer
col = 66

    Do While (Worksheets("Sheet1").Range(Chr(col) & row).Value <> "")

        Worksheets("Sheet2").Range("A" & sheet2row).Value = Worksheets("Sheet1").Range("A" & row).Value
        Worksheets("Sheet2").Range("B" & sheet2row).Value = Worksheets("Sheet1").Range(Chr(col) & row).Value
        Worksheets("Sheet2").Range("C" & sheet2row).Value = Worksheets("Sheet1").Range(Chr(col) & 1).Value
           col = col + 1
           sheet2row = sheet2row + 1
    Loop

    row = row + 1

Loop

End Sub

Sub CreateHeadings()

Worksheets("Sheet2").Range("A1").Value = "product code"
Worksheets("Sheet2").Range("A1").Font.Bold = True
Worksheets("Sheet2").Range("B1").Value = "price"
Worksheets("Sheet2").Range("B1").Font.Bold = True
Worksheets("Sheet2").Range("C1").Value = "tariff code"
Worksheets("Sheet2").Range("C1").Font.Bold = True

End Sub

Original (Sheet1)

enter image description here

After running VBa, sheet2 looks like

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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