I have a collumn with lots of different text values some repeated. how can i count all these values so that it only counts each value once.
e.g
if in cell A1 i have = "apples"
and in cell A2 i have "apples"
and in cell A3 i have "Pear"
i want this to return the value 2 not 3, make sense?
how can this be done via a formulae or macro?
I have a collumn with lots of different text values some repeated. how can i count all these values so that it only counts each value once.
e.g
if in cell A1 i have = "apples"
and in cell A2 i have "apples"
and in cell A3 i have "Pear"
I'm trying to find a code that will count duplicate values in columns B,C and D and put the outcomes in B1, C1 and D1.
If've tried conditional formatting but can't count any formatted cells with duplicate values.
Any Ideas?
I have a 2 columns with lots of different text values some repeated. Again Colum B has a Yes/No anwer input, how can i count all these values in Colum A based on whether the Column B value equals yes.
Column A / Company Name_____________Column B / Reply
Company X ___________________________Yes
Company X ___________________________No
Company Y ___________________________Yes
Company Z ___________________________Yes
Company Z ___________________________Yes
What I want the formula to do is count all the Yes occurrences in column B and Only one distinct occurence of a Company
eg
From the above I want to see 3 as a result as opposed to 4 as they are two Yes entries for Company Z
I have been able to use the SUMPRODUCT function but dont have the knowledge to build upon this to take the second column value into consideration.
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
I use the following formula to count duplicate random numbers on Row 35 that occurred on Row 34:
=SUMPRODUCT(--(D35:I36<>""))-SUMPRODUCT((D35:I36<>"")/COUNTIF(D35:I36,D35:I36&""))
I would like to continue to compare against Row 34 and count duplicates on other rows (ex. Row 36 or Row 37) one row at a time.
In addition is it possible to visually identify the duplicates through conditional formatting (ex. Bold or color change) or other ways?
I am trying to determine wether there are duplicate entries within a range and have then counted also. Example:
I have a list if zip codes and I want to find out how many of each zip code are there. If I have a list with:
12345
12345
12345
12346
12346
12347
I want to have it tell me there are 3 of "12345" and 2 of "12346", possibly next to the entry?
12345 3
12345
12345
12346 2
12346
12347
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
i want Counting duplicate numbers in a string for example Counting 2
22241278-------- Count(2)=4
I'm on Excel 2003 and have a sheet to track people who have read my emails. I want to know the answers to the questions below. I've included a fake sample to show what I'm looking at. The number of cells in each column is much higher and not the same per column. I've tried variations of MATCH, VLOOKUP, SUMPRODUCT, etc. but haven't found a good way to set up a formula.....
View 6 Replies View RelatedI am trying to make a Macro that will search through a folder on my PC and look for file names with a string such as shown below:
The issue i am facing is that many of the files have duplicates.
I need it to check to see if the number of duplicates in Column E for each file name have the same amount of duplicate files in the actual folder... and if they don't I need the rows(s) highlighted Yellow.
Example: Say I run this macro.. and there are only 2 files in the folder that start with "BH1003".. as there is 3 instances in column E.. the 3 rows that have "BH1003" in them should all highlight..
I have added my code so far below also, but it only highlights each row Yellow if it finds an instance of the string in column E.. I don't know how to get it to count and compare....
Code:
Private Sub CommandButton3_Click()
'Search Folder for files - Highlight Yellow if Exists - Highlight Red if NOT Exists.
Dim r As Long, lastRow As Long
With ThisWorkbook.ActiveSheet
'get last row
[Code]...
I have a Workbook with multiple sheets with Data in them. I would like to Count the number of duplicate values between the sheets.
For example.
Sheet one has a list of case references Person A is dealing with.
Sheet two has a another list of case references Person B is dealing with however the also have a list of case reference's they are monitoring as part of a project.
Sheet 3 has got the total number of case references each Person is dealing with, it also contain the total number of cases Person A&B together are dealing with. however i need to find the total number of cases Person B is monitoring that Person A also has so that the total number of cases is the true number of total cases with no duplicates involved.
(References begin with VB then have a string of numbers afterwards and I would like to know the total number of cases the Person B is monitoring as well so these need ot be calculated).
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100
101
102
105
Col D
6
6
5
5
I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.
Here is my code thus far....
-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
[Code]....
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100.........................
I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.
Formula:
[Code] ....
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")
I have data that looks like this:
day# id amount
1 56575 0
1 56675 0
1 56680 0
1 56683 0
1 56681 0
1 51810 0..............
How do you write an excel macro that looks at the number in the first column (day #) and finds all the duplicate id#s in the second column that are in day 1and adds the amounts together in the 3rd column then writes the first column number (day#), second column number(id#) and the third column (sum of the amounts of duplicate Id#) to an new worksheet. Then the macro would loop through day #2 and do the same thing. Notice that the values in the id column are unique in this data set below this is how I would like the data to look. I have accomplished this in a pivot table but my problem is I need a cvs file to export the final data into an external database which is why I need a macro.....
how to get this with formula:
Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c
I'm trying to display a 2-column table that tells me the number of games broadcasted by each channel. The code has to figure out the channel name itself, without me specifying the channel name in it. How can I do this in VBA?
Game1 NBC
Game2 CBS
Game3 CBS
Game4 CBS
Game5 FOX
Game6 CBS
Game7 FOX
Game8 FOX
Game9 FOX
Game10 NBC
Game11 ESPN
Game12 NFL Network
Game13 FOX
Game14 CBS
if i want a cell to count certain value in a range what formula o set, i know that CountA function counts cells with words in them but it counts everything, i want a certain word(s) to be counted.
for example i have...
New
Old
Take Over
and i want C11 to count 'New' in range of B12:B20 .
how to?
and 2nd how to Name the Columns, i mean Instead of regular A,B,C how to set a name or title for them...
and how to keep them on top, i mean as i scroll down columns keeps there only sheets scrolls up and down so i can see given Column Names/heads.
I have a cell, say C74. In the cell contains 416 values are four & five digit numbers separated by a comma and a space. (example: 21160, 21161, 21250, 21340, 21440, 2155, 21650, 21651, 21740, 21741, 2185, 21940, 2253, 2254, 2440, 26160, 26250, 26293, 26323, 26350, 2636)
So I'm trying to write a formula to count all values with in the cell.
Answer should be "21"
I am trying to count how many times a certain value appears in a column. For example how mnay times A81001 appears in column A and compare with column B. I suppose it's Count function?
column A column B
A81001A81001
A81001A81001
A81002A81002
A81002A81002
A81002A81002
A81002A81002
A81002A81002
A81003A81003
A81003A81003
A81003A81004
A81004A81004
A81004A81004
A81004A81004
A81004A81005
I'm putting a presentation together for work regarding false alarms and I'd like to count how many alarms have gone off at certain locations and I'm wondering if there is a formula that will accomplish this. I've attached a pic of a spreadsheet containing the data to be used. On the left side, there is a table labeled "Alarms" with columns displaying the dates, street numbers and street names. On the right side of the spreadsheet, there is a table labeled "Activations", which will be used to display the amount of single, double, triple, etc. activations.
For example, using the data, the alarm at "2 Red Oak Row" has gone off 3 times and since there are no other addresses where this has happened, a "1" would be displayed under the box labeled "3" in "Activations". Since the alarm at "4 Winding Way" and the one at "23 Blackberry Lane" have both gone off twice, a "2" would be displayed under the box labeled "2" in "Activations". The same would hold for addresses where the alarm has gone off 3 times, 4 times, 5 times, so on and so forth. In addition, if an alarm has gone off 10 or more times at any residence, the first 10 activations and any subsequent activations would be counted as a "1".
For example, if there is one case where an alarm at a certain residence has gone off 10 times (or more), a "1" would be displayed under box 10. If another residence's alarm also went off 10 times (or more), a "2" would be displayed under box "10".
Ok so I have an excel file, that has 20,000 records/rows of data. There is a column called <STATE>, which could be PA, MD, CA, MT, etc.
I need to know how I can count how many times each state is populated and return the results to a new created sheet called <REPORT>. See example below.
State:
PA
PA
PA
MD
CA
MT
NJ
NJ
Report Results
PA 3
MD 1
CA 1
MT 1
NJ 2
Can this be done?
I have a sheet with a bunch of data including dates. I'd like to report out the number of cells that are in a certain month (say January). If I create another column and use =MONTH(A12), I can use COUNTIF(A1:A100,1) to perform this task. This somewhat clunky solution isn't very practicle, becuase I have about 30 column's I'd like to analyze by the date in the column. Is there a more elegant solution where I can incorporate the MONTH(A12) into the COUNTIF equation?
View 9 Replies View RelatedI have data that is set up like this:
A1 B1
Apple Sweet
Apple Juicy
Apple Worm ridden
Apple Round
Apple Cooking
Apple Grannies
Pear Green
[code]......
and would like to combine all of the duplicates from A1 into one field, with all of the options in B1 combined into one cell (separated by a |). So the above table would read:
Apple Sweet|Juicy|Worm ridden|Round|Cooking|Grannies
Pear Green|Tasty|Mouldy
Orange Tangy|Tasty
Peach Rare|Forbidden|Expensive|Squishy
I am currently busy with a material tracking sheet. The sheet tracks all the material from fabrication to being released from painters.
My problem is that in a summary sheet that I created, I used vlookup to get the exact value of the item number that was released.
Focus on Item # 043-06016
example summary.jpg
[Code] .....
This formula only gets the first value and not the 2nd value that was also released so that in my summary sheet i can get 2
example released items.jpg
I have a range of times, and with the whole range I cant have any duplicate values, if they are duplicates I would like to add 1 second until all the values are individual.
View 3 Replies View RelatedI have to compile spreadsheets as tables of various values. One column in particular is a column of ID numbers and the worksheet is very very long. I need a method for checking this one column for duplicate ID numbers.
View 4 Replies View Related