List Running Horizontally-Pivot Table Possible?

I just had a customer ask this one and I'm not so sure 
about it.
1) The customer has setup a list of information that is 
running horizontally instead of the typical list running 
vertically.
2) I just taught him how to do pivot tables and he wants 
to generate a pivot table with his "horizontal" list.
3) I know he can copy and paste special and use the 
Transpose feature to change the list from horizontally 
oriented to vertically oriented.
4) the question is: can he leave the table in its original 
orientation and still generate Pivot tables? I can't seem 
to make it work and would like to know if it is possible 
or not.

Thanks for an assistance on this request.
Jugglertwo
0
anonymous (74722)
12/23/2004 9:07:20 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
824 Views

Similar Articles

[PageSpeed] 30

A pivot table assumes the headings are in the first row of the source 
data, so your customer would have to rearrange his data.

Jugglertwo wrote:
> I just had a customer ask this one and I'm not so sure 
> about it.
> 1) The customer has setup a list of information that is 
> running horizontally instead of the typical list running 
> vertically.
> 2) I just taught him how to do pivot tables and he wants 
> to generate a pivot table with his "horizontal" list.
> 3) I know he can copy and paste special and use the 
> Transpose feature to change the list from horizontally 
> oriented to vertically oriented.
> 4) the question is: can he leave the table in its original 
> orientation and still generate Pivot tables? I can't seem 
> to make it work and would like to know if it is possible 
> or not.
> 
> Thanks for an assistance on this request.
> Jugglertwo


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/23/2004 9:18:53 PM
You mean like this?

Header1 x  x  x  x
Header2 y  y  y  y

etc.

No, a pivot table works on a typical database table with headers like

Header1  Header2
x               y
x               y
x               y


So the solution would be to transpose the data, however you can create a 
transposed link using the TRANSPOSE function that will be updated when the 
original table is updated and then create the pivot from the linked table, 
assume 
original table is A4:Z8, in another sheet (preferably) select a range the 
same size, i.e.

A2:E27

with A2 as the active cell in the formula bar type =TRANSPOSE(Sheet1!A4:Z8)

then enter it with ctrl + shift & enter

The create the pivot from the new linked table

Regards,

Peo Sjoblom



"Jugglertwo" wrote:

> I just had a customer ask this one and I'm not so sure 
> about it.
> 1) The customer has setup a list of information that is 
> running horizontally instead of the typical list running 
> vertically.
> 2) I just taught him how to do pivot tables and he wants 
> to generate a pivot table with his "horizontal" list.
> 3) I know he can copy and paste special and use the 
> Transpose feature to change the list from horizontally 
> oriented to vertically oriented.
> 4) the question is: can he leave the table in its original 
> orientation and still generate Pivot tables? I can't seem 
> to make it work and would like to know if it is possible 
> or not.
> 
> Thanks for an assistance on this request.
> Jugglertwo
> 
0
PeoSjoblom (789)
12/23/2004 9:37:06 PM
Thank you very much. It makes sense and it saves me from trying to figure out 
a way that does not exist. I will try your suggestion later.
Thanks!
Jugglertwo

"Peo Sjoblom" wrote:

