Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 17th 08, 03:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
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.


  #2   Report Post  
Old March 17th 08, 05:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 06:59 PM
Need lookup of data both vertical and horizontal rmargh Excel Discussion (Misc queries) 2 November 21st 07 06: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 10:29 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017