A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can I transpose data from rows to cols with a formula not pivot ta



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 08:54 PM posted to microsoft.public.excel.worksheet.functions
BrainDead
external usenet poster
 
Posts: 1
Default Can I transpose data from rows to cols with a formula not pivot ta

Hi All

I have data listed in rows that I want to display in columns which will then
be used in another program and I can't use a pivot table.

Example:
RefNo Area Item Units Price Value
1871459 Dealer2 CarA 40.560762 401.62989 16290.41438
1871459 Dealer1 CarB 0 2.934 0
1871459 Dealer4 CarY 0 1 0
1871459 Dealer1 CarD 7259.675176 3.421 24835.34878
1871459 Dealer1 CarV 0 1 0
1871459 Dealer1 CarU 0 2.8215 0
1872780 Dealer1 CarA 0 3.7035 0
1872780 Dealer1 CarB 300.706389 5.132 1543.225188
1897670 Dealer5 CarF 2801.437112 3.20225 8970.901992
1897670 Dealer5 CarH 0.143672 6.19482 0.890022179

One line for each Ref No with repeating columns for the number of items they
have (no calculations - the data has already been grouped). Won't know what
will be in any of the columns but can expect a maximum or 10 items per Ref
No. Only lines where Units are greater than zero required. Columns: Area1,
Item1, Units1, Value1, Area2, Item2, Units2, Value2 etc. I can achieve this
with code but can it be done with function or formula?

Will be grateful for any help.
Ads
  #2  
Old June 3rd, 2010, 10:09 PM posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
external usenet poster
 
Posts: 1,113
Default Can I transpose data from rows to cols with a formula not pivotta

Excel 2007 Tables
Transpose with (monster) formulas
and no helper columns.
http://c0718892.cdn.cloudfiles.racks.../06_03_10.xlsx
Pdf preview:
http://www.mediafire.com/file/j2ciuyonvny/06_03_10.pdf

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:37 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.