> You mean like this?
> 
> Header1 x  x  x  x
> Header2 y  y  y  y
> 
> etc.
> 
> No, a pivot table works on a typical database table with headers like
> 
> Header1  Header2
> x               y
> x               y
> x               y
> 
> 
> So the solution would be to transpose the data, however you can create a 
> transposed link using the TRANSPOSE function that will be updated when the 
> original table is updated and then create the pivot from the linked table, 
> assume 
> original table is A4:Z8, in another sheet (preferably) select a range the 
> same size, i.e.
> 
> A2:E27
> 
> with A2 as the active cell in the formula bar type =TRANSPOSE(Sheet1!A4:Z8)
> 
> then enter it with ctrl + shift & enter
> 
> The create the pivot from the new linked table
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
> 
> "Jugglertwo" wrote:
> 
> > I just had a customer ask this one and I'm not so sure 
> > about it.
> > 1) The customer has setup a list of information that is 
> > running horizontally instead of the typical list running 
> > vertically.
> > 2) I just taught him how to do pivot tables and he wants 
> > to generate a pivot table with his "horizontal" list.
> > 3) I know he can copy and paste special and use the 
> > Transpose feature to change the list from horizontally 
> > oriented to vertically oriented.
> > 4) the question is: can he leave the table in its original 
> > orientation and still generate Pivot tables? I can't seem 
> > to make it work and would like to know if it is possible 
> > or not.
> > 
> > Thanks for an assistance on this request.
> > Jugglertwo
> > 
0
Jugglertwo (10)
12/24/2004 5:29:02 AM
Reply:

Similar Artilces:

Merging List w/ a Form
Can anyone assist me with merging a form created in Excel with a database (or list) also created in Excel? I know how to merge an Excel worksheet to be used as a database for Word in the Mail Merge command, but my FORM, with which I am trying to merge my database, was created in Excel and needs to remain under its format (not be converted to Word). I am filtering the information I need from the database to be imported to the Excel Form. Any help would be appreciated. Thank-you. If you're trying to display information from one record in the database, there are sample files here...

sending and receiving tables with relationships
I have a database with three linked tables (parent table, a child and a "grandchild" table). There are 8 field reps in the company I work for. Each of the parent tables cooresponds with the data pertaining to each rep. What I'm wanting to do it to somehow separate the data so I can send each rep just their table and corresponding children tables, they update the data and send it back to me. Is there a way to do this? I have found a way to do it with all the data in one table, but the forms to enter data end up being cluttered, hard to navigate and confusing. -- Message pos...

Hiding OLD customers in list
I have a customer list (tblCustomers) And an Orders table (tblOrders) I want to have an admin area functionality that prunes (hides from display in any list or report) Customers from the list that have not placed an order in X number of days (Admin can enter the number of days in a text box) Solution does not have to be in days or in a text box. Could be several check boxes (30 days, 10 weeks, 2 years...). I am just looking for the simplest solution for us to limit the Customer list by last order date. Any help here will be appreciated. Thanks in advance If it were me, I would have a ...

Sharing tasks, group task list
I have a team of account people who assign tasks to a production team. I want to create a shared task list that I (and others) can assign tasks to my team that also updates me when progress on the tasks has been made (ex. marked complete). I know I can do this from my personal task list, but is there a way to have a shared task list where items can be assigned and will also be updated through exchange? Public folders maybe...? Using Outlook 2003 on exchange server. Thanks! Chris Set up a shared Mailbox and make Tasks there, Assign them to team members. In that Tasks folder, show t...

OT: demoralized tables
Kirk Kuykendall has a good overview on Common Table Expressions http://www.sqlservercentral.com/articles/CTE/62291/ but his conclusion contains a "new db term" (at least I had never seen it before)... "As you can see CTEs can be very useful. I have found them particularly handy when cleaning up demoralized tables. Like imported spread sheets." I assume it was on purpose and couldn't resist passing it on. We've all been there... gary I'm wondering if demoralized tables might be an undiscovered cause of file corruption. I would think that one table wit...

9.0 Table Changes
Will be updating to V9, am looking for a list of the tables that changed and the documentation says it is on V9 cd 2, under the SDK But I don't see anything on the CD so do I have to install v9, and the SDK before I can get a list of the table names? -- Doug You need to install the SDK "Doug" wrote: > Will be updating to V9, am looking for a list of the tables that changed and > the documentation says it is on V9 cd 2, under the SDK > > But I don't see anything on the CD so do I have to install v9, and the SDK > before I can get a list of the tabl...

