converting a column of repeating data to rows?

**Space1471** · 02-03-2010, 07:26 AM

**vikas.bhandari** · 02-03-2010, 07:50 AM

Not sure if you are looking for the same. Please find attached an example. It is purely formula based and not done through programming atall.

hth,
Vikas

**DonkeyOte** · 02-03-2010, 08:07 AM

Vikas, if you're interested (?) I suspect you could simplify your approach... using your file as example:

'Table Output'

A2: 
=IF(ROWS(A$2:A2)>COUNTA(InputData!$D:$D)/5,"",ROWS(A$2:A2))
copied down to A29

B2: 
=IF($A2="","",INDEX(InputData!$D:$D,5+(COLUMNS($B2:B2)-1)+(5+1)*(ROWS(B$2:B2)-1)))
applied to matrix B2:F29

ie no need for A:C etc on InputData sheet

the instances of "5" in the above could of course be replaced by a cell which details number of fields per "block"
(the use of +1 assumes 1 blank interspersed between blocks - again could be altered as required)

**vikas.bhandari** · 02-03-2010, 08:39 AM

Thanks so much for the updated code Mr. Moderator. I am loving reading your posts these days btw. For example, if you check this post :
http://www.excelforum.com/excel-prog...-in-cells.html

I didn't know that the problem is actually Application.Volatile. I got the solution from your "My Recommended reading" Section.

Thanks sir

Vikas