Count Alternating Values In Column?
Aug 25, 2014
I'm sure there's a simple way to do this (I've been trying countif/match formulas but I'm not quite there)! I need to group line items with the same dollar value, WITHIN say 1 cent. If I do a pivot it will group exact matches, but I'm looking for the flexibility to group something within a few cents. So instead of pivots, I was trying to think of a formula that would do what Column A is doing below:
*I manually added Column A, looking for a formula that would group dollar values within 1 cent, assuming I've sorted Column B from smallest to largest:
Column A : Column B
1 : $2.13
1 : $2.14
2 : $3.00
2 : $3.01
2 : $3.01
3 : $4.00
View 4 Replies
ADVERTISEMENT
Jan 18, 2014
I'm looking for a way to count groups of alternating rows of "TRUE" values across 3 columns. There will never be an occurrence of more than one "TRUE" value per row. In the sample below, alternating TRUE values occur in rows 4 through 7, so this would be counted with the groups of 4.
1Column 1Column 2Column 3
2FALSEFALSETRUE
3FALSEFALSETRUE
4FALSE TRUEFALSE
5 TRUEFALSEFALSE
6FALSE TRUEFALSE
7 TRUEFALSEFALSE
8FALSEFALSETRUE
9FALSEFALSETRUE
10FALSEFALSETRUE
View 4 Replies
View Related
Mar 6, 2009
I have a bunch of start End times in columns calculating total time :-
View 12 Replies
View Related
Aug 12, 2010
I'm looking for a way to put data into 1 column from 2 different columns, alternating every other row.
The data looks like this:
A B C
1 2
3 4
5 6
And I want C to look like this:
C
1
2
3
4
5
6
I don't necessarily need to keep the data in columns A and B, as long as C follows this format.
Excellent, just what I was looking for. I couldn't get the UDF working right, I'll try messing around with it, but the Index solution works great!
View 5 Replies
View Related
Mar 13, 2014
I have sets of data... like so:
A word text blablabla
B word text blablabla
A dog cat france
B dog cat france
B this that other
A this that other
A tiger dragon monkey
B tiger dragon monkey
My source data I can't get to sort alternating on columns A and B, and I would like the data to alternate A-B-A-B-A-B while retaining the pairings of data in the adjacent fields. Is this possible?
View 1 Replies
View Related
Mar 14, 2014
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.
View 2 Replies
View Related
Apr 25, 2014
For column "B" count all the 1's if column header is equal to name in column A.
For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.
View 5 Replies
View Related
Mar 5, 2014
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
View 2 Replies
View Related
Dec 19, 2010
I would need to count unique frequencies in column A, but only for a specified value in column B.
Example:
A|B
1|1
1|1
1|1
2|1
2|1
2|2
2|2
3|2
3|2
I am using Excel 2007 and found this formula that counts all unique frequencies in column A: =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
In my example, result = 3 Now, I would need to add the possibility to count only for B=1; in my example, result should be = 2.
View 9 Replies
View Related
May 15, 2014
I have a column that repeats it self and I want to perform a count on Column B, based on the values in Column A.
Example attached : Book2.xlsx‎
View 2 Replies
View Related
Oct 18, 2011
How do I count unique values in Column A only if the values in Column B match?
A B
1 a
2 a
1 a
4 b
5 b
1 b
The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.
View 5 Replies
View Related
Jun 17, 2009
I grow my business by dialing a massive amount of leads everyday which I manage in Microsoft Excel. In row "E" I have it labeled as Dispositions and each call I make gets labeled as..
WN = Wrong Number
AM = Answering Machine
UA = Unavailable
NQ = Not Qualified
DNC = Do Not Call
IV = Invite
SA = Sale
I would like to create functions or formulas which displays the total number of each of these dispositions from column E and shows the totals in separate cells so that I dont have to count them manually anymore.
View 2 Replies
View Related
Jan 21, 2014
I have a spreadsheet which contains (see illustration below):
1. a column called LEAD ID which contains a unique id
2. a column called WON which signifies if the deal was won/not won (1 = WON, blank = Not Won)
I can have multiples of the same value in the Won and Unique Id field (see illustration below)
I would like to create a formula field which returns the summary of the No. of Deals WON
I need to tie the formula back to the Lead ID and whether or not the WON field contains 1 or not.
I've tried this formula but it won't work
(Lead ID is in Column A, WON is in column B)
=IFERROR(SUM(1/COUNTIF((A:A,A:A) AND(B2=1)),"0")
LEAD ID WON No. Won (Formula field)
abc112 0
abc222 1 .25
abc222 1 .25
abc222 1 .25
abc222 1 .25
abc333 1 1
abc444 0
View 9 Replies
View Related
Dec 15, 2007
I have a list of varying IP addresses in a column. I need to create a formula that will tell me how many times an IP address appears in the column so that I can ultimately determine the most common IP address listed.
View 6 Replies
View Related
Jul 1, 2009
I am using Excel 2007, and I'm stuck with this problem.
View 4 Replies
View Related
Dec 29, 2008
I 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
Jan 20, 2014
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
View 2 Replies
View Related
Aug 27, 2010
I was wondering if there was an array formula or some VBA code which can transpose/repeat values based on a count. Below is an example of what I mean:
Name Count Output Tod 4 Tod Bill 3 Tod Sue 5 Tod
Tod
Bill
Bill
Bill
Sue
Sue
Sue
Sue
Sue
View 6 Replies
View Related
Apr 10, 2012
I have 10 columns and 18 rows. Every 2nd row might contain a number. I need to sum those numbers, however, sometimes group of cells in a row can be merged and i value of a cell is then 0.
example:
A1 to D1 merged; value 6
A3 to B3 merged; value 3
B5 to E5 merged; value 2
=sum(A1,A3, A5) would return 9 - correct
=sum(B1,B3, B5) would return 2 - should be 11?
=sum(C1, C3, C5) would return 0 - should be 8?
=sum(D1,D3, D5) would return 0 - should be 8?
=sum(E1, E3, E5) would return 0 - should be 2?
View 9 Replies
View Related
Mar 18, 2013
I've got two columns, one with policy numbers, the other with error codes.
A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function
=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))
I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with
=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))
The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.
What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?
Here's my sample data: [URL]
View 9 Replies
View Related
Jun 11, 2009
see attached workbook.
I'm attempting to count the empty cells in column C which correspond with the project reference shown in column A. I only want to perform this calculation where the project numbers change (see desired results in column D).
Am struggling with finding a way to define the different range of rows for each project ref within the formula. The full sheet has over 6000 rows and 1500 project refs.
View 4 Replies
View Related
Nov 25, 2009
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View 3 Replies
View Related
Mar 5, 2014
I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture:
Column C Column D
111111 M
111111 M
111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
View 9 Replies
View Related
Mar 15, 2014
I have a column (k) of 65,000 rows with numbers that are either positive or negative digits. The next column (L) has classified them as either 1 for positive number or 2 for negative.
There may be a run of all positive or all negative numbers or 2 positives and 3 negatives or any combination of positive and negative numbers reading down the column.
Reading down the column (from L1), I need to find any sequence of alternating 1 positive, followed by 1 negative or vice versa.
I am looking for evidence to find any sequence that has more than 2 alternations. (Remembering that positive is 1 and neg is 2)
That is, two or more alternations of a positive followed by a negative or neg followed by a positive.
eg
1
2
1
2
or
2
1
2
1
View 4 Replies
View Related
Sep 13, 2006
I'm writing a macro that’s taking user input (from a form I developed) and will generate a chart according to the input.
One requirement for this chart is that the background alternates fill colors between gray and white. Every four columns need to be either gray or white. Based on the duration the user entered, the width of this chart is created. The length (the number of rows) is constant.
I've tried different loops (for, if, do - you name it I tried) This loop is just out of my league. Any and all help would be greatly appreciated.
Summary of Facts:
- Starting Point of Chart is B2 (the top left corner of the chart will be in B2)
- the number of columns is dynamic (based on user input)
- The rows are constant are (2 through 16)
- Every four columns need the fill color to be either gray or white
View 8 Replies
View Related
Nov 29, 2006
to tint/color alternating rows with white on my worksheet so when new rows are entered the sheet will adjust maintaining this pattern
View 6 Replies
View Related
Apr 13, 2009
I was wondering, what would the macro be to colour alternating rows.
So I could select the range of rows, hit the macro - and it would colour them for me?
I need it to do alternating green / lightgreen. For one, but I need to do other colours afterwards as well. So what would the code be to do this?
View 10 Replies
View Related
Feb 21, 2014
I have a spreadsheet that contains thousands of rows and I need to highlight all the duplicates in Column B.
What I need though is one group o duplicates to be colored one color and then the next group of duplicates be colored a different color. I need just a 2 color banding.
My need for 2 colors is that the cells contain 9 digit numbers and there can be duplicate groups right next to each other and for the eye to distinguish a different grouping of cells I need that 2 color banding.
I have the below code that colors everything in one color. Can I be modified or a completely new code to do 2 colors?
Code:
Sub Dups()
Dim Rng As Range
Dim CL As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
For Each CL In Rng
If WorksheetFunction.CountIf(Rng, CL.Value) > 1 Then
CL.Interior.ColorIndex = 6
End If
Next CL
End Sub
View 1 Replies
View Related
Aug 1, 2014
My spreadsheet has rows with inspection numbers in column A, but there are varying numbers of rows associated with an inspection number. I'd like to change the color of the rows for every other inspection number to yellow using a formula. I think it has to involve an if/then statement but I can't figure out how to word the formula to just choose every other group of inspection numbers.
Example:
inspection numbers
date
description
[Code].....
View 5 Replies
View Related
Nov 30, 2006
Is there a way to shade alternating rows in a range and have it always be alternating no matter if a row is added in the middle of the range. If I do it manually now and add a row it messes up the shading and fixing it manually is very time consuming.
View 7 Replies
View Related
