Count Unique Cells Based On Adjacent Column
I have a worksheet with following data in 1 tab.
[TABLE]
Seq Class
SE110
SE270
SE110
SE370
SE310
SE110
SE170
SE370
[/TABLE]
In a different tab I want to calculate the unique count of sequence for a specified class.
In above eg: for class 10 it should return 2
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count Unique Cells Based On Ajacent Cells Having Values
I have the following data in 1 of the tabs. [TABLE] ClassSequenceAB 10SE132422 20SE23212 20SE321 20SE23425 10SE332455 15SE132412 10SE234 [/TABLE] I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows: [TABLE] ClassAB 1032 1511 2012 [/TABLE] Can this be achieved through a formula?
View Replies!
View Related
Count Filled Cells In One Column Based On Date In Another Column
I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example: AB 1-May 1-May777 1-May 1-May 1-May 2-May999 3-May 2-May 1-May 2-May111 1-May If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
View Replies!
View Related
Count Occurrences Of Adjacent Cells By Condition
I am attaching a sheet in which Data can be entered in cells C5 & D5 in "Calculation " sheet, depending upon the value of C5 it will count all the Occurrence of data of D5 in adjacent cell i.e. (one up & and one below of data of C5) . data will search in "PCM_PLAN" sheet in column " I ". count will be given in E5 in "calculation sheet"
View Replies!
View Related
Color Cells Based On Adjacent Cells Being Empty & Add Text
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!). What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out. I have posted a link to an image which shows what I want. I hope I've explained it well enough! http://img530.imageshack.us/img530/6239/excelspfk0.jpg
View Replies!
View Related
Sum Based On Adjacent Column Value
I have a spreadsheet with a list of account numbers and values(sheet1) and on a seperate sheet (sheet2) a list of all unique account numbers that appear in the transaction data sheet. What I am trying to do is get a total sum for the values of each account number. What I am getting well I'm not quite sure what I'm getting. The problem appears to be when the xSubtotal variable is reset to 0 at the end of calculating all the values for the account number.
View Replies!
View Related
Count Of Unique Numbers Based On A Criteria
My data is like: A1: 5 -- B1: X A2: 5 -- B2: X A3: 5 -- B3: Y A4: 3 -- B4: Y A5: 3 -- B5: X A6: 8 -- B6: Y A7: 2 -- B7: X A8: 3 -- B8: Y A9: 1 -- B9: X I want the count of unique numbers in column A based on the criteria i have on column B. So the count for X should be 4 and for Y should be 3.. Can i achieve this using formula only?
View Replies!
View Related
Copy Cells From Column To Adjacent Column If Bold Or Empty
I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group. The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.
View Replies!
View Related
Count Unique Items In A Column
Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.) basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.
View Replies!
View Related
Conditional Formatting Based On Adjacent Column Value
I'm trying to highlight cells a certain color based on the value of the cell 1 column to the left. How would I be able to do this in conditional formatting? Example) Highlight the cells in "Visit 2" column if it is greater than the "Projected Visit 2" column. NameProjected Visit 2Visit 2Person 124-Nov-0820-Nov-08Person 226-Jan-0927-Jan-09Person 32-Jan-082-Jan-08Person 430-Dec-0829-Dec-08Person 46-Nov-0830-Oct-08
View Replies!
View Related
Count/Sum Unique Values Based On Criteria
I'm facing a big problem and I can't found a solution for days. I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below: =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")) Let me Put to you an example for you been able to understand my problem. Example considering Range "C2:C20" exluding blank cells (1) - To count unique values =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")) (2) - To count unique values based on criteria in range "B2:B20" =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")) (3) - To sum unique values on "E2:E20" based on same criteria =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20)) They all work fine. But I need to insert a merge at Row 10 for better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's. I just don't know what to do guys ....
View Replies!
View Related
Count Unique Values Based On Matching Criteria
I have a worksheet with two tabs. First Tab Account Name Account Number Second Tab Account Name Account Number Account Ship Location Number On the first sheet each account name and number only appears once. On the second sheet there may be multiple entries for each Account Name and Number. On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
View Replies!
View Related
How To Count The Number Of Unique Values In A List, Based On Another Value
I need to count the unique values in a list based on another value. i.e. AB ___ Jones AB ___ Smith BC ___ Henry AB ___ Jones BC ___ Brown BC ___ Henry AB ___ Tomkins For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting: =IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100))) but haven't been able to get it to work.
View Replies!
View Related
Count Unique Records With Criteria From Another Column
As the title says, I need to count the number of unique records (names) in column A, where column L is = to something specific (X,Y,Z,W, whatever) for some statistics im trying to report. Please see the attached spreadsheet. For role X statistics, I need a count of the unique names from col A, where col L = X. Based on my sample spreadsheet, the number should be 2. For Role Y, it should be 3. There is the potential for spaces in the rows, and no one will be 'cross role' I've got a few different methods to just count unique values with specific criteria in the same column, but I just can't make anything work for specific criteria in another column.
View Replies!
View Related
Count The Number Of Unique Numbers In A Column?
I have a column where I have record numbers. The record number can end up duplicate in some cells. PROBLEM_ID IBM-02204732 IBM-03252238 IBM-03252238 IBM-03335648 IBM-03534918 IBM-03534918 IBM-03862015 IBM-03862015 IBM-12737629 IBM-12737629 IBM-17269762 I would like the get a formula that can count the number of unique numbers in that column? I would like to get if possible to get a formula that summarize the number into a single cell.
View Replies!
View Related
Count The Number Of Unique Numbers In A Column
i'm currently trying to count the number of unique numbers in a Column using this formula: =SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&"")) So far so good.....But I want to add some conditions like only count the numbers that meet the condition say N1:N3000="SE*" =SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&"")))) On this one I get #VALUE! and i can't figure out why. I have also tried to put the AND/OR condition before the actual starting of the array of the SUMPRODUCT but nothing.
View Replies!
View Related
Count Unique Values In A Filtered Column
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 Replies!
View Related
Fill Empty Column Cells With Reference To Adjacent Cells
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row. I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
View Replies!
View Related
Count Unique Words Appearing In A Column Once Per Row
I would like to modify the below macro so it only counts a word once even if it appears more than once in a row. The reason I want to do this is so I can get a snapshot of the data without certain terms being over-represented. For example, in the attached example the word 'Microsoft' appears 5 times in row 4 but I only want this to be counted once. In the whole data set Microsoft appears 20 times but only in 7 of the 20 rows so I would like the count to be 7. The example is set out as follows: In column B there is a description field which in practice will contain consumer complaints and inquiries. To keep the data anonymous the description is filled out with random words and all other columns are blank. The output of Andy Pope's unique word counting macro appears in I:J.
View Replies!
View Related
Count Unique Entries In One Column That Meet Conditions
I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.] The situation: I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values. namegender jones m martinf smithf collinsf wilsonm jones m martinf hughesm wilsonm martinm smithf west f jones m west f martinm The challenge: In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman! The correct result: ...
View Replies!
View Related
Total Cells Based On Adjacent Cells
I have multiple sheets cataloging multiple vehicles' mileages in multiple areas (one sheet per month). I want to reorganize this data by Vehicle ID rather than month. Here is an example spreadsheet to help explain I have attempted this by writing this formula ...
View Replies!
View Related
Combining Cells Based On Value Of Adjacent Cell.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks. Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names. A B 1 Car, 1 Taxi, 2 Bus, 2 Lorry, 3 Skateboard, 3 Bike, 1 Motorcycle, What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
View Replies!
View Related
Delete Rows Based On Adjacent Blank Cells
I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this? Sub DeleteBlankARows() Dim r As Long For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1 If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete Next r End Sub
View Replies!
View Related
Find Value Specified & Calculate Based On Adjacent Cells
I am really stumped at this particular point in my coding. What I am trying to do is to have a user input an ID number. When they click OK, I want to find the ID number and then subtract 1 from a number that is 13 columns over. So if the ID number is "123456789" and that matches the value in A3, I want to subtract 1 from the amount in M3 (leaving the new value in cell M3). So, if M3 was 30, now it would be 29.
View Replies!
View Related
Sumproduct (count The Number Of Unique, Or Distinct, Company Names In Column)
I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count. I should also point out that most company names appear multiple times in column [C7]. a) Count the unique company names in column [C7]... b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
View Replies!
View Related
Copying Cells Down To Next New Data In Adjacent Column
In column A, I have unit numbers. Column B is blank. I need help copying the unit number in Column A down in Column B until a new unit number appears in column A. For example, in A6 is unit LS2, A12 is unit LS24, A17 is unit LS34. I would like to be able to copy A6 into B6:B11, A12 into B12:B16, and A17 into B17:B22, etc. I have the code to copy A6 in to B6 and down, but I don't know how to make it stop at A12. Attached is also an example.
View Replies!
View Related
Merge Adjacent Cells At Each Change In Column
I have a list of values, some are like values, others are not. is there a way to have the column to the right automatically merge the cells which have like information to their left. Attached is a sample file. I want to be able to have the cells in column B automatically merge based on the values in column A. I'm not sure if this can be done, but figured I would ask before I spend hours merging cells.
View Replies!
View Related
Copy Cells To Adjacent Column, Removing Blanks
In a worksheet, J2:J52 contains a list of unique strings; each cell populated by an embedded formula. Some of the cells contain a valid 'blank' "" given by the formula. I'd like K2:K52 to contain the values J2:J52, with all the populated cells stacked at the top and the 'blank' cells at the bottom. 'Blank' cells do not have to be in any particular order. I've been fiddling with the formula kindly provided by WindKnife on the second post in the thread below for the past couple of hours, but to no avail: http://www.excelforum.com/excel-work...om-a-list.html Columns A:H in the same worksheet are populated and unavailable for use.
View Replies!
View Related
Conditional Formatting Date Cells Based On Entry In Adjacent Cell
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M. I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting: Cell value is less than or equal to =TODAY() All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M. In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View Replies!
View Related
Lookup Column Values On Another Sheet & Return Adjacent Cells
Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b. We have two worksheets, sheet a and sheet b compare sheet b, column c (email addresses) to sheet a, column x, (email addresses). If they match, move sheet a, columns d, e, f, g, and h to sheet b.
View Replies!
View Related
Filtering For Unique Entries Based On One Column
This may be a basic question but I haven't been able to find the solution: I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example 0001 a 0001 b 0002 c 0002 d 0003 e 0003 f I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example: 0001 a 0002 c 0003 f I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
View Replies!
View Related
Extract Unique Records Based On 1 Column
I have a single excel data sheet with 10 rows of header information and then multiple rows and columns of data I need to extract the 10 rows of header data plus the rows for each unique record in Col A into its own separate worksheet, with the work sheet name being the unique record from Col A To further add to the challenge, the data in col A may have "/" in so will not comply with excel sheet naming convention so would like an error message to remind me to manually change a sheet name. I attach an dummy data sheet just to show what I mean!
View Replies!
View Related
For Each Unique Value In Col A+B, Removing Lines Based On Column C
For each unique value of Order Number [col A] + Line Number [col B], I need a program which will locate the first instance of Next Stat [col D] = 530 and the first instance of col D = 540, deleting the other lines. The output will have two lines remaining for each unique col A+B combination, one where col D = 530 and another = 540. The data will always be sorted in date/time order prior to running this program. The actual data set has a varying number of lines, usually 1000+.
View Replies!
View Related
Return Unique ID Based On Multi-Column Lookup
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system. I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST: SCH0001, Lindsey Schubert, 75230 SCH0002, Thomas Schoembs, 53132 ADA0001, Samantha Adams, 28205 ... What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary. Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View Replies!
View Related
Counting Unique Cells Based On Another Cell
I have 2 columns that I need to use simultaneously to conclude something. Column A Row 1: Dog Row 2: Cat Row 3: Bird Row 4: Dog Row 5: Dog Row 6: Dog Column B Row 1: See Vet Row 2: See Vet Row 3: Get Rest Row 4: Get Rest Row 5: Get Rest Row 6: See Vet Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.
View Replies!
View Related
Count Data In Column X Based On Conditions In Column Y
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 Replies!
View Related
|
TRACKER
