• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple OFFSET twist

G

Genuiine

New Member
I have a table with values as such:

Category 1 Category 2
Value1 Value2 Value3 Value1 Value2 Value3
1 data11 data12 data13 data14 data15 data16.........
2 data21 data22 data23 data24 data25 data26
3 data31 data32 data33 data34 data35 data36

With about 50 columns of repeating data in different categories. I want to create a function that would allow me to make a pivot table without having to copy-paste. I was thinking of an offset function but i can't get it just right. I want a pivot table but don`t care how it`s made, maybe something like:

1 category1 value1 data11
2 category1 value1 data21
3 category1 value1 data31
1 category1 value2 data12
2 category1 value2 data22
3 category1 value2 data32
1 category1 value3 data13
2 category1 value3 data23
3 category1 value3 data33
etc...

Basically I want a function to join all the columns into one....then with corresponding observation, value (or column name/variable), and category. Any ideas?
 
That link is actually very useful, thank you, but cannot normalize in this case b/c I can't take in different metrics; it simply combines them. The pivot table created is just too simple for these purposes. I have uploaded an example of what I am looking for. I think the first thing I need is an offset function to place the data (or values) into a single column: take the 3 values from column 1, then 3 values from column 2, etc. I need an end result like what is in the example. Thank you.
 

Attachments

  • Example.xlsx
    9.5 KB · Views: 5
Hmm...well the example I gave is similar to the project I'm working on, without giving real information. I'm guessing this was an unintended consequence (example is based on another file). I can't seem to edit older posts...could someone delete it?
 
Last edited:
Hi ,

Can you give us a link to the older post which you want deleted ? You can click on the post number to get a permalink to the post.

Narayan
 
That was exactly what I needed. Sorry I don't need the post deleted, there's no mistake/link. Thank you very much for the help Narayank.
 
You must log in or register to reply here.
Back
Top