Pivot Table Layout - Generate List Of Unique Names And Their Associated Phone Numbers
Mar 18, 2013
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
View 7 Replies
ADVERTISEMENT
Jul 29, 2008
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
View 9 Replies
View Related
Oct 24, 2007
How can I use the RAND function to generate a list of simulated phone numbers.
View 9 Replies
View Related
Mar 27, 2012
I have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.
Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.
View 1 Replies
View Related
Oct 14, 2003
if there is a way to display a table as column percentages but have the totals as raw numbers.
View 9 Replies
View Related
Jul 22, 2007
I have an excel file, attached. I have a system that output all the stock in the format of sheet "price listing".
At the moment I manually create the layout for the text in "sheet 1" and use a macro to update prices etc. There are about 400 lines in the full listing and having to update the layout constantly for new products, deleted products is very time consuming. I send this list to customers every few months for them to see the range and the prices, so it has to look well.
I am looking for a macro or a pivot chart or something that I would be able to run on the "price listing" sheet and would put it in some usable format. Different customers can have different prices so that it needs to be quick and flexible. I send this list to customers every month for them to see the range and the prices, so it has to look well. Is there any way to create an index also from an excel workbook?
View 10 Replies
View Related
Apr 12, 2012
In my data, I have:
1. Age (up to 20, 21-30, 31-40)
2. Gender (male, female)
3. Location (London, Paris, Berlin)
I want to see Age and Gender as column labels and Location as row labels. However, I don't want Gender as a sub-set of Age - I want Age Labels followed immediately by Gender labels ie:
Upto20 / 21-30 / 31-40 / Male / Female
I don't want: 21-30 Male / 21-30 Female / 21-30 Total / etc etc..
Is there a way to specify this in a pivot table?
View 3 Replies
View Related
May 9, 2008
I have upgraded to Excel 2007 and the PivotTable formats are different. I prefer the old 'drag and drop' formatting, and would like to default to that.
I can switch to the classic format by going to Pivot Table Tools ---> Options ---> Display --> Classic Pivot Table Layout, but I have to do this for each table I create. I'd like this to be the defaut choice.
I've searched the board and someone posted that if you add the Pivot Table/Chart icon to your task bar, that will open the old layout --- it opens the wizard, but the table that is created is still in the new format which does not enable drag and drop.
View 9 Replies
View Related
Dec 7, 2013
How to generate all the possible combinations of 5 numbers, e.g. 1,2,3,4,5. Then 2,3,4,5,1 and so on, in a series of excel columns and rows. Also for 6 numbers etc.
View 14 Replies
View Related
Oct 7, 2005
I want to generate numbers (1 to 15) in cells A1 through O1, but the
number in each cells should be unique compare to the other cells, how
can I do it? If I use =randbetween(1,15), I can not get unique number
in each cell, some numbers are duplicated.
View 11 Replies
View Related
Mar 7, 2009
I want to add & generate unique numbers in a column of similiar words..
For Instance:
RED
RED
RED
BLUE
BLUE
BLUE
etc..etc..
I want it to look like this:
RED1
RED2
RED3
RED4
RED5
BLUE1
BLUE2
BLUE3
I want infinite numbers next to the same wording but unique.
I have 2 rows A & B.. NAME & MOBILE PHONE Title in line 1
I am not to familiar with excel. Step by Step please, anyone out there.
I saw a similar posting, but the formula did not work out for me.
Then i am going to save the file as a CSV
View 3 Replies
View Related
Oct 21, 2009
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
View 9 Replies
View Related
Mar 22, 2014
In an earlier life I was tasked with finding a "random" method of selecting two numbers from a "1 to 20" range so that the generated numbers can be applied to an set of people who will be partnered in a golf game draw.
It is only one draw per year so I don't care if the players have previously played together in past years.
easily be modified by a "passable knowledge level" person to be able to select a mystery "9" out of 18 holes that count for scores that particular round.
(btw: this is an issue only for the 20 guys who go away once a year to play golf, the world will not collapse if I have to draw numbers out of a hat, just looking for a slightly more elegant solution and I already have a few scoring macros so my first guess (but not only possibility) is VBA)
View 8 Replies
View Related
Apr 21, 2014
I have 2 columns of data in one sheet that list dates (say Master Dates) and transactions (say Master Transactions).
In another sheet i would like a formula that can choose the correct transactions that fall between certain dates (call Date Beginning and Date End) and generate a list.
i have tried this array:
If((Master Dates>=Date Beginning)*(Master Dates<Date End),Master Transactions,"")
Problem with this is when used in different columns for different date ranges there are large gaps in the generated list...
View 9 Replies
View Related
Jun 16, 2014
I have a pivot table based on sales data and I need to know the 3 salespeople that had the highest sales during the current fiscal year versus the last fiscal year.
I have tried manipulating the pivot table but I keep getting the error message: "the following system error occurred: the requested name is valid but no data of the requested type is found".
View 1 Replies
View Related
Jun 26, 2007
I am trying to create a pivot table macro to append to the end of an existing macro I already created which formats the data prior to the pivot table.
Here's the Pivot table setup info if if I was setting up the pivot table manually:
1) Column C (column header row = "D C/D") to be PAGE
2) Column G (column header row = "MN") to be ROW
3) Column A, B and D (column header rows = "TT", "NT", and "CC") to be COLUMN
Sum of Column E (header GA (DA)) and Sum of Column F (header TFA) in DATA ...
View 9 Replies
View Related
Mar 28, 2014
Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
View 3 Replies
View Related
Mar 15, 2014
Is there any script that I generate 6 numbers of these numbers from the list? so they do not recur after another example 7,12,13,19,26,33
View 2 Replies
View Related
Oct 24, 2008
I have a column of names in Column D
I need code to list these names in column G
when I hit a button.
View 9 Replies
View Related
Jun 21, 2007
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
View 11 Replies
View Related
Oct 5, 2007
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
ex) 1.751
2.52
3.753
418
417
416
View 5 Replies
View Related
Jun 4, 2014
I have a list of names in column A these names repeat themselves a lot and I don't know how many there are.
now I want to make a list of the names in column c starting at C2 that looks down row A finds names and puts them in column C one after the other.
View 3 Replies
View Related
Jul 16, 2008
I have list of various city names in column B. From that i need unique city names in validation cell.
The reason is,
we have emp id, name, designation, area, marital status for our employees. From that if i select emp id from validation cell1 i have to get all employee id's available in the sheet1 is need to come under the validation2 cell automatically. and if i select "area" from validation cell1, all unique areas need to be list out in the validation cell2. Its look likes a filter. but without filter i need this for creating application.
View 9 Replies
View Related
Aug 16, 2008
I need to set up a formula to choose 2 job titles from a single column. There can be 2 to n job titles (non-repeating) and I want to find the title at random. I have no problem finding the first job title using index and randbetween, but I want the second title to meet the same criteria, just not equal to the first job title.
View 3 Replies
View Related
Feb 19, 2007
I have a formula-generated defined list of names. I need to select them in random order without duplication and without choosing any blanks in the list.
View 6 Replies
View Related
Jan 10, 2013
If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
View 2 Replies
View Related
Jun 5, 2014
Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.
View 5 Replies
View Related
Jul 30, 2009
If I have a list in Collumn A such as:
1
1
2
2
3
4
5
5
6
6
Is there a simply excel function I can use so that in collumn B I return only the unique results. That is, collumn B will look like this
1
2
3
4
5
6
????
View 9 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
May 13, 2009
When I make a pivot table I wanted to know why it is showing duplicate names. Here is an example workbook below. It should group all the same names together. However it isn't doing so. Anyway around this problem?
View 2 Replies
View Related