# Pivot Tables - Finding Number Of Individuals With Particular Filter Combination?

Jan 30, 2013
I am using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".

So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?

Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.

The purpose of this data is for reference to question #2, so that i can utilize some percentages.

View 1 Replies
ADVERTISEMENT
Nov 10, 2008

i have a pivot table report filter that i cannot sort a-z. i've tried sorting the source data and also right clicking on the first value then choosing display a-z - neither work.

View 5 Replies
View Related
Jul 31, 2006

I want to create multiple pivot tables each performing its own tasks. When i want to filter a particular category in all first pivot tables i have to do this one by one.

This is time consuming and i think it can be done faster. Is it possible when i filter a category in pivot table 1 that this filter is automatically filtered in the other pivot tables?

View 9 Replies
View Related
Jun 15, 2012

I am trying to update multiple pivot tables from different OLAP cubes based on the same cell value that an user defines,

namely one country for which he/she wants the create the report for. The code I recorded goes like this:

VB:

Sub TUR()

Sheets("Pivot").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Geography].[Geography]"). _

ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Geography].[Geography]"). _

CurrentPageName = "[Geography].&[TUR]"

End Sub

In this example, TUR stands for Turkey.

Basically, I have over 20 countries and at least 3 different OLAP cube-based pivot tables in my report, I can do it with 3 x 20 different macros but that seems like taking the long way.

View 2 Replies
View Related
Mar 3, 2014

By using this function I have managed to do a 1000 sheet report, 1 for every customer.

However, my problem is that I don't know a way of globally setting the print area for every single sheet.

Also, the reports are not exactly the same size, they will vary from 5 to 12 columns.

View 1 Replies
View Related
Apr 30, 2013

I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?

As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.

Before filtering:

Study ID

Study Short Title

Study Patient ID

Area

1346

LLP

90126

Northwest

[code]....

View 8 Replies
View Related
Nov 6, 2008

Is there a field setting in pivot tables to return the number of unique records?

