A Microsoft Excel forum. ExcelBanter

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 » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Reformattign Data/Converting-Combinign Vertical Data to Horizontal



 
 
Thread Tools Display Modes
  #1  
Old March 17th 08, 02:10 PM posted to microsoft.public.excel.worksheet.functions
Tunderwood
external usenet poster
 
Posts: 1
Default Reformattign Data/Converting-Combinign Vertical Data to Horizontal

I have a report formatted in Crystal Reports and exported to Excel. It lists
costs for various products vertically grouped by source.

EX:
Source #1 Full $3.00
Source #1 Half $2.00
Source #1 Qtr $1.00

I need this Data to be displayed in a workbook as follows:

Source #1 $ Full $3.00 Half $2.00 Qtr $1.00

The Full Half and Qtr woudl be the Column Headings with the $$ amounts under
each. This involves deleteing the duplicate Source #1 from the Row format and
combining them into one row.

I need help? This has been doen for a while here at my company and the
person who was handling this is no longer with us and left no instructions.

Ads
  #2  
Old March 17th 08, 04:20 PM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
external usenet poster
 
Posts: 5,441
Default Reformattign Data/Converting-Combinign Vertical Data to Horizontal

Tunderwood,

Use a pivot table.

I'm assuming you have column headings. Select all your data, including the headings, then use Data /
Pivot Table... and click Finish.

Then drag the column heading for the Source values to the row field area, the column heading for the
Full/Half column to the column field area, and the dollar values column heading to the data items
area. Right-click on the grey tab if it says "Count of...." instead of "Sum of ..." and change the
field setting to SUM.

You can turn off grand totals and subtotals,a nd format the cells to get the table to look the way
you want.

HTH,
Bernie
MS Excel MVP


"Tunderwood" > wrote in message
...
>I have a report formatted in Crystal Reports and exported to Excel. It lists
> costs for various products vertically grouped by source.
>
> EX:
> Source #1 Full $3.00
> Source #1 Half $2.00
> Source #1 Qtr $1.00
>
> I need this Data to be displayed in a workbook as follows:
>
> Source #1 $ Full $3.00 Half $2.00 Qtr $1.00
>
> The Full Half and Qtr woudl be the Column Headings with the $$ amounts under
> each. This involves deleteing the duplicate Source #1 from the Row format and
> combining them into one row.
>
> I need help? This has been doen for a while here at my company and the
> person who was handling this is no longer with us and left no instructions.
>



 




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 On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Turn vertical data to horizontal data automaticaly Leandro Sesarego Excel Worksheet Functions 7 August 2nd 08 04:31 AM
how do i convert vertical data into horizontal data Rod Dakan Excel Discussion (Misc queries) 6 February 27th 08 05:59 PM
Need lookup of data both vertical and horizontal rmargh Excel Discussion (Misc queries) 2 November 21st 07 05:10 PM
transposing vertical data to horizontal with varying amount of data Ghosty Excel Discussion (Misc queries) 5 August 1st 06 05:20 AM
converting vertical data list to horizontal data list tjb Excel Worksheet Functions 2 July 15th 06 02:17 AM


All times are GMT +1. The time now is 04:30 PM.


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