I would like to get the count of unique values in my 2nd column using my values in 1st column as the criteria. Below is my example of my data set.
EFGH5DeptHC
6A304794A17B86122B38B86179C39B90050D1310C82705
11C94955
12C308165
[Code] .........
Excel 2007
I would like to see the count on column H (highlighted in yellow).
I am using Excel 2007
I have 50 000+ rows that covers fiscal years from 2006 to 2014 and I want to calculate how many unique clients are there per asset type per fiscal year. In addition, the results table needs to adjust to filters i would use on the raw data..
As:
2007
2008
2008
Commodity
45
FX
Other
other
Here is a sample file: SampleData.xls
[Excel 2007]. I'm trying to get the list of unique values from a Filter on a column in VBA.
I've given the column a Range Name
Code:
Dim rClient As Range
Set rClient = ws.Range("CLIENT_NAME")
Dim aClients() As Variant
aClients = rClient.???
There's nothing in the range method dropdown that obviously relates to the Filter on the range.
Is it something to do with the Table method?
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF
Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1
I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
View 4 Replies View RelatedI 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 worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.
Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View RelatedIf 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'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.
=COUNT(
1/FREQUENCY(
MATCH($A$1:$A$50,$A$1:$A$50,0)
),
ROW($A$1:$A$50)-ROW(OFFSET($A$1:$A$50,0,0,1,1))
+1))
I'm looking to tweak this formula to count the number of unique values that occur at least 3 times.
So, imagine that A1:A50 contain 50 last names from the phone book, 27 of which are unique according to the formula above.
Now, further imagine that only 5 of these unique 27 last names appear at least 3 times.
I'm looking for the formula that returns 5.
And (like always), for simplicity and sharing reasons, I'm hoping to find a single formula that occupies a single cell, and does not rely on additional columns of intermediate information.
I'm working with a data set (attached) that tracks financials at both the job and item level (in my customer's data - a unique job can be made up of a single or multiple items).
I've successfully built a formula (in column C of the attached) that counts the total number of items and their associated $ values that were invoiced in a given month based on my customer's requested item-type categorizations (i.e. "Exact Match", "Similar", "Inferred", "Cost-Plus").
The problem I'm running into is building a formula that counts the first unique instance of the Job ID associated with those items. For example - in cell C33 on the attached - The total number of items invoiced in June that were "Exact Match" was 10. These items were spread across 9 unique jobs - and that's where I'm struggling - to build a formula that only counts the first unique instance of the corresponding Job ID in Column C.
The formula I'm having trouble with is located in Cell D12 on the "Dashboard" tab of the attached.
The formula as I have it is returning a result of 8 - because it's summing the "1"'s in Column I on my "Invoice Data" tab - however it should read 9 (Filter Column Q on the "Invoice Data" tab to "June", Filter Column AQ on the "Invoice Data" tab to "Exact Match", and then sum the unique job IDs in Column C).
Excel_Forum_Example_Dataset_7.24.14.xlsx
So here is my dilemma and I haven't been able to find anything to exacly help me out:
I have a large (100k+ records) sheet of sales invoice details.
I need to add a total item line per invoice number to each record ....
Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274
I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.
I want to count data that corresponds to 4 criteria for data located in Columns B, C, D and E. However, I only want to count only the unique values in Column A. How do I improve on this formula?
=SUMPRODUCT(($E$2:$E$5=J$2)*($D$2:$D$5=I$2)*($C$2:$C$5=H$2)*($B$2:$B$5=G$2))
See data below
Is there a formula that can be used to count all unique values in a specified column.
Example:
Col. A
123
123
Test
ABC
ABC
ABC
Result: 3 (since there are 3 unique values in col. a)
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'm having some trouble with a spreadsheet copy.
I have a spreadsheet listed out with 2-3 pieces of data per row like:
Mark - abc
John - def
John - ghi
Abby - xyz
etc.
What I need to do is create a new file for each unique string in column A. So for my brief example above, the "Mark" file would have 1 row, the "John" file would have 2 rows, etc. I also need to save the new file by the name in the first column (so "mark.xls"). (Conveniently, the table is sorted by the first column already.)
What I did was create a new worksheet for each row and then save that as my file. However I'm having problems getting my file to contain more than one row (I can get either the first or last row, but not all of them). I have about 30 spreadsheets to do this for, and each will create 80+ files... I'd really rather not do them by hand.
I'm on Excel 2007 if that matters.
I cobbled together some code I found in various places and this is what I have so far:
Code:
Sub CreateWorkbooks()
Dim newSheet As Worksheet, regionSheet As Worksheet, CurSheet As Worksheet
Dim cell As Object
Dim regionRange As String
Dim lngRows As Long
Dim CurRow As Long
[Code]...
(note that the comments are what it is supposed to be doing, not necessarily what it is doing)
Column A has following names: A5 Peter A6 Sally A7 Andre A8 John A9 Wayne Column B has following names: A5 Andre A6 Wayne
I need Column C to show names that are in Column A but not in column B ...
Column C should show following: C5 Peter C6 Sally C7 John
I am struggling with this in excel 2007
I am wanting to find rows that with unique values in the first column, and then take those rows and display them in a more readable format. The section titled input is an example of information that would be pulled from a query into Excel. The Desired Output section is how I would like the info to be displayed. If this is possible without VBA then that is even better.
Excel 2007
A
B
C
D
[Code].....
I have a spreadsheet that holds customer information. What I want to do is find how many customers there were last month. I have a cell (C1) which has last month displayed as 2009/05. A1 holds the customer ID and B1 holds the date they used us. Each customer may has used us many times and I'm having a nightmare trying to solve this.
View 8 Replies View RelatedHaving a large spreadsheet with Columns A to F filled with data.
I'm trying to create a formula that's count the unique value "Order no." (Coloumn F) but only the ones between certain Departments Column(H) a certain "Error type" in Column(J).
I want to count the unique values between 2 dates. He only needs to count the values that match 2 criteria. I want to know how many unique orders were place between 01/10/2012 and 30/09/2013.
In my Data sheet:
Colomn X: Customer name
Colomn D: Status
Colomn AH: Date of order (Day/Month/Year)
I want to count the unique values in Colomn O: Ordernumbers
This is what I already have:
=SUM(--(FREQUENCY(IF(Data!$X:$X=$A4;IF(Data!$D:$D="BOOKED";Data!$O:$O));Data!$O:$O)>0))
This formula is working. He counts the unique values in colomn O that meet the 2 criteria (Status = "BOOKED"; Customer = A4).
But I don't know how the add the date range in this formula. I want him to take only the values that are between 01/10/2012 and 30/09/2013.
I have a large list of data and need to filter unique values of 1 column with a criteria.
For example:
A B
Car 1
Bike 1
Car 2
Car 2
Bike 3
Bike 3
Bike 4
Car 4
I want to filter out only the lines whereby the column A & B is the same comment/value.
Other words: both column A & B have to be unique values.
Based on the list above this means that the list should look like this:
A B
Car 1
Bike 1
Car 2
Bike 3
Bike 4
Car 4
Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)
I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...
Can DCOUNTA be used to only count unique values?
If the above is not possible how would I do the following:
Criteria
Column H = text1 AND Column B date is between date1 & date2
Count unique values in column C
I am using Excel 2007, and I'm stuck with this problem.
View 4 Replies View RelatedI need to count the number of unique names from a column of filtered text. On the attached example D5 is where I need the value, the current array I have does not work when the filter is applied by changing the product in drop box. I need to have a count of the unique account names from column A.
View 3 Replies View Related