How To Count A Column If It Matches Data From Another Column In Seperate Rows
Feb 6, 2009
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
View 14 Replies
ADVERTISEMENT
Nov 29, 2006
How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.
View 4 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
Jan 29, 2010
I have data as follows in Column A:
Part Number: 0000000-1 ARTEC-GH-56S 12A
SPARES in Repair: 20
On-hand: 100Location: BNCD
I need the data under different columns as follows: I also want an extra column before Column A labelled as Common number.
A B C D
Part Number SPARES ON HAND LOC
0000000 20 100 BNCD
View 11 Replies
View Related
Jun 5, 2009
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies
View Related
Feb 25, 2014
I'm trying to sift through 10000+ rows of results, and I'd like to know if there's a count formula to determine how many times I have matches in one column (name) and mismatches in another column (ID #)
Profile ID |Name | License #
12345 |Debra Nelson |12345678
12345 |Debra Nelson |23456789
I want to count how many times theres a match in the 1st or 2nd column (profile ID, name - those two columns should match) and a mismatch in the 3rd column (License #). I'm not sure if this can be done with a formula, like a COUNTIF, or not.
View 4 Replies
View Related
Jul 28, 2006
I have data (coming straight out of another system, so whilst is seems messy, its the easiest way to have it in Excel).... where there are a number of rows (one for each employee) - then every column represents a date in the month (so there are about 30 columns)....the cells contain a number of hours worked by that employee for that day.
I have figured out how many hours they worked for each day of the week, but my client now wants to know:
for each employee - how many Mondays did they work?- how many Tuesdays? and so on
I am happy to figure out the day of the week for the first date in the month and then manual set up another column heading above the date that tells me what day it is - but I feel that Excel may actually be able to work out what day of the month it is - although the dates come across from the other system as text - April 26, 2006 etc...
So I would like a summary in my sheet that has column headings
Mon, Tues, Wed, Thurs, Fri, Sat, Sun
and then for each row for each employee - I would like the number of days that employee worked for the month by these Days of the Week.
I have looked at Count if - but I need to be able to select the range of cells to count, and also the range of cells to evaluate for the criteria and I cant figure it out!
View 9 Replies
View Related
Aug 11, 2014
I want to count the number of times partial text strings occur in a cell adjacent to another cell containing specific text.
A B
FB Milton v Town PHOTO
BS Fairfax v South
BS North v Town
BS Milton v South PHOTO
FB North v Milton PHOTO
FB Milton v South
I'm looking for the number of times "FB" and "Milton" occur in Column A when "PHOTO" also appears in Column B in the same row. From numerous searches I've tried figuring out VLOOKUP, DCOUNTA, etc.
View 6 Replies
View Related
Nov 30, 2008
See attached spreadsheet. I need to have all the data which is held in 1 row in column A seperated into its own column automatically. The order i need the data to be in is as follows
Date(pink), Time(red), Racecourse(green), Race Type(black), Class Of Race(purple), Going(blue), Prize Money(yellow), Distance(grey), Number Of Runners(brown). the text in bracket is a key for the info for your ease of use.
View 3 Replies
View Related
Sep 6, 2006
I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!
Sub DeleteRepeatedRows()
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
rCell.EntireRow.Delete
End If
If rCell.Value = rCell.Offset(2, 0).Value Then
rCell.EntireRow.Delete
End If.........................................
View 7 Replies
View Related
May 19, 2006
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View 9 Replies
View Related
May 12, 2008
I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.
View 9 Replies
View Related
May 5, 2008
I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.
Data set example:
In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.
Dim c As Long
c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
With Range("A3:A" & c)
If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then
. AutoFilter field:=1, Criteria1:="1/*"
.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
.AutoFilter
I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.
View 6 Replies
View Related
Apr 25, 2014
I am trying to copy values and dates from a row if it matches the numbers in the left column.
I attached a simplified version where I explained how it should be done.
View 9 Replies
View Related
Jan 22, 2014
I am trying to Count (or counta?) cells in one column if they have data but only if within a date range in another column.
So, in the example below, I would want to count how many cells in column E have data in them, but only if the date in column A falls between Jan 1 2014 and Jan 5 2014. (In this case, result should be 4).
A
B
C
D
E
01/01/2014
[Code].....
View 4 Replies
View Related
May 6, 2009
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".
In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
View 3 Replies
View Related
May 12, 2014
I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
View 4 Replies
View Related
Apr 10, 2014
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana
B1 - 3
B2 - 2
B3 - 1
B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
View 1 Replies
View Related
Aug 18, 2009
Does anyone have a macro that will delete entire row if column a matches column d
View 6 Replies
View Related
Oct 4, 2008
I have a column containing text values eg M1, T2, M3, A4 etc. and I am trying to return the value (numeric) in a second column to a cell when the text value in the first column is matched.
View 4 Replies
View Related
Oct 4, 2007
I have a spreadsheet which will be completed by numerous users, with a worksheet reserved for each area. The spreadsheet is to record the number of days lost to training etc on a weekly basis.
Each worksheet has 3 columns – column A DESCRIPTION, column B WEEK COMMENCING DATE and column C DAYS LOST.
The table will be completed by the manager’s as the info becomes available to them.
I will be collating the data on another worksheet and need a formula that will look in column B for all instances of 01/10/07 and then sum the corresponding cells in column C, then do the same for 08/10/07 and so on.
I have attached an example of a page.
I thought it may be VLookup or Sumif, but I don’t know how to go about it.
View 3 Replies
View Related
Mar 2, 2011
I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
View 7 Replies
View Related
Feb 15, 2007
I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.
Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...
i'm not even sure if this makes sense lol... let me use example
on SP-B there's a column M.
in row 11, value = 3
in row 12, value = 5, in this same row column E is filled with whatever
in row 13, value = 2, in this same row column E is filled with whatever
in row 14, value = 6
in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.
View 9 Replies
View Related
Apr 6, 2012
Here is what I am trying to accomplish.
I have to columns.
I have copied column A from a PDF file. It contains entries like this, all in the same column:
5 0.002HP Direct- 26845 7/1959 Airesearch
This column contains an ID # (5), a description (0.002HP Direct), a part # (26845), a date (7/1959), and a manufacturer (Airesearch). All this info is in one column. This column is 11,000+ entries long.
I have column B which is 45,000+ entries long, and contains ONLY part numbers, like this:
325111
I would like to compare column B to find the matching results from within the text of column A, and put these matches into column C.
View 3 Replies
View Related
Oct 4, 2009
Is there a simple formula that can be used to seperate text in a column.
Example:
ABC - 123456
I want to return the text before the "-".
View 9 Replies
View Related
Oct 29, 2009
I have columns with titles at the top, and with numbers in the below cells.
What I would like is a formula for a column seperate to these that firstly, looks if there is a quantity in the relevant cell on that row, secondly takes the title (top cell) of that column, and thirdly puts the number in the cell on that row after the title:
A_____B_____ C_____ D_____ E______ F
1_____UK____ Ger___ M/E___ Rus_____ Nor
2_____80_____250___ 300___ 350_____200
3____ 60_____200____350____350____ 150
So in cell A1, I would like a formula that reads the subsequent cells and their titles, and gives a one-cell summary of the data seperated by commas. So in A1 it would say:
UK 80, Ger 250, M/E 300, Rus 350, Now 200
And in A2:
UK 60, Ger 200, M/E 350, Rus 350, Now 150
View 9 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
Jul 1, 2014
VBA which would count data in Column F of dump Sheet and paste the count in master sheet B2 Cell.
View 7 Replies
View Related
Apr 21, 2009
I have a spreadsheet with two sheets, 'Sheet0' & 'Sheet1'. In 'Sheet0' I have a column named 'A_Sheet0' containing a list of numbers from Rows 2 - 50. In 'Sheet1' I have 3 columns, the first containing numbers 1 to 50, the second, 'B_Sheet1' containing a list of ascending numbers and the third is the column of interest. This column entitled 'Nearest Value' must read the corresponding value of Column 'B_Sheet1' then find the closest number to that from the numbers in 'Sheet0', column 'S_Sheet0'.
I have included a sample file with this thread, and in the 'Nearest Value' column I have manually input some of the nearest values for your information.
View 7 Replies
View Related
May 7, 2007
I am looking for a way to ease a 24,000 line duplicate list into showing just the files from a specific folder/subfolder and its dupes. I have marked the files that reside in that folder by adding a column and entering a "1" in it. What I am looking for is a way to add that "1" to the others of that unique numerical ID duplicates, so I can sort and trim the list to just those files. End result is to show files in one folder and the locations of duplicates of those files elsewhere for eventual cleanup.
Very loosely, if C2=C1 and B1=1, then make B2=1 (and so on)
(A is blank and only there in case a formula needs input there)
A B C
110121
10121
118134
18134
18134
124232
24232
View 9 Replies
View Related