Excel 2000 running on Windows XP SP2 does not properly open CSV fi
The same file opened in using Excel 2000 running in Windows 2000 Pro open properly. The rows and columns are formatted properly. The same Excel 2000 opening the same CSV file but on Windows XP SP2 will not properly format the rows and columns. Hi check your regionals ettings in Windows ('Start - Control Panel') I'd suspect you have different column delimiters specified (comas versus semicolons for example) -- Regards Frank Kabel Frankfurt, Germany "Oscar" <Oscar@discussions.microsoft.com> schrieb im Newsbeitrag news:4985F8F5-5D33-425E-989C-65529CE63D05@mic...

Mails are being send without colors and table structure destroyed
Dear All, I am using Microsoft Outlook 2003. My other office products are of Office 2000. Word 2003 is not there on my PC and therefore the option to use it is disabled. Now how to use Microsoft Word 2000 as the mail editor? I am facing some strange problems with Outlook 2003. I composed a outage notice in Word 200 with table structure and colouring. I pasted the same in mail and send it. The mail send was in a format where table structure completely destroyed, all the words are not well aligned and the e-mail is not in colors. I able to see it structured in the send folder but it is not gett...

Tree view of wiki pages: is it possible???
Hi all, I'm building a wiki site and I'd like to have on the left side menu (the quick launch menu) somehting like a tree view that let me easy navigate the wiki pages... maybe a wiki library is not what I need, because none will add or remove contents... basically I'm using a site as an "instruction book"... and the tree view should make easy to navigate the differents web pages... any suggestion??? Thanks Vit On Mar 19, 5:59=A0pm, Vit <fina...@gmail.com> wrote: > Hi all, > > I'm building a wiki site and I'd like to have...

List of Differences Between Outlook 2000 and Outlook 2003
I need to spend 45 minutes introducing Outlook 2003 to my Outlook 2000 users. Can anyone provide a site with a succinct list (screenshots would help) of differences between Outlook 2000 and Outlook 2003? Many thanks. Tom Are you looking for screenshots of Outlook 2003 or ones comparing OL2000 and 2003? Wouldn't you be a more convincing teacher if used both and took the screenshots yourself? This would help you learn and understand the products better. (Vmware or virtual pc make this really easy and you could show them the differences live. :)) -- Diane Poremsky [MVP - Outlook] ...

Contact List
How come whne I put in a contact in Outlook 2004, and then I add a fax #, it lists the contact twice on my main list? It's because there is no Outlook 2004. The standard behavior for the Outlook Address book is to display all electronic addresses (both fax and e-mail). That behavior cannot be changed. The only way to prevent the display of fax numbers is to store them in a different field or to disguise them (e.g., precede them with an alpha character) so that Outlook won't recognize them as phone numbers. There are also a number of utilities available that can do this for you automat...

grabbing emails and paste into dist list?
Hi there, Okay, here's the scenario in Outlook 2003. I get an email from someone who has included about 50 important people in the cc field. I'd like to take all those email addresses and dump them into a distribution list in one fell swoop. So far, I hit "reply to all" and selected all of those cc email addresses, but I can not paste into a distribution list. How can I do this? Thanks, Gordon ...

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

Filters, Can't Find Exception List In Rules
I'm trying to set up my filters for junk mail. I want junk mail with certain words in the subject sent to deleted folder. I found the filters.txt file and some of the words are already there. So apparently the filter isn't working. I tried to follow the directions on how to turn them on and they said to choose the exceptions entry in the rules wizard but I have no such choice. I know how to edit the filters.txt file but Outlook is not using it. Can anyone help? The Filters.txt file isn't actively used by Outlook -- it's really just a "readme" type file to sh...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss´┐Ż:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

