Rearranging data

**BFee** · 07-21-2010, 12:50 AM

Hi,

I am looking for a way to automate / formulate the 'transposition' of data. The data is held in multiple rows across three columns and I want the final product to be held in a single row with the three colums repeated across the one row - please refer to the attached example (a bit difficult to explain in words!).

I have tried a Pivot Table but have not been able to get the data into a single row

Perhaps I'm not using Pivot Tables correctly (I am a novice in 'Pivots' and not very goosd in Excel in general). Perhaps the solution is a formula, or something else??

Cheers,
BFee

**rethguals** · 07-21-2010, 04:18 AM

in L11
=IF(COUNTIF($C$11:$C$27,$K11)<INT((COLUMN()-9)/3),"",OFFSET($C$10,SMALL(IF($C$11:$C$27=$K11,ROW($C$11:$C$27)-10),INT((COLUMN()-9)/3)),MOD(COLUMN(),3)+1))
with [Ctrl + Shift + Enter]
& copy & paste to another cells

**BFee** · 07-28-2010, 01:53 AM

Thanks so much Rethguals!

Apologies for the delayed reply ... been ill. I have applied the formula and, whilst I'm still struggling to understand it, it works! Also, I had no knowledge of the CSE [Control + Shift + Enter] function ... a great one to remember when needing an array in an 'IF' formula.

Thanks again,
Ben

Rearranging data

LinkBack

Thread Tools

Rate This Thread

Display

Rearranging data

Re: Rearranging data

Re: Rearranging data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions