Pivot table question #6

Hi, I'm using an excel spreadsheet and I have a list of customer
numbers in column A and a list of account numbers in column B.  Many
customers have more than 1 account number.  What I would like to do is
change the layout so that I have each customer number listed only once
and have all of the account numbers corresponding to the customer
number in the same row (so for example I would have customer number in
A:1, account number 1 in B:1, account number 2 in B:2, account number 3

in B:3 ect) .  Is there a way to do this?  Is it possible to do this in

a pvt table?  Thanks.

0
lj1 (84)
2/6/2006 6:07:40 PM
excel 39879 articles. 2 followers. Follow

4 Replies
235 Views

Similar Articles

[PageSpeed] 14

Hi

You could do this with subtotals. Subtotal the customers first, and then run 
again with the accounts. Make sure that when you run it for the second time 
you uncheck 'Replace current subtotals'

Hope this helps.
Andy.

"lj" <lj@spu.edu> wrote in message 
news:1139249260.140395.192870@g47g2000cwa.googlegroups.com...
> Hi, I'm using an excel spreadsheet and I have a list of customer
> numbers in column A and a list of account numbers in column B.  Many
> customers have more than 1 account number.  What I would like to do is
> change the layout so that I have each customer number listed only once
> and have all of the account numbers corresponding to the customer
> number in the same row (so for example I would have customer number in
> A:1, account number 1 in B:1, account number 2 in B:2, account number 3
>
> in B:3 ect) .  Is there a way to do this?  Is it possible to do this in
>
> a pvt table?  Thanks.
> 


0
Andy
2/6/2006 6:19:21 PM
Use Subtotal in the pvt table?  I tried adjusting it in the pvt table
but this didn't seem to adjust to the layout i want.  How do I use this
function to get what i'm looking for?

0
lj1 (84)
2/6/2006 6:26:52 PM
Hi

I didn't mean subtotals in the pivot table. I mean just straight subtotals.

Andy.


"lj" <lj@spu.edu> wrote in message 
news:1139250412.080360.132320@o13g2000cwo.googlegroups.com...
> Use Subtotal in the pvt table?  I tried adjusting it in the pvt table
> but this didn't seem to adjust to the layout i want.  How do I use this
> function to get what i'm looking for?
> 


0
Andy
2/7/2006 10:46:27 AM
If your account numbers are really numbers then you could try this.
Assume column A contains the Customer name, column B the customer
number.
Add a column C .. call it 'Ref'

Sort the data by Customer then Cust Number.
In Column C enter the value "1" in cell C2.
In C3 use a formula which says ..
=if(A3=A2,C2+1,1) and copy it down.

The objective is to get data which looks like...
Cust  CustNo  Ref
Fred  10012    1
Fred  24683    2
Fred  34685    3
Sue   12345    1
Sue   22345    2
Vera  34567    1   etc..

Then create a Pivot table with Ref across the Customer in the ROW area
and REF in the Column area and CustNo in the DATA area. Hide the row
and column totals.   This gives...

Sum of CustNo  Ref			
Cust        1     2    3     
Fred    10012   24683  34685 
Sue	12345	22345		
Vera	34567

... which seems to what you were asking for.

Hope this helps.


-- 
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30757
View this thread: http://www.excelforum.com/showthread.php?threadid=508992

0
2/9/2006 10:28:47 PM
Reply:

Similar Artilces:

SOP Form Question
We have several customers that require we put a Department on their invoice. One customer has about 10 and the other one has about 50. I have create a Department field on the User Defined form and if click the hour glass you can see all 60 departments. My question: is there a way to setup an Xref somewhere that would filter the dropdown values that would be associated to each customer? The issue is that a user may inadvertently select a department that is not valid for that customer. I am trying to make this as much idiot proof as possible. Any suggestions would be welcomed. T...

A question nobody probably knows the answer to. :)
I am migrating from VS 6.0 to VS .NET 2003. We have a third party library that uses the AUX_DATA structure defined in afximpl.h. One of the members it is using is bWin4 - A BOOL value to tell if we are on a Windows NT 4.0 platform. In VS .NET 2003, bWin4 was removed from the structure. In fact, all the BOOL values for the different windows flavors have been removed, except for bWin95. Anyone know why this is and what the best course of action I should take in modifying our 3rd party code to be up to date with the new afximpl.h that came with VS .NET 2003? Thanks. "Leo" <l...

Extracting Data from Table
I am working on a table where I am trying extract certain data and placing it into another table on the same sheet. Everytime I try, I highlight the criteria and click advanced under filter and select the correct range and press okay nothing changes or shows up in the new table. Help please! Thanks, Alisa ...

Update query result to table
I can't find a good example on the web showing how to update table with the result from query. Table1 name "Agent1" column have: phone#(primary key), birthdate, and agent (total record 100). table2 name "Agent 2" have the same field (total record 150) except the data on agent is different. I run a query for this two tables and want to update the table1 with the table2 data where the phone#(primary Key). I want the result form the query will replace the agent in Agent1 table. How can i do that? UPDATE Table1 INNER JOIN Table2 ON Table1.[Phone#] = Table2.[Phone#]...

