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 RepliesI know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
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.
I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data
a1 = apples
a2 = pears
a3 = oranges
a4 = apples
a5 = apples
a6 = apples
a7 = pears
in this case number of uniques items is 3
Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this.
I have a long column of 9-digit numbers which represent the swipe-card-ID's of patrons accessing a parking facility. Most of them occur more than once, some of them many times. There is an operational rule that bars them from using the facility more than x times. (I can't implement this limitation in the parking software/hardware, or I would happily do so.)
So my question is, is there a way to process through that column, identifying each unique ID, and reporting any that occur more than x times?
I am trying to seperate singlr column information into seperate column based on the name in the Data 1 column.
Ex.
Data1 Data2 Result 1 Result 2 Result 3
one100one100two200three1
one350one350two450three2
one500one500two600three4
two200
two450
two600
three165
three236
three450
im looking to have a drop down list on sheet2 that displays unique items in column A on sheet 1.
then when an item is selected from the drop down list, i want to be able to display all unique values that correspond to that option. i have attached an example worksheet
data on sheet1 is ever changing
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
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.
I have part of this routine working correctly. It will go and find the value I need and move to the next column using the Offset method, but now what am I doing wrong to try and get the code to select to the end of the current column?
Sub Find_First()
Dim FindString As String
Dim rng As Range
'FindString = InputBox("Enter a Search value")
FindString = Worksheets("Template").Range("A26").value
If Trim(FindString) <> "" Then
With Sheets("Service Level Score Paste Sheet").Range("A:A")
Set rng = .find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
rng.Select...........................
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
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
I need to put a part of a text from a column and place it in a new collumn. At this moment I use 2 columns and an extra sheet to solve this.
I have a collumn with a lot of data like:aaa-bc
bbb
abc-ba-bd
aa-bb
bbb-xx-yy
aa
abc-yy
klmn-zz
klmn-yy-ff
What I need is behind every cell a new cell with only the 1st part like:aaa
bbb
abc
aa
bbb
aa
abc
klmn
klmn
What I do now is use a new column with this formula:
[Code].....
And I made a translation table in which the 1st 4 characters are translated to what I want to see. In a 3d column I use
[Code]....
to get the results I need. Since I need to get results like this very often this method is very time consuming.
Is there a formula available I can use with only 1 new column?
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
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 "-".
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
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
View 1 Replies View RelatedHow would I count unique dates associated with another specific value on a worksheet?
For instance, Lets say I want to count each day a particular person makes a sale.
Example:
Sales PersonSale IDSales Date
Don 1001/6/2014
Don 1401/7/2014
Don 1601/8/2014
Jack 1011/6/2014
Jack 1021/6/2014
Mike 901/5/2014
Mike 1031/6/2014
So on another worksheet, I'd have:
Sales Person Days with Sale
Don 3
Jack 1
Mike 2
The Formula would go into the "Days with Sale" Column.
I have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.
View 2 Replies View RelatedI 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.
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.
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.
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
I have a worksheet with Coulmn A and B , wherein , Column A has a list of values that can be duplicated. Based on the values in Column A, I want to fill corresponding cells in Column B. see the attached.
way to accomplish the same. Tried VLOOKUP.
how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.
..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York
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.
augmenting current code to select the column after a column. Current code is:
Code:
If Sheets(data_sheet1).Cells(2, iCol).Value = "Stations" Then TargetCol = 1
If Sheets(data_sheet1).Cells(2, iCol).Value = "Agency" Then TargetCol = 2
If Sheets(data_sheet1).Cells(2, iCol).Value = "Advertisers" Then TargetCol = 3
If Sheets(data_sheet1).Cells(2, iCol).Value = "Product Code" Then TargetCol = 4
The next line should find the column after "Product Code".
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 RelatedMy table is as follows:.....
If the columns A, B, and C were constant and never changed and only column H changes from job to job, how do I: SUM the TOTAL number of pieces (pcs per X qty) if the value in column B is "A"? I need to do this without creating a seperate mutliplication column for the sum of the pieces. It all needs to be done without adding any new columns.
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