It is a sample of my table to operate on: excel.jpg
My task is to make a list of mobile phone brand and model name – success, fail, reject, keeping in mind that one particular phone could have been contacted several time during the time of the campaign, but it should appear counted in all stats only once (hint: track phones by their unique Bluetooth code).
I am trying to get an Array formula to pull unique values from an ENTIRE table. I was able to write one just for a column but not for an entire table. The unique value column is my desired results - t
View 11 Replies View RelatedI have a Column, say column A, setup with a lot of repeated data. Now what I want is to pick only a single value from each set of data and put them into a separate column. for example:
Col A
1
2
3
2
4
1
5
4
1s
a2
1s
a2
a2
6
What I want the excel to do is to pick from the above data only a unique value and put them into a separate col, like this:
Col C
1
2
3
4
5
1s
a2
6
This is my first post here!
The Question:
This is a hard one, I promise! Is there a way/formula/VBA to return multiple unique values from a table or from multiple columns with criteria?
My Objective
I want to create a table that has unique company names in the first column (done already) and all the connections that belong to a given company in the columns #2....#n. Like this:
Column1 Column2 Column3 ..... Column n
Company X1: Co. ABC Co.XYZ Co.Acme
Company X2: Co. SEB Co. Becme Co. Cecme
....
Company Xn: Co. X1 Co. ABC Co. Acme
I have a small sample that has 20.000 rows and 25 columns of data of 1946 unique companies downloaded from my schools database. This means that doing this by hand would take forever!
The data has many duplicates which makes this complicated. The first column shows company name and columns 2...n present connections. Like this:
Column1 Column2 Column 3 ...... Column n
Company X1: Co. ABC Co. XYZ Co. Acme
Company X1: Co. ABC
Company X1: Co.Acme
Company X1: Co. XyZ Co. ABC
In this example Company X1 is connected to companies ABC, XYZ and Acme. The formula should be able to filter/omit overlapping entries.
I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.
When I set up a basic Pivot, it counts each occurence of the person's name.
I have a worksheet with a list of employees and the workgroup they belong to, along with other data like manager, start dates, etc. Recently a couple of the workgroups were duplicated (change in managers), so these employees are showing up on two rows even though the workgroup has the same name. The only differences in the two rows are the workgroup effective start and end dates. I need to be able to count, in a pivot table, the number of unique employee/workgroup combinations there are per workgroup. I can add columns to the sheet, but it's a dynamic set of data that will grow each time it's refreshed...
View 9 Replies View RelatedI'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.
View 5 Replies View RelatedI have a set of data that I'm trying to identify the unique values in a column and then sum the related quantites against each of those values:
View 14 Replies View RelatedI have data that has a customer name and the month that they were taken care of" (of course not spaces
Customer Month
Smith January
Smith January
Mark January
Suzy January
Smith February
Mark February
I want a pivot table to say:
Month #customers
January 3
February 2
but when I do the count it adds the total number of customer names and gives (it is counting smith twice in January
Month #customers
January 4
February 2
I was reading something about doing the count (so it shows 4) and then doing an index within the count but I keep just getting 1 as the value (for both months).
I tried doing a formula in the document that said =IF(ISERROR(MATCH(A2,$A$1:A15,0)),1,0). basically looking to see if the name is above the location I am looking at and if it is to put a 0 if not put 1 and then do a sum of that column....trouble is that when a customer has orders on more than one month, the second month is coming up with a 0 instead of a 1 (and this happens quite often).
I am not sure what to do. if the customer shows up in more than one month I want them to count towards the unique count of both january and february...we look at each month separately.
I have data set up like the example but then for 1797 lines.
D39BKYes
D39BKYes
D39BKYes
D39BKYes
GHFLBNo
R80FANo
R80FANo
[code].....
What I need to get in a pivot table is: two columns or rows (Yes / No) and the unique count of the code.In this case is should show Yes: 2 and No: 3
How do count unique values in category in pivot table. (my table, im taking data from ms query). I am using excel 2010
Pivot table example: the result i want is the "no of types" as my data only show "category" and "types".
Category
Types
No of Types
Fruits
Apple
Pear
Orange
3
Vegetable
Cabbage
1
I have a spreadsheet with some calculations and the part i am interested in is data in R4 : V58
In this R4:R58, S4:S58 and T4:T58 have some rows that have False because of IF calculations that put False in some rows of R and hence in S and Hence in T.
For example, R15="FALSE" so will S15 and T15 be. Col U is based on whether the corresponding row in R is False or a number. If a Rown in R is false, the row in U= 0 else the row in U is the same as in the same row of a different column - Col I
Col R, T and U are numbers. Col S is text
For example:"
R S T U
False False False 0
False False False 0
False False False 0
55 DEF 3 15
46 XYZ 2 67
False False False 0
False False False 0
23 GEF 4 43
I want a table in another worksheet that will sort through this using a formula and print
Col1 Col2 Col3 Col4
55 DEF 3 15
46 XYZ 2 67
23 GEF 4 43
If I can define things like where the table will be placed in the new worksheet etc, that will be a bonus..But It appears I cannot understand how to do this at all..
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies View RelatedI need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
how to get this with formula:
Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))
Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.
I am trying to populate a tab with all the unique values from a data table. For example, list all the SKUs, SKU Names, Buying Groups, etc. for "Owl Filled Candles" on the "COLLECTION - SKU" tab. For reference, the "VENDOR - SKU" tab works perfectly. I want the COLLECTION tab to do the same thing as the VENDOR tab. The only difference is the VENDOR-SKU tab is pulling data based on a Vendor's name in a drop-down list in B3 and the COLLECTION tab is pulling data based on the Collection name in a drop-down list in B3.
I tried to just copy the tab and reset the reference cells but that isn't working.
I am looking for a formula that matches two unique numbers in a table based on two numbers that are entered in two other cells. This will be clearer by looking at the example below and the attached.
The goal is to find the number entered for the home field (cell c2) in the "home" row (B5:K5) with the number entered for the away field (cell c3) in the "away" column (A6:A15) and result in the data element where the row and columns come together. The expected result for this example in the attached would be "JPD".
Another example would be if a 6 was in cell c2 and an 8 was in c3, the expected result would be "RBK". I think this can be done by indexing and matching but my memory has gone blank with how this works.
I have a list of location names that may be repeated within the list. I also have possible blank cells within the list. I would like to filter them automatically into another table without having to use the autofilter or any macros to keep things simple.
How could i do this by removing blank cells in the new table and having only uniquely listed names ie get rid of any duplicates.
Index Match lookup formula does not find valid entries that exist in the lookup table. Formula: =INDEX(tbl_Costs,MATCH( F8,tbl_Costs_PN,0),MATCH("Cost",tbl_Costs_hdngs,0)). I've verified that the matching criteria and values are, in fact, in the table and have made double sure that all cells are formatted the same. the attached file and show me the error of my ways.
View 7 Replies View RelatedI 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
What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)
So that it will return the value next to the match
(instead of returning gibberish, will return a number instead)
Solved
Okay had that table setup that way because I needed separate tables ("linked" comboboxes")
So I just altered it so that the vlookup range changes dependent on the value of combobox 1.
I have the following data:
TRX_IDQTYCUST_ID892486021-67.991623230892486140-35.411623230892486378-29.271623230892486515-60
.711623230892488006-69.081623230892488082-43.261623230892486668-31.091623230892486794-27
.0562118898892546057-42.829337675892487417-19.331623230892487513-33.251623230892488890-33
.61719407892543537-13.891623142892544321-11.021623142892487602-18.921623230
Would like to know the number of unique customers (CUST_ID) have purchases and the sum of those purchases and how many transactions:
CUST_ID Sum of QTY Count of Transactions
50 $2000 1500
I cant seem to find how to do unique Cust using a pivot table - it just gives me a count of cust which is the same number as the number of transactions.
The pivot table has only two columns, the first is the identification number and the second is the count of the identification number. I am trying to get a count of the number of identification numbers, not how many times it was entered in the spreadsheet (some numbers are entered more than once on different days). It currently looks like:
Column A Column B
00000001 1
00000002 1
00000003 2
00000004 1
00000005 3
Grand Total 8
I'm trying to arrive at 5 for the answer, so that each number is only counted once even if used more than once.
I have 2 columns. One with a store number. The other with an item. I need to get the count of each item for each store. So:
Store, Item
1, Cashier 1
1, Cashier 2
1, Cashier 2
1, Photo PC 1
1, Cashier 1
1, Cashier 2
2, Photo PC 1
2, Cashier 1
2, Cashier 2
2, Cashier 1
2, Photo PC 2
2, Photo PC 2
2, DriveThru 1
3, Cashier 1
3, Photo PC 1
3, Photo PC 1
3, Photo PC 2
I would like to get to
Store, Cashier 1, Cashier 2, Photo PC 1, Photo PC 2,
1, 2, 3, 1, 0
2, 2, 1, 1, 2
etc....
How can this be done?
Found what it think is the correct methodology (Pivot table) for doing this, but I'm struggling to apply it.
I have a very simple excel 2007 document that has one column with multiple rows;
blue
Blue
green
red
red
green
Green
blue
red
Blue
blue
Bleue
Rouge
red
Bleue
I would like the easiest way to count the total of each and sort in highest first,
e.g.
red 4
blue 3
Blue 2
green 2