I've found part of my answer from searching for a previous thread and altering to suit my needs.
I've got the array formula:
=SUM(IF(FREQUENCY(IF(D2:D20000"",MATCH("~"&D2:D20000,D2:D20000&"",0)),ROW(D2:D20000)-ROW(D2)+1),1))
This gives me the correct number of unique entries. What I'm after is the number of unique entries that start with 1042
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
I am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.
View 6 Replies View RelatedI need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.
View 2 Replies View Relatedi need to filter and delete duplicate values. where i have some issues is that i just need keep the first entry and delete the second (and so on) entry. means when i have a list with ids and hours like that:
id1 hours1
id1 hours2
id1 hours3
id2 ...
i need to have id1 and hours1 but delete hours2 and hours3. hours is the result of a formula. i tried with advanced filter but it somehow just hides me the duplicates and it sums me the hours.
ID = 1: The B Number is the same for entries of ID =1. Thus where ID = 1 and Last is indicated, Cost = 10.
ID = 2: The B Number changes four times. In cell G20 (Last of all ID =2) Cost = 20+30+40+30 = 120. You are basically summing at each instance the B Number changes.
ID = 3: Cell G24 = 100 +30 (two instances of B Number changing)
ID = 4: B Number is always the same. Cost = 50
ID = 5: B number changes 3 times, Cost = 50+120+140 = 310
In essence, the idea is that if the ID matches for all rows of particular client, the total cost = individual cost. However, if the ID changes multiple times for a single client, the total cost becomes the sum of changed costs, but not the entire column, just summing at each instance it changes. And this summed cost must be entered whenever we see the last instance of a unique ID (This is indicated using Column F).
The problem that I'm running into is that currently I am doing this manually with my actual data set (has nearly 200,000 rows). What excel function or VBA code could I write to automate this entire process?
I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?
View 5 Replies View RelatedI need a unique count of the ID number. What I need is to check and see if the row has a number in it and if it is greater than 0 and is not blank. If the row has 0 in it, then that is not counted in the count. For in stance .....
View 9 Replies View RelatedIs there a way to make Advance Filter show the most recent (highest row)unique entry instead of the first entry (lowest row)?
For instance if a spread sheet has
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
I want to keep the "1 3", "2 3", and "3 3" entry, not "1 1", "2 1", and "3 1".
I'm making a user form in excel/vbe, that will enter data into a worksheet behind the form. I would like to make a unique reference number that is automatically generated by the form as the next in the series. It can be as simple a number as 00001 so nothing too complicated I hope.
When the user opens the user-form i would like the reference to appear on the form so that they know what it is. Then when they enter the data I want the number to go in the database with all the rest of the info they have entered and also for a message box to come up reminding them to rememeber their number.
Can someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).
Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)
I've an excel sheet with 1000 rows data. I need a formula to count number of unique items in a column with a condition. Please find a sample data sheet.
I've tracking list that has ordered and delivered items for a given month on weekliy basis such as Week1(W1), Week2(W2), etc. I need a formula to count number of unique item delivered on this month.
Item 1 to item 8 delivered and item 10 also delivered in week 4, So totally I need to get the result as 9.
I was trying to use "countif" for validating and counting each row, it's throwing value! Error =1/(COUNTIFS(A:A,A2, c2:c23, ""&0)) i.e., = 1/countifs(unique items from column A, then nonblank cells in column C) but it's giving Value error..
I just need to count the unique item with another condition.
Item details
Order week
Delivered Week
Item 1
W1
W1
Item 2
W1
W1
[code]....
I would like to check if there's a way to count the number of unique value with a given condition
Eg. condition where parcel count <> 0, also want to find out the unique value of district and postal sector
Attached is the files, and my desired result is highlighted in RED.
I need to count all unique entries in column A (list of names) (this bit I can do!)...however:
Column B gives each name a value of A or B, I need to count all unique names with an A and all unique entries with a B.
eg
Matt A
Matt A
Matt A
Bob A
Tom B
Tom B
This would give a count of 2 for A's and 1 for B's.
Could someone let me know if my suggested formula below will calculate the number of Unique records that represent either an A or a B.
=SUMPRODUCT((B5:B14="A")/COUNTIF(A5:A14,A5:A14&""))
Although it is giving me the right answer here I am not confident it is correct.
I'm looking to get a formula to counting unique values listed in a column depending on a condition also find the attached file for more details
[URL]
I need to count the amount of unique values in a range, but only count unique values that also have a seperate condition. To illustrate with the 2 column dataset below....
Colour Letter
Yellow A
Yellow A
Green B
Yellow B
Blue A
Blue A
Yellow C
For this set of data i want 3 seperate cells for each 'Letter' telling me that, for 'A', the answer is 2 unique values (i.e. Yellow and Blue). For 'B' there is also 2 unique values (i.e. Green and Yellow) and finally for 'C' there is just 1 unique value (i.e. yellow).
I have a test sheet thus :
+ABCDEFG
1Bob11234
2Sally2
3Ann3
4Emily4
5Lexi3
6Bill2
7Josh1
8Jacob2
9Mary3
10Gary4
11
In D2:D11, I want a non-duplicate list of all the "1" names, i.e., Bob and Josh. In E2:E11, a list of all the "2" names (Sally, Bill & Jacob), and so on. Sorted would be nice, but not necessary. When new names and their numbers are added to A1:B200, they need to automatically show up in D2:G:201 as appropriate. I've looked over VLOOKUP, INDEX, MATCH, etc., but all seem to simply return the first and not every match. Maybe I'm just missing it.
Is this possible without VBA? Not that I have any objection to it, but delving into and learning it is a little outside the available time I have now. If it takes VBA I'll address it when I can free up some time.
Formula which will count unique values in column A based on condition in Column B which "y" .....
Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....
Counting Unique text entries in a sheet with a condition ..
View 14 Replies View RelatedI need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.
Company Name
Company Type
Company A
SME
Company B
SME
(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies View RelatedI used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
View 4 Replies View RelatedI am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
say sheet 1 has 2 collums A & B
collum A is Names Collum B is Dates
A B
Bob Fenton 05/04/08
Rob Smith 05/06/08
Al Feth 05/08/08
Al Feth 05/18/08
Al Thomas 04/23/08
Rob Smith 05/23/08
Bob Smith 04/22/08
Bob Fenton 05/15/08
Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
so sheet 2 would be like ....
I'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?