Question about printing
For some reason my printer prints three copies of everything in a specific Excel spreadsheet. I reduce the number in the print screen to one, but if I close and reopen the file it returns to three. How can I set up one copy as the default? OK -- I only know this 'cause it happened to me too. But for future reference this is an Access Forum. Anyway, Go To Start | Printers and Faxes. Right click the printer that you are using go to Properties. In the General Tab Click on Printing Preferences. Then click on Advanced. There's an option that says Copy Count. Make sure that&#...

Shipping amount field in SQL table.
Good Morning To All, I have a customer who is having one of their internal programmers write an interface that will update GL accounts according to their finance department's needs. They are very specific. One of the things they need is the shipping amounts at a transaction level. I cannot find these amounts anywhere. I read somewhere else that in order for this amounts to be populated the ship to and the shipping carrier had to be filled in on the transaction. I tested it, and I still can't see the totals for the shipping charges. Does anyone know anything about this? THANKS! ...

Enter values in table from form using dlookup
Hi, Just wondering if it's possible to put values in a table, using data entered on a form? Like if the person enters for item a, 1, then it adds 1 to item A in a table. I've tried dlookup(blah) = me.text1 but it doesn't work. Thanks Dear s4: What you want is the way it is supposed to work. You don't need to do a lookup to move data from a form to a table, you simply need to "bind" your form to the table (or a query based on the table). Every form has a "Record Source" property; which should be the name of the table or query you want to be the source ...

XML question #2
Hi, I don't know much about XML but I thought it was a cross platform data file. I opened a CSV file in XL and saved it as XML. Not only did it save the file structure, plus the data, but also saved a lot of XL specific stuff. Now, I want to open the XML file with another programme (Filemaker) but I get an XML parsing error when it gets to the "ProtectScenarions" tag. Is there some way to save the file without all the XL-specific stuff ? Is there such a thing as "plain XML" ? Thanks Yrrreth. ...

