Bring Top 10 Unique Name Based On Multiple Criteria?
Apr 23, 2013
In the below data set i have the data ranked by branch and center by value. I am trying to create a summary page that looks at the data set by branch center and rank and brings back the debtor code. The letters above headers represent the columns.
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program Clinic Title Start Date
[Code].....
formula that will automatically give me that answer?
I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user.
Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this?
Sub PopulateReport()
Application.ScreenUpdating = False
Dim MyFilter1 As String Dim MyFilter2 As String Dim MyFilter3 As String
MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string MyFilter2 = CStr(Sheets("Report").Range("C4").Value) MyFilter3 = CStr(Sheets("Report").Range("C6").Value)
Sheets("Waste").Select
Dim Rw As Long Dim Rng As Range
Rw = Range("A65536").End(xlUp).Row Set Rng = Range("A1:W" & Rw)
With Rng .AutoFilter .AutoFilter Field:=20, Criteria1:=MyFilter1 .AutoFilter Field:=2, Criteria1:=MyFilter2 .AutoFilter Field:=13, Criteria1:=MyFilter3 End With
how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!
I have data and after doing some data manipulation it looks like this
Job # Part # Lot # Total Grand Total
[Code]..
What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.
Most details are shown in the spreadsheet below. I would like it to be dynamic because the quarterly and annual data dumps I'm working with are are hundreds to thousands of lines.
Have the list be sorted, which is a part of the first attempt, would be nice but is not necessary. At this point, just being able to generate the dynamic list would be fantastic.
Excel 2007ABCDE1product lines:consist of these product subtypes2Widget series:Widget.type1Widget.type23Fidget series:Fidget.type4Gidget series:Gidget.type1Gidget.type2Gidget.type356data dump of parts sold or used in repairs7product subtype repairedpart number
Im just going to start with the tables that will help show what i am trying to do...
TABLE 1 File #Dateid 215? 211? 27? 26?
TABLE 2 File #StartStopid 29131 214162 2183
Ok so say i have these two tables...i want to be able to look at the file # of line 1 in table 1, go to table 2 and return the id where the date from table 1 is between the start and stop in table 2. ie...in that example it should return id "2".
For example, each job has a specific ref number and within that job ref number there is a number of lines of data, some of which we ignore and some of which we include. Each job will have a planning time using an old methodology (Planning A), a planning time using a new methodology (Planning B) and an actual time. There are a few other variables too such as the category of work.
I am trying to count the number of each unique ref no. within the sub category of work I am looking at (there are circa 50 sub categories of work). Each ref no. will only have 1 combination of work cat and sub category.
I've got an interesting problem which I haven't been able to find an answer for.
A B PR Yes PR Yes PR Yes DN Yes DN No DN No
I need to count how many unique values in column a have every row in column b the same value. So counting for yes values, PR would equal one but DN would equal zero.
I used this formula to find out how many unique files had yes values, now I need to find out how many files only have yes next to them.
I am looking to count the unique amount of customers who are listed in column C based on the criteria that they purchased the product on the 1/08/2014 and that the product came from Department 3. The output I am looking for in this example would be 3. I can do it for this example but when I have 300 different customers it starts getting tricky and I'm a bit stumped on how to incorporate an array formula into a countifs() function or whether there is an alternative.
Date DepartmentCustomer 1/08/2014 3 A 1/08/2014 3 B 1/08/2014 2 C 1/08/2014 3 D 4/08/2014 3 A 5/08/2014 2 A 5/08/2014 3 D
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B 2366 2005 T 2 (as opposed to 3)
A B C D T 655 2005 2366 T 656 2005 2366 T 656 2005 2366 W 659 2005 2367 W 659 2008 2369 W 659 2006 2370 F 659 2005 2370 W 660 2005 2370 W 660 2008 2371 W 660 2006 2371 T 660 2005 2371 W 661 2005 2372 W 661 2007 2372
What I am trying to figure out is how to populate cells N4:N5 and N8:N9 for unique locations by Fruit & Vegtables by Area A and Area B. I just have some basic data for this example as the spreadsheets are 30,000+ lines long.
I have two ranges of data and want to count the unique number of times the values in column A occur if they have two values in column B. For Example:
ColA ColB 333310143333306344441014444430635555161246666101466661612466663063
I want to count the number of times value in ColA has the value 1014 AND 3063. My intention is to create a table that cross references the two
10143063161241014331306333116124112
I searched and found answers where multi criteria were in multi columns but I can't seem to find a solution to this means of find the unique count in ColA.
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B 2366 2005 T 2 (as opposed to 3)
A B C D T 655 2005 2366 T 656 2005 2366 T 656 2005 2366 W 659 2005 2367 W 659 2008 2369 W 659 2006 2370 F 659 2005 2370 W 660 2005 2370 W 660 2008 2371 W 660 2006 2371 T 660 2005 2371 W 661 2005 2372 W 661 2007 2372
I have two spreadsheets in the same workbook, on the first worksheet I would like to bring in certain values from the other worksheet if certain criteria is met.
On the first worksheet
Column A = G/L account Number Column C = Is where I would like the amount brought in from the other worksheet column D = The percent number I would like to bring in from the other worksheet
On the second worksheet
Column A = G/L account Number Column C = Amount Column D = Percentage
The criteria to use to determine if the number should be copied to the first sheet is as follows
Column C on the second worksheet if the value is greater them or equal to 1,000.00 (positive or negative) and Column D the percentage is greater or equal to 10% (the cells for this column are not formated has percentages)
For example"
GL account number 4000000-0 the value in column C is 1500.00 and column D is 15.19 then both numbers would be entered into the first worksheet in column c and d foor the row where the gl account number is 4000000-0
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............
I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
I'm facing a big problem and I can't found a solution for days.
I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
Let me Put to you an example for you been able to understand my problem.
Example considering Range "C2:C20" exluding blank cells
(1) - To count unique values =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
(2) - To count unique values based on criteria in range "B2:B20" =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))
(3) - To sum unique values on "E2:E20" based on same criteria =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))
They all work fine. But I need to insert a merge at Row 10 for better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's. I just don't know what to do guys ....
Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.
I need to update the master workbook with data from the invoices workbook using the following criteria.
In the master workbook column C has unique job numbers. In the invoice workbook column F has unique job numbers. Both worksheets are the first worksheet in both workbooks.
If the job number in the invoice workbook is already in the master workbook do nothing.
If the job number is in the master workbook but not the invoice workbook then delete the entire row from the master workbook.
If the job number is not in the master workbook add it to the master workbook.
To complicate matters the master workbook is shared. It is my understanding that shared workbooks can't contain the macros needed ? It would be preferable that the master sheet had them but if not ...
In one sheet I'd like the user to select from 3 dropdown lists certain predefined values.
On the second sheet there is a long list of unique cells (one column, that can't be split into usefull columns with text to columns or something). I want to find the cell that holds the three text choices. These can be in different order to make things more complex. How do I create a search that finds that one match.
Example The user selects "AAA" and "DDD" and "FFF" from the dropdown lists
The formula should find that one cell that holds this value: "FFF JJJ GGG DDD CCC AAA". This is the only cell that holds all three chosen values in one text. In the end I would need to have the row number of that cell,
I have a challenge I haven't quite been able to figure out:
I need a 6 digit trace code to be automatically generated for each job based on the date, the product type and the job number.
The first 2 digits on the trace-code are alphanumeric and the sequence for each job is AA, AB, AC etc.
The last 4 digits are a date code (YYWW.... two digits for the year and two digits for the week number).
The criteria is that multiple jobs of the same product type within the same work week must not have identical trace codes. So Job 1 gets "AA" plus the date code... Job 2 gets "AB" plus the date code, etc. If Job 3 is done in the next work week, it cycles back to "AA" and gets the new date-code.
Generating the date-code was simple enough, but coming up with a function or rule to assign the two-digit alpha has been a challenge that I have not been able to beat.
Below is an example of how the Trace-code column should work....note that Trace code on one product type might be identical to the trace code on another product type.
2.) ... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2
3.) ... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2
4.) ... Column H <>"" and Column I >=A1 and <=A2 and Column J =""
A1 - user defined (start) Date 1 A2 - user defined (end) Date 2 Column B - 6 digit number (or blank) Column H - Date 3 (or blank) Column I - Date 4 (or "expired" or blank) Column J - Date 5 (or blank)
See attached file, basically need a formula that will give me the unique resources for each location. I know a pivot table would be easier but i need a formula for my model to work.
Second Tab Account Name Account Number Account Ship Location Number
On the first sheet each account name and number only appears once.
On the second sheet there may be multiple entries for each Account Name and Number.
On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
I am looking for a formula which will allow me to count unique values in a database, based on multiple criteria. Sample file attached (Formula required in cells J and K).
I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.
I have tried different formulas, but fast running out of time
I have attached a modified sample from the main spreadsheet
I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.
i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results
LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row Application.ScreenUpdating = False For i = 26 To LastRow Target = Sheets("design owb").Cells(i, 10) With Sheets("design") For J = 2 To 54 For K = 4 To 11 If Target = .Cells(J, K) Then Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | " End If Next K Next J End With Next i Application.ScreenUpdating = True
