Counting TYPES Of Duplicates
May 23, 2014
I have a dataset that has a good number of duplicates, many of which have more than 2 records.
In these sets of duplicates, there are two fields that have many combinations of values.
I would like to find a way to COUNT THE COMBINATIONS in all of the sets of duplicates, as in, every time there is a type of a certain combination of values, create a count for it:
12345 9 9
12345 9 0
123456 9 9
123456 9 0
(Total: 2) For 9/9 and 9/0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
(Total: 2) For 0/0 3x
I have attached a listing of types of combinations that I found by scanning the database, but I don't know what formula or functions would do the trick. I don't think there is something in Subtotal or the Count functionality that would apply but I will fumble around.
View 11 Replies
ADVERTISEMENT
Jan 1, 2014
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13
H1 Total of matching numbers in cell range K1:P11
View 3 Replies
View Related
Dec 24, 2008
I currently have a spreadsheet that grabs data from 10 columns using vlookup.
This data is transferred to the new sheet in individual cells. (see attatched)
what i want to do is create a list from them cells, filtering out duplicates & counting them.
Creating a list like this:
720x560 = x5
452x282 = x3
etc.. etc...
I've tried everything i can think of, but i'm imagining that its a vba job to do what i'm asking.
I'd appreciated it of someone could point me in the right direction.
View 7 Replies
View Related
Sep 10, 2008
How do I count the number of air handling units, "AHU" column without counting the duplicates........
View 9 Replies
View Related
Nov 27, 2007
i need to put serial no. without considering duplicate value.. i have ' N ' of value somewhere data is repeating when i am giving serial no i want to ignore duplicate value ...example given ... please see attached file..
View 4 Replies
View Related
Jan 20, 2010
This is a follow-up of my previous post which was brilliantly solved. I found out that I need someting additional, which is over my head really.
I know have the following formula: IF(SUMPRODUCT(($G$4:$G$3000=$G6)*($AA$4:$AA$3000=AA6)*($O$4:$O$3000=O6)*($I$4:$I$3000=I6))>1,....
However, I want to change the end into something which can count the number of appearances of the duplications in a specific row, so I would get results saying that they appear 1 time, 2 times, 3 times or 4 times (4 being the most) in the list and could be placed after the text value of O and I (I398&" "&O398,"-"))?
I hope I formulated this well enough....
Also, does anybody have an idea how I can message my file? I've got close to 3000 rows all the way up to AB and my pc now starts to complain...
View 8 Replies
View Related
May 13, 2014
I'm trying to take column A (number of records can change from time-to-time and may contain blanks), copy that to column B less duplicates and then use a count forumla to count items in column B based on original list in A. I am aware of how to do this in Excel but am interested in VBA.
Example
Column A
Jim
Jim
Mike
Jeff
Jeff
Jeff
[Code]...
View 4 Replies
View Related
Oct 17, 2013
I found this formula =SUM(1/COUNTIF(A1:A17,A1:A17)) onlinesometime ago and have been using it regularly. It counts the number ofduplicates in a range of cells. I have been trying to modify this formula sothat it only looks at the first two characters in the cell. So, instead 7duplicates it would return 5 duplicates because the formula would only belooking at the first 2 characters in the cells.
A
1
GD020T01B02
2
GD020T01B02
3
GD020T01B02
[Code]...
View 2 Replies
View Related
Feb 15, 2013
I found this spreadsheet on here and I have been trying to customize it to what I need. I am trying to have scores from skins match highlighted. I want only the minimum score to be highlighted but if there is another duplicate minimum score I don't want it to highlight anything. I also need to find a way to count the skins won by each player and have it off to the side.
For those not familiar with golf a Skin is a game where you try to get the lowest amount of strokes on a specific hole. Ex- 4 people play the hole one. P1 scores 4, P2 gets a 3, P3 and P4 get 6. The skin would go to P2 who has the lowest score on that hole.
Highlight lowest number in each column not highlighting if there are duplicates starting at L6 down to L11 and for each column till AC. And the same for the group just to the right on attached file.
On row 13 and 14 it tells me who won a skin. I want to tally up the total skins won by each player. so if Joe's names shows up twice on R14 I want it to tell me somewhere in the sheet Joe = 2
View 6 Replies
View Related
Sep 9, 2009
I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.
View 8 Replies
View Related
Sep 8, 2009
I have a problem concerning datatypes for an if-loop, the failure message tells me "Datatypes incompatible" for this code-line:
View 14 Replies
View Related
Jun 17, 2006
some of the functions in this file, and the problem is that I have one bug I can't fix.
There are four sheets. The first two sheets contain different types of scores.
The fourth sheet ranks each of the different types of scores on both first sheets. The third sheet reports out on the bottom five scores in each category. If one of the scores is missing, the whole thing gets screwed up.
I have attached the file and removed a some of the scores to illustrate.
View 9 Replies
View Related
Mar 8, 2012
As a complete novice....I want to:
1. Create a list of 3 different types of projects
2. Write three different types of calculations that should be undertaken dependent on the type of project. Each will be in a separate worksheet.
3. Make sure once I've checked the project type I apply the right calculations that match the project type
4. Summarise the results of the calculations against each of the projects
Can I do this just in excel or do I need to use macros?
View 9 Replies
View Related
Jun 23, 2008
I want to sum the values of two userform text boxes however I assume they are stored as strings so I get 1 + 2 = 12 - how do you convert strings to integers in VBA? It seems as though it's different to VB where you'd just use convert.toInt16() etc
View 9 Replies
View Related
Sep 5, 2008
I have portion of a macro that works when I specify a range like this:
ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets(A).Range("O5:T41"), _ PlotBy:=xlColumnsEnd Sub
But I need one of the coordinates to be changeable on eacj round of the macro so I wrote this:
ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets(A).Range(Cells(5, 15), Cells(B, 20)), _ PlotBy:=xlColumns
But when I executed the macro, it froze on this lines saying "Method of 'Cells' Global Failure. I need a way around this but I have no idea how at this point.
View 9 Replies
View Related
Aug 11, 2006
I want to have an array where each record contains a name and three numbers. My understanding is that Excel requires all element types within an array to be the same, and so I turned to a user-defined data type. But I don't seem to be able to create a dynamic array out of this type--when I try to ReDim Preserve it, I get an error saying that it's already defined. Can't I do that?
View 3 Replies
View Related
Apr 16, 2014
interpolating numbers in excel for an exponential function. I want to know how to be able to curve a curve in 4 different ways essentially the 2 that make up the two halves of a peak and the 2 that make up the two halves of a trough. I've tried using the LINEST function to create this interpolation and it's good to get 1 of the 4 curves I'm interested in. how to do the other 3? I'm also interested in knowing if these functions "whip the tail up" as in almost like how the tail end of a "sin" function flips back up. here it is.
[URL]
For this example of the exponential function, it creates the curve in one particular direction. I was wondering how I would do it to have it curve in another direction, for example I was testing the same function, but going downwards, and also having the tail curve back up (by hump I mean to simulate a gaussian distribution with the tails) how would I do that. seems like I now have part 1 of 4 aspects of the curve. here's the attached file to show what I mean. I want to get the hump going up and then sort of reflected along the y-axis and then similar features if I were to reflect the curves on the x axis. is it possible with the functions in excel? Linest test.xlsx
View 14 Replies
View Related
Dec 11, 2013
I am having trouble converting file formats. I would like to convert a.xlsx file to a .xls file. It is password protected and everything I have tried to use to convert the file has failed.
View 5 Replies
View Related
Apr 14, 2014
I would like to create a chart in vba which contains 2 sets of data, both using the same x axis. The first must be a line graph, the second a staggered bar chart. This must be represented on one chart with data labels. Also, how do I change the colour of the plots?
View 5 Replies
View Related
Apr 12, 2009
My question is about creating User-Defined Data Types. Is there ANY way possible i can create a User-Defined Data Type that declares a variable of another User-Defined Data Type instead of the Pre-Defined User Types like String, Integer, etc?
The following explains my problem in more detail.
I know to create a User-Defined Data Type at the top of the module before any procedures. Like this:
View 6 Replies
View Related
Apr 28, 2009
My question is about creating a hierarchy of Data Types.
I need to code 3 Data Types:
1.) MealPlan
2.) Meal
3.) MealItem
MealPlan represents a daily meal plan. It should have a certain number of meals. An example of MealPlan would be "Healthy" and have a total of 3 meals. Meal represents a single meal. It should have a certain number of meal items. An example of Meal would be "Meal 1" and have a total of 3 meal items. MealItem represents a single meal item. It should have a certain number of protein calories, fat calories, carb calories. An example of MealItem would be "Broccoli" and have protein calories of .2, carb calories of .8, fat calories of .1.
By using Data Types, I hope to create meal plans, which are made up of meals, which are made of meal items. The following code creates a Data Type hierarchy for two Data Types. I've examined it closely but i'm still having a hard time recreating it for my needs as stated above.
View 3 Replies
View Related
Jan 3, 2012
I have three items I would like to graph, on one chart. The first is %Complete. This is a number figure. The next is the date the item is due to ship. Obv, this is a Date. The third is today's date. Basically, I would like to display in the same chart so that we can see how far a long a project is, in relation with today's date and how much longer we have until it needs to ship.
This is the current display of the chart:
Is there a way to show the ship date, as the 100% complete mark? In order to make it display half-way decent, I had to format the left y-axis to go above 100%. I first tried to format the right y-axis, but was extremely confused to setting values. I figured out that the end date is somewhere in the 40,000 value mark? WTF is this about? Is 1 equal to 24 hours? I assumed that was the case, but then when I pick the value it should end with, it doesn't seem to react that way. I want the right axis to start with 12/01/2011 and end with 5/01/2012. How can I achieve this?
View 3 Replies
View Related
Sep 20, 2012
I have a csv file. When I open it by double clicking, all the finance figures appear as currency with the currency symbol. I can use the SUM function on these and it's perfect.
However, when I open this file using a macro, these same numbers look the same, except that they are now left justified and are text because the SUM function no longer works. If I select a range, I get the count of the number of items selected showing at the bottom of the screen but not the sum.
I have tried opening the file using the OpenText function which has several ways of setting a date format to the fields but nothing for numbers.
I have even tried, in the macro, selecting a cell with numeric 1 and then selecting the range and pasting the 1 as a multiply function but this doesn't work either.
View 2 Replies
View Related
Sep 19, 2008
I an newbie at VBA but I took some short programing classes back at my college days. I am trying to declare an array with different data types and since that seems to be imposible for what I gather then my other option is to declare what I remember as a structure.
View 9 Replies
View Related
Nov 5, 2008
I have a procedure that was working perfectly yesterday. I've not changed it and yet today it doesn't seem to be working.
I keep getting a 'Runtime Error '13', Type Mismatch' debug error.
Public intRowCount As Integer
Sub mcrCopyDataNewSheet()
Dim strRawDataBook As String
Dim strNewBook As String
Dim strRawDataRange As String
intRowCount = Range(Range("B5"), Range("B5").End(xlDown)).Count
strRawDataBook = ActiveWorkbook.Name
strRawDataRange = Application.InputBox(Prompt:="Please enter the Raw Data Range beginning with Earliest Start on Sunday and ending with the Saturday Rota:", Default:="L:AR", Type:=8)
View 9 Replies
View Related
Jul 18, 2009
In one excel book I have 2 sheets.
1. One sheet (request type wise) contains request type and the phase which it belongs to. eg request type A belongs to phase 1 and request type B belongs to phase 2. (like wise there are 212 request types divided into 8 phases)
2. The second sheet contains raw data (request type) where the request types are randomly arranged.
What I am trying to do is:
Compare the raw data of sheet 2 with standard data in sheet 1 and paste the phase to which it belongs to with a help of macro/code, since the standard data (212 request types with 8 phases) is huge.
View 9 Replies
View Related
Dec 10, 2009
to create a macro that counts a number of values for a couple of types. The list looks like this;
29 bananas
13 apples
18 bananas
14 pears
7 pears
etc.
So i want to create a loop the goes through the list and adds up all bananas, apples and pears. How do I do that in the best way?
View 9 Replies
View Related
Oct 12, 2007
Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:
1) if the user highlights multiple cells of the desired column
2) enters the letter of the column (up to "z", not case sensitive)
3) enters the number of the column
Sub Button5_Click()
Dim ColumnUsed As Variant
' find number of columns selected
For MyColNum = 1 To Selection.Columns.Count
Cols = Cols + 1
Next MyColNum
'find number of rows selected
For MyRowNum = 1 To Selection.Rows.Count
Rows = Rows + 1
Next MyRowNum..............................
View 2 Replies
View Related
Dec 14, 2007
How do you convert all the picture files in a worksheet so they can be processed the same? Half my pictures have eight white adjustment circles framing them when they are selected, the other half have four blue circles and four blue squares.
View 2 Replies
View Related
Aug 24, 2011
In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:
I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.
I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).
View 3 Replies
View Related