SQL Server 2005 SP3 Failed IIS 6.0
I have a SQL Server 2005 Enterprise Edition database server (32-bit) with service pack 2. When I tried to install SQL Server 2005 SP3 on the database server, it completed on all SQL Server programs except reporting services (Database Engine, SSIS, and Analysis Services). The present IIS version is 6.0. Is there a compatible issue with SQL Server 2005 SP3 and IIS version 6.0? Please help me resolve this issue. Thank you very much. Summary Report Error: Product : Reporting Services (MSSQLSERVER) Product Version (Previous): 3042 Product Version (...

question regarding transaction log files
Does the transaction log file (E000X.log) contain the contents of emails sent or received by a user or it just contains information regarding emails. The reason i am asking this is that incremental backup will only backup the transaction log files, not the databases. My concern is that if i perform a Normal backup on Monday and then perform incremental backup for the rest of the week and then i have a database crash during the week, will I be able to restore emails received to the database after the last Normal backup? Thanks. if you do a weekly fulll, and then a nightly incremental,...

Find as u type by Allen Browne question
I've used the 'find as u type' utility (http://allenbrowne.com/AppFindAsUType.html) many times successfully, but I'm now running into something I can't solve. When I base the form on a table or selection query, everything works fine. But when I base the form on a query which in itself is based on a query and a linked table I get the following behavior: 1. The form_load works fine. 2. Filtering works fine as long as the value is found. 3. When the typed value can't be found the record fields disappear and the focus is set to the field where you enter the value (th...

OE 6 losing blocked senders from list?
I've been noticing lately that some of the trolls I've blocked in various NG's messages are reappearing and I've found that the blocked senders list is suddenly losing details from it. Any ideas ? Has OE6 only got a limited amount of space for blocked senders? Seems to work fine apart from this. Try posting this in an Outlook Express news group - this is not one of = them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news = groups. You can also find some good Outlook Express informatio...

Database question: licensing and costs
Hello NG, I want to design a database application used on notebooks. My potential customer is talking about 50 licences for his notebooks and one server application. Would you prefer to use MDBs on the notebooks? If so, do you know if I have to purchase the licence fees and how much maybe? Or would you use an Open Source database like for example MySQL - but I don't want to make my source code public. Do you know prices? Does someone has experiences or does someone know links? Thanks for hints, Guido I will post this in a SQL server related NG instead. --- Ajay On Apr 21, 1:04 pm, &...

Navigation question
Is there a keyboard shortcut for returning to the most recent edit (equivalent of Shift+F5 in Word)? [to avoid potential misunderstanding, I don't mean going to the active cell, which is Ctrl+Backspace]. Thanks Hi AFAIK there's no such shortcut / functionality -- Regards Frank Kabel Frankfurt, Germany NOtoUCE@execulink.com wrote: > Is there a keyboard shortcut for returning to the most recent edit > (equivalent of Shift+F5 in Word)? > > [to avoid potential misunderstanding, I don't mean going to the > active cell, which is Ctrl+Backspace]. > > Thanks ...

Memo field spacing question
I have a memo field in a form and I want to be able to have the user hit enter to move to the next line of the space, however when you hit enter in the field it just brings you to the next field in the form. In excel I would accomplish this by hitting the alt and enter key - how do i do this in a memo field on a form? You can hit Ctrl+Enter or Shift+Enter or set the property sheet (Enter Key Behavior property) for the control to New Line in Field (on the Other tab). -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Belinda7237&q...

binding two tables into one recordset
I am trying to bind two tables in a database into one recordset. I have added all the Column and Binded them with Class wizard and have added the addtional table to the GetDefaultSQL() method and the filter to the set object. When I run the program it tells me that I have: Attemped to scroll past the end or before beginning of the data. This the section of my code that I added the filter to. it is in my OnInitialUpdate for my recordview m_pSet = &GetDocument()->m_leagueManagerDBSet; m_pSet->m_strFilter = "Teams.Teams_ID = Games.ID"; CRecordView::OnInitialUpdate()...

Form Data Entry From Two Tables
I would like to create a form that gets it data from two different tables. The tables are very basic and table1 has a one to many relationships with table2. Table1 fields; ID – auto number, Name – text, Available – yes/no. Table2 fields: ID – number, Resourced – yes/no, Date – date/time, Hours – number. The idea of the form would be for data entry with a list of names, and then next to each name is a field to enter the hour for each day of the week that each person is available to work. I would need to use a main form for unbound data and sub form for data entry. One unbound field for th...

Another Date Validation question (date +1)
Hi Everyone, I'm trying to build in a date validation on my form that ensures that the start date that is entered for the new record is equal to the the end date from the "previous" record plus one. I did some searching in the group and saw that it would be difficult perhaps to do that b/c databases don't work like spreadsheets so the concept of "previous" is a little different and I wanted to do this I would have to have some sort of counter field that associates the records? Is that correct. This is what is on my form: 1: Last day of previous period (date fiel...

table design 12-24-07
I'm building a new database and making the following tables: tblStudents, tblTeachers, and tblAdministrators. Many of the filed s in these tables are the same, for example name, address, city, state, zip, phone.....do I need to specify these fields differently in each table, for example StudName, StudAdd, StudCity, and TeachName, TeachAdd and TeachCity or is it acceptable to just name them the same in all tables thinking the tables will differentiate them? Thanks On Mon, 24 Dec 2007 12:13:00 -0800, SITCFanTN <SITCFanTN@discussions.microsoft.com> wrote: >I'm building...

store external db password in a linked table
Hi All, I have an access file which linked with an orcale database. Each time when i query the file, I need to enter the id, pw & server name. Is there a way to store my login information into access so that I dont need to login everytime? Thank you very much!! ...

simple question
hello, how do i reset my StatusBar data when user opens a new document (File->New) ? i tried something like this (it doenst work because status bar isnt ready yet) BOOL CGraphicalEditorDoc::OnNewDocument() { if (!CDocument::OnNewDocument()) return FALSE; ((CMainFrame*)AfxGetMainWnd())->UpdateStatusBar(BLA,BLA,BLA); return TRUE; } i mean by "status bar isnt readey yet" is it return an assortion error ...

year week table help
Hi All, Given: year = 2009 week = 3 How would I build a temp table of year/week values 27 weeks back. for the example above: 2009, 3 2009, 2 2009, 1 2008, 52 2008, 51 .... (Also, doesn't some years have 53 weeks?) thanks, rodchar About the 53/54 week issue is no longer an issue. 2008, 52 will work fiine. "rodchar" wrote: > Hi All, > > Given: > year = 2009 > week = 3 > > How would I build a temp table of year/week values 27 weeks back. > > for the example above: > > 2009, 3 > 2009, 2 > 2009, 1...

W2K3 Eval key question
The server setup group doesn't seem to be discussing Windows 2003 server stuff so I thought I'd try here instead. Please redirect me if I'm incorrect. Can I use the product key on the original W2K3 eval kit (no SP) I have for installing W2K3 R2 SP2 eval which I recently downloaded? I have not used the original eval kit and it appears I can only order a Windows 2008 Server eval kit from MS these days, although I did manage to locate the download for the 2K3 R2 SP2 disc images at MS but not a method for getting the key emailed to me. I'm lucky I found what I did ...

Filter Juction Table and related tables
Hello I have a Relational DB in Access 2003. I have a Junction Table with (PK OOB and WordID) Keys. The Junction Table is linked many to One to TOOB and TWrd. TOOB [ObrID(PK), Fld1, Fld2, Fld3, Fld4 and Fld5] TWrd[(WrdID(PK), WordName} I need to filter in an unbound txtbox by Wordname entering one, two, three or four words separated by OR or AND operator (Which operator you recommend?). In my continuous form in the Form Header I have an unbound txtFilter to enter the words and a bButton (bFilter) to trigger the search. When records are identified in Junction Table, reco...

Pivot Tables question
I have two colums of data such as: French / Male British / Female Chinese / Child French / British / Male Chinese / French / French / Female I create a pivot table that shows me how many instances of blank (n data) in column 2 for each item in column 1 (nationality), in this cas French 2 times, Chinese 1 time. The question is the way I have it set up I have to un-check (100 lines) all of the instances of male/child/female etc. in order t leave only the Blank instances. How can I just choose to see only t...