I have three columns (A) has a date (B) has the name of a person and (C) has an audit type.
I know I need an array but not sure how to structure it for 3 columns
DateNameAudit type01/04/2008DavidCDM01/04/2008John1 in 1030/04/2008Bill1 in 1001/05/2008DavidVehicle01/05/2008DavidVehicle01/05/2008JohnCDM
The answer for these 3 columns is 5. Rows 4 and 5 are the same so are only counted once.
I can get the arrray to count unique items in the whole range and in a column but not for each row.
I have a feeling this is going to obvious but I can't work it out.
Col A Col B 163401 1 163401 1 163401 0 163402 1 163402 0 163402 0
GOAL -I want to be able to count Col B only once for the same set of records in COL A.
Based on Distinct criteria on COL A, I need to be able to count COL B. The count should be 1 for 163401 and not 2. Similarly for 163402 the count should be 1
I have another question to ask, that is why I am posting another thread. I have a column with blanks and data (roughly abt 300 rows as such, not unique, there are many rows repeated numbers). How can I copy the distinct values from this column to another sheet. I am able to copy unique values to another column in the same sheet. But when I try to copy them to another sheet it gives me an error. I used advanced filter option to do this. I also want to get the count of these unique values. In sql I know it can be acheived with "Select Distinct row1, count(row1) from table1 order by row1".
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.
I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).
I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.
All the cust numbers are unique so would I be able to use a wildcard?
I have a column on sheet with has some data. I want to know: How many unique data has my column. If I use auto filter I can count it in list. But how can I do it with macros? I don’t want to make a cycle because it will takes me a lot of time…..
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
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"))
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data - a header - a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data - a header - the repeating model number
What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.
Is there a way to accomplish this via some functions or maybe even a VBA script?
I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!
As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually
Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)
I have one worksheet which acts like a data dump (actuals) where data is just pasted in. This worksheet is sorted and edited on a continues basis. I need this data to be sorted into their respective coded worksheets (110, 120, 130).
I would like a function in Worksheet 110, 120, 130 that captures the data for that 'code'. Now the issue is that much of the data is non-unique, dates, codes, accounts may sometimes be duplicated however i still want to display the entries separately in their respective coded worksheets. (I had a solution using column numbers however it isn't viable because the data gets resorted often)....
I need creating a macro which compare the values of "Column B" of attached both "Sample1" and "Sample2" excel files and if any unique value found in Column B of "Sample2" file then the entire row should be get copied in "Sample1" file after row count.
For ex. the rows colored as yellow in "Sample2" file are unique and should be get copied in "Sample1" file.
I used Sheets(1).Cells(1, 1).Rows.End(xlUp).Count instead of UsedRange.Rows.Count in this code , but it didn't succed with me. Why and how to do that
Dim i As Long, j As Long j = 1 For i = 1 To UsedRange.Rows.Count Sheets(2).Cells(j, "a").Value = Sheets(1).Cells(i, "a").Value Sheets(2).Cells(j, "b").Value = Sheets(1).Cells(i, "b").Value Sheets(2).Cells(j, "c").Value = Sheets(1).Cells(i, "c").Value j = j + 1 Next i End Sub
I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.
The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:
a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)
Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT Mary-------some text--------some more text John-------<blanks>---------just text here Sue--------just some here-----<blanks> Dave-------something--------something else Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
I have this formula that works perfectly fine for what I am trying to do (from another post) but I am wanting to transfer this into VBA. It is an array formula so I'm not sure if this can be done.
How can I convert this, into R1C1 code or any way possible. I would put it within a WITH RANGE statement.
Need to do this using formulas and without needing to add any new columns.
Need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab.
Want the formula to be applied to the summary to go and lookup/count from the other sheet.
Have shown the expected results in the col "total unique heads".
I'm working with a data set (attached) that tracks financials at both the job and item level (in my customer's data - a unique job can be made up of a single or multiple items).
I've successfully built a formula (in column C of the attached) that counts the total number of items and their associated $ values that were invoiced in a given month based on my customer's requested item-type categorizations (i.e. "Exact Match", "Similar", "Inferred", "Cost-Plus").
The problem I'm running into is building a formula that counts the first unique instance of the Job ID associated with those items. For example - in cell C33 on the attached - The total number of items invoiced in June that were "Exact Match" was 10. These items were spread across 9 unique jobs - and that's where I'm struggling - to build a formula that only counts the first unique instance of the corresponding Job ID in Column C.
The formula I'm having trouble with is located in Cell D12 on the "Dashboard" tab of the attached.
The formula as I have it is returning a result of 8 - because it's summing the "1"'s in Column I on my "Invoice Data" tab - however it should read 9 (Filter Column Q on the "Invoice Data" tab to "June", Filter Column AQ on the "Invoice Data" tab to "Exact Match", and then sum the unique job IDs in Column C).
I want to have a unique count field in my pivot table. I already read (via forum search) that this can only be achieved by adding an extra column to the database.
The problem is: I have no clue which formula to use for this column. Please have a look at the attached picture. Can someone provide me with a formula for this example?
I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.