Is there an excel formula to define a range of cells, and returns then number of unique records: (i.e =Unique(A1:A10)?

View 9 Replies
View Related
Mar 23, 2014

I need to find the which two columns that give the greatest combination.

Row 2 shows some possible combinations, a total of combinations is 21.

Attached File : test.151515.xlsx

View 12 Replies
View Related
Nov 13, 2009

I've been assigned a task of finding a combination of three or four machines. For example AD,AJ,AQ, and AB would equal 7 therefore it would be the best combination of workstations for that cell. However, I'm having an issue that if AD, AJ, AQ, and AB are being selected more than once.

My question is, how can I analyze all the data and determine the best combination given the relationships for each row given the column.

3 = Absolutely Necessary

2 = Extremely Necessary

1 = Necessary

0 = Do not associate

View 14 Replies
View Related
Dec 14, 2012

The goal of this is to find the best combination of a captain (out of 5 possible choices), 5 crewmates (out of 25, no repeats), deck items (2 items, can be repeated, 20 total options), ram (1, out of 13), hull (1, 13 choices), and rudder (1, 7 choices)

Each choice in every category has 4 attributes - fighting, morale, seafaring, and speed. The ship parts also have a true/false field called "unlocked" - the false fields obviously can't be chosen.

A mission gives numbers saying what total attributes are needed for a 100% chance of success. If the mission has 2 attributes that are required (say seafaring and fighting, for instance) then it takes whichever chance of success is lowest and counts that as the mission success chance.

Example: A mission requires 2000 fighting and 2000 seafaring - I can reach 2000 fighting but 1500 seafaring, chance of success is 75%. It would be better for me to get a combination that gave me an 87% success rate in both fields.

How do I have excel go through every combination (and is that even plausable? I would imagine that being in the billions of combination choices) and highlight (turn a cell green or something) the combination that has the highest minimum chance of success in all fields?

View 2 Replies
View Related
May 19, 2009

I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.

View 4 Replies
View Related
Jul 10, 2009

I am having some problems trying to filter a list to display exactly what I want to see. The list has one column of part numbers, a second with due dates, and then another with quantity.

I want to use an advanced filter on the part numbers to only look at unique entries. Then I want to filter that list using a custom filter on the due dates to only view those due within a certain period. So ultimately I want to view only unique entries due during a given period.

I am able to apply one filter, but when I go to apply the second, the second filter removes the first. For example once I have filtered out duplicates, when I try to filter based on date all of the duplicates return.

View 7 Replies
View Related
Aug 16, 2013

I have a pivot table in the first sheet which includes the field "Date" as a column label.

In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.

I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.

note that the field "Date" is used as a Column label, i.e., it is not a Report filter.

View 3 Replies
View Related
Jan 21, 2013

I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.

Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.

Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.

View 4 Replies
View Related
Jul 25, 2006

I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these

Sub Macro1()

ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _

"PivotTable1"

End Sub

View 6 Replies
View Related
Sep 5, 2006

Is it possible to create pivot table from another multiple pivot table.

Example: I have two diff pivot table "Income" and "Expense" as well

and I need to preapare new pivot table using with those two pivot table

View 3 Replies
View Related
Mar 5, 2014

I have 12 months worth of data in twelve separate workbooks each with a pivot table, I have copy/moved the pivot table tab from each into one workbook so now I have a workbook that consists of 12 tabs each with a pivot table. What I would like to be able to do is create a summary table with the full years data; where I am running into problems is that each months table has slightly different row and column counts and labels making any formula like =sum([sheet 1 cell a1]+[sheet 2 cell a1]) problematic.

View 3 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
Jun 19, 2008

I've got 4 pivot tables (all derived from the same base data) on 4 separate worksheets. I've been able to (with this help of this site) to use VBA to hide pivot items on all of these sheets using a list on a user form. Hide/Show Pivot Table Field Items. Hide Pivot Table Fields Pivot Items by Criteria

I now need to be able to show all the pivot items on only 3 of the 4 pivot tables, with the 4th pivot table being left untouched. For ease assume that my sheets are sheet1, sheet2, sheet3, and sheet4. The tables I wish to update are on sheet2, sheet3 and sheet4. The pivot table on each sheet is called "PivotTable4" and the pivot item is called "Business". The pivot item contains 12 business names (Business1, Business2 etc etc)

Is there an easy way of doing this? I've spent the day looking through the internet and various "Dummies" books but with little success, I fear that I'm obviously below even Dummy level

View 5 Replies
View Related
Dec 21, 2012

I have letter and number combination code in two collumns and they differ for 10.000 numbers:

BAM98314

BAM88314

BAM90000

BAM80000

As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.

View 2 Replies
View Related
Aug 9, 2012

in finding duplication within different worksheet tables using excel VBA.

Sheet 1 has the following table below:

A

B

C

D

E

F

G

[Code]..

The format of both tables is different within both of the sheets.

The desired output should be shown in sheet 2 table:

A

B

C

D

E

F

G

[Code]..

The above table (sheet 2) shows duplicate rows (bold) from comparing table in sheet 1.

I was using this function below, however this does not give me the desired output i am looking for.

=IF(AND($F2=Sheet1!$F2,$H2=Sheet1!$H2,$E2=Sheet1!$E2,$G2=Sheet1!$G2),Sheet1!I1,"").

View 7 Replies
View Related
Jul 14, 2014

I have two tables, both contain a column of ID numbers and a column of addresses.

I would like a method to match the IDs from table1 to those of table2 - if no matches are found, search for matching addresses, if a match is found then output the ID in table1, the address, and the corresponding ID in table2.

If still no matches are found, output the ID and address from table1 and indicate that it didn't match.

Then I want to check table2 records - i.e. search for matching address between the records and output the table 1 ID, address and table2 ID were partial or no matches are found, as above.

View 3 Replies
View Related
Aug 25, 2014

Any easy way to retrieve data from another table using an array formula.

I have two files that I am using that contain 2 sets of data with columns for name, address, city, and state. The red highlighted data needs be used to find the blue data first column, which is a possible name for the company found from the red data. The issue is that the blue data is larger and has rows of data that will not be found in the other table.

I have the spreadsheet attached. I attempted to use an if/and statement :

=IF(AND(D2=C8I:I,E2=J:J),H2,0)

but found out that it does not work with arrays and only found the first address by default.

How would I set up a formula to retrieve the possible names using criteria from the blue data such as if the address and city match, then input that company name?

View 5 Replies
View Related
Mar 31, 2008

I have a worksheet with two distinctly different tables of data. Is it possible to apply autofiltering to a column in both tables? Applying to one is fine but when I try to apply a filter to the second table the filter on the first one is canceled.

Aligning the tables so that the same column in both tables is the filtered column is not possible with the worksheet setup.

View 5 Replies
View Related
Mar 14, 2014

1

0

0

4

6

=largeodd(a1:e1)

=largeeven(a1:e1)

write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.

[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]

View 9 Replies
View Related
Jan 26, 2009

I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9

it would look like this

4 0 3 9 0 4 3 3 2

9 2 5 6 5 6 9 6 6

8 9 9 3 1 0 2 9 8

1 6 7 5 9 9 8 2 5

2 7 2 2 2 8 5 1 3

0 1 4 7 4 7 6 0 9

3 5 6 4 3 3 0 4 4

7 8 8 1 8 5 7 8 7

5 4 1 8 6 1 1 5 1

6 3 0 0 7 2 4 7 0

each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.

View 9 Replies
View Related
Aug 22, 2006

Can Excel work out every possible combination of 3 numbers from a numbered set 1-24.

I need every combination of 3 numbers.

View 3 Replies
View Related
Jul 17, 2014

I have this problem which I have simplified below:

Sheet 2 contains a Pivot table. One of the report filters is "Month-Year" (Eg, May-14 - in date format 'mmm-yy') which is selected by a Slicer on sheet 1.

Cell D1 on Sheet 2 contains a formula which calculated/displays the mmm-yy of the same period last year (eg May-13).

I want a code so that when I select the 'Month-Year' Slicer to filter the pivot table on Sheet 1 (eg to May-14), another Pivot on Sheet 3 is filtered with the previous year mmm-yy (May-13).

View 5 Replies
View Related
May 3, 2012

I want to convert a Lexicographic Index Number, better known as Combination Sequence Number (CSN) to a combination using an EXCEL formula.C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.

I have a lookup table in cell M1:R56 which holds the correct data, that I am pretty sure about.

In cell G1 I have the Lexicographic Index Number I want converted to a combination.

In cell J2 I have the value 39 (n).

In cell I2 I have the value 5 (k).

In cell K2 I have the formula =COMBIN($J$2,$I2).

In cell A1 (the first number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1,$M$1:$R$56,6))

In cell B1 (the second number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,5))

In cell C1 (the third number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,4))

In cell D1 (the fourth number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,3))

In cell E1 (the fifth number in the combination) I have the formula:

=IF(G1=0,"",$J$2-($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,1)))

This works for the vast majority, but gives an ERROR when numbers 35,36,37,38,39 are in the combination, but funny enough NOT all the time.Lexicographic Index Number 575757 which is the maximum combination gives ALL ERRORS.

View 9 Replies
View Related
Jan 4, 2014

I have a table that contains all my data and would like to filter it by company into their own tables that will stay up to date with the main table and then hopefully somehow calculate their investment returns (future stage). I thought Microsoft Query would work but I came across a problem see my other post MS Query returning data to excel some columns I cant sum looks like text

Excel 2010

View 1 Replies
View Related