Is it possible to add my own header to an email in Office Outlook or Outlook Express?
Hi all, I mean something like followings: X-MDaemon-Deliver-To: someone@somewhere.com or X-MyHeader: 123456 I'm able to do this via C# SmtpClient programmatically, but I want to this by an email application. Thanks in Advance Afshar Mohebbi > I mean something like followings: > X-MDaemon-Deliver-To: someone@somewhere.com > or > X-MyHeader: 123456 > I'm able to do this via C# SmtpClient programmatically, but I want to > this by an email application. For Outlook, you'd need to do it via code. I'd start at http://www.outlookcode.com. For OE, you'll wan...

Creating a e-mail contact list which has "undisclosed recipients"
Can anyone help me with a contact list for my e-mail which does not disclose all recipients names. I would like to send an e-mail regarding company information to several people which answers questions, but I don't want everyone to know who I am addressing it to. You need enter the names under the Bcc option when you address the email. That way, each recipient will only see their own name, but the email will go to everyone. Actually, the recipients won't see their own name in the To field. They will only see what the sender puts there, if anything (some ISPs require that you...

Is it possible to find multiple data?
We have a small lotterysyndicate in work which is all logged onto an excel spreadsheet. Is it possible to search all numbers and all occurances at once, rather than individually? Ie - we have 15 lines of 6 numbers each - can i search for all 6 numbers drawn, across all 15 lines in one go? Im sure this must be possible - just dont know how. Thanks -- stewwie ------------------------------------------------------------------------ stewwie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37128 View this thread: http://www.excelforum.com/showthread.php?threadid...

Control Supported Device List for Active Sync
Hi I want to restrict what devices can connect to Active Sync - there is a built in list of supported devices which the service packs keep updating but is there a way to control this list yourself i.e. to only include two devices in the list? I want to control which devices meet the company standard and stop any which don't! More Info: Overview of mobile devices that are supported by Outlook Mobile Access in Exchange Server 2003 http://support.microsoft.com/Default.aspx?kbid=821835 Thanks Clive ...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

checking for existance of file before running macro
I have a macro that opens a source file to retrieve information. I want to be able to check to see if it's there first and if it's not send a msg to the user stating that the file isn't there. When the user clicks okay on the box I want the macro to end without error. Can anyone help? thx, Jim You can use the Dir() function: dim myFilename as string myfilename = "C:\autoexec.bat" if dir(myfilename) = "" then 'it's not there else 'yes, it is end if Jim wrote: > > I have a macro that opens a source file to retrieve information. I wan...

Drop Down List for Chart
I have grouped my series into several groups. How do I display a drop down list including the groups which will dynamically identify which series to plot on the chart? For example: Group A = Series 1, Series 2, Series 3 Group B = Series 4, Series 5 Group C = Series 6 Drop down list includes values: "Group A", "Group B", "Group C" When Group A is selected within the drop down, Series 1, 2 and 3 are plotted on the chart. Do you mean you have to plot data, and have the chart move week to week? if so, easiest way is to use a hlookup/vlookup "savior1&quo...

hwo to recover personal folders and contact list??
I had office 2003 running on my C: drive and I had a major system crash so I backed up the entire C: drive to D: .. then I formatted and reinstalled windows XP pro and office 2003. can someone tell me where on the D: drive I woudld find the contact list and all my folders from outlook 2003 that I had running before? THanks.. Hi Dave, please look here, which datas are important for outlook http://home.arcor.de/andreas.roeder-privat/English%20Help.htm#18 If you search for the relevant files you could import them into your new Outlook! -- Gestern gings noch, ICH habe nichts gemacht! ;-) Brave...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

finding common numbers in large lists
Hello, I have two large lists of numbers (on two seperate worksheets). By large, I mean that one list is 1000+ numbers, and the second list is 3000+ numbers (each number is in its own cell). The numbers are of the form: xxxx.xxx (that is, they are precise up to three places after the decimal) 1) Is there some quick way that I can find the numbers that are common to both lists? 2) Is there a way I can find the numbers in common to both lists using only the whole number portion (diregarding what comes after the decimal of each number)? Thanks! Hi see: http://www.cpearson.com/excel/duplic...