Transpose rows to columns: variable amount of colums and corresponding values

**Browser** · 07-10-2012, 08:40 AM

Dear all,

I have an excel sheet with three columns: ARTICLE, OPTION and VALUE.
I need to rearrange this data automatically so every OPTION becomes a column with VALUE underneath per ARTICLE (ARTICLE has to become a unique row)

I've attached the worksheet (limited to two ARTICLES), and the data how I'd like it to look afterwards.
The amount of rows per ARTIKEL can vary, it's not always the same. It is important that the VALUE sticks with the correct OPTION.

I've tried transposing but can't seem to get it to work without jumbling up the data

**jindon** · 07-10-2012, 09:10 AM

try

Please Login or Register  to view this content.

**Browser** · 07-10-2012, 09:25 AM

Apparently the code is not Mac compatible (I should have mentioned that, has to run on Office for Mac 2011)
I've tried to google for the mac alternative for the CreateObject function (which is ActiveX) but didn`t get any wiser.

**jindon** · 07-10-2012, 09:40 AM

No, it is only for Windows...

I will try another way.

**Marcol** · 07-10-2012, 10:07 AM

Try this formula approach

Some of your values are blank and some are a single space i.e. " ", others have trailing spaces.
When the value is blank the formula will return 0 (zero), it's up to you how you want to handle this.

[EDIT]
This should work for all versions of Excel, including macs that don't use VBa, e.g. 2008.

**jindon** · 07-10-2012, 10:08 AM

try

Please Login or Register  to view this content.

**Marcol** · 07-10-2012, 10:30 AM

Probably better with this non-array version of post #5

**Browser** · 07-10-2012, 10:40 AM

I tried the different proposed solutions and found Marcol's version in post #7 to be best suited.
Thank you both for your efforts!

Transpose rows to columns: variable amount of colums and corresponding values

LinkBack

Thread Tools

Rate This Thread

Display

Transpose rows to columns: variable amount of colums and corresponding values

Re: Transpose rows to columns: variable amount of colums and corresponding values

Re: Transpose rows to columns: variable amount of colums and corresponding values

Re: Transpose rows to columns: variable amount of colums and corresponding values

Re: Transpose rows to columns: variable amount of colums and corresponding values

Re: Transpose rows to columns: variable amount of colums and corresponding values

Re: Transpose rows to columns: variable amount of colums and corresponding values

Re: Transpose rows to columns: variable amount of colums and corresponding values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions