Apply Formula In New Column That Pulls Unique Values From Another Column?
Apr 24, 2014
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?
Our spreadsheet pulls values from a column when a month is inserted in a certain cell:
OCT is typed in B2 and in the column below it, from B10 down to B286, data (numbers) are pulled from the October column K10 through K286 using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed in B2 then the same happens except data is pulled from the November column, L. The monthly columns from K through V are tied to and updated from other sheets. As you can see the problem is that nested functions allow only 7 and I need 12, one for each month. I have looked at the VLOOPUP and the HLOOKUP but our data is not set up that way (tables) since they need to be exact numbers pulled from the monthly columns that are tied to other sheets.
OCT (B2)
OCT NOV DEC Complaint 3 (B10) 3 6 Inspection 10 (B11) 10 2 NOV 11 (etc) 11 5 Door Notice 1 and so on Recheck 32 Citation 2 Work Order0 Demolition 0
I have a file with a column containing a series of records where a reference number can be repeated several times. I want to create a formula that will count the number of unique reference numbers in the column.
However - and this is the tricky part - I need it to allow for when the report is filtered, i.e. something like a SUBTOTAL function which ignores the hidden values.
So, the column in the full report has 691 unique values across 2,200+ records. If I apply a filter the column only has say 78 unique values. Is there a formula that can calculate this?
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?.
I have two columns in excel, col A and col B.What I would want to achieve is following:
Col A Col B A 2 A 2 A 2 B 3 B 3 B 3 B 3 C 3 C 3 C 3
Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).
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.
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.
Hello, below is a sample of my excel sheet. The number of rows keep changing daily and the Month and year column has to be populated automatically as and when there is a new row inserted. I tried the formula =MONTH(A:A) and I paste it on the entire column. it works for rows with data but I also get 104,000 odd blank rows added to my excel because I pasted the formula on the entire column.
way to put a simple formula in a colum that will apply to the entire column, no matter how many rows without having to physically copy it to the number of rows required?
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB: Sub Macro1() ' ' Macro1 Macro ' Range("B10").Select Cells.Find(What:="as % of Revenue").Activate Range("B20").Select ActiveCell.FormulaR1C1 = "=R[-1]C/R10C" Range("B21").Select End Sub
I need to do something to my workbook, and I need to do this task:
When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..
Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..
So I don't need to insert/delete the row and copy the formula manually for each worksheet..
I know that I could simply solve it with grouping the sheet tab..
But I have plenty of data that needed to be inserted and applied with the formula..
I will attach the little example : insert.xlsx
And one more thing, I received this VB code from [URL] ..... for inserting the column:
VB: Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim lngRow As Long Dim ws As Worksheet If Target.Row = 1 Then Cancel = True
[Code] .....
And this code for deleting the column:
VB: Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim lngRow As Long Dim ws As Worksheet If Target.Row = 1 Then Cancel = True
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.
I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
HOW TO pull the unique values out of a column of entries and put them into another column.
For example I'll have a list of maybe 2,000 but with only 1-7 different values in the column, what I want to do is populate another column with only the list of the 1-7 values that appear in the larger set.
I need to capture a list of unique usernames appearing in a column on the sheet, and also the number of times each user has a desired value occur for their row (an error). The number of users and the names of the users will change from one sheet to the next making a static solution unworkable. The user names will be unsorted in the column.
My first reflex is to use an array, and an associative array if possible would allow me to reference the user by name, instead of a clumsier index number and iterating through a loop to verify if the user is already stored in the array. I don't know if it's possible in VBS though. I'll add any new users names to this array if they aren't already in there.
How can I count the number of unique values in a column?
I have a spreadsheet that lists transactions that occurred over a one-year period (2007). I need to determine the number of days our facility was operational. No transactions on a particular date (25-Dec-07, for example) and the facility would be considered to have been closed on that date.
On the days that the facility was open, multiple transactions would have occurred on those open days and those multiple tranactions are all reflected in the worksheet.
So I need to count each occurrence of '01-Jan-07', '02-Jan-07', and so on.
I filtered the data based on the column1 using vba. Then after that, the filtered data in column2 have repeat finance accounts. What i need to find the unique finance accounts under the column2.
Is there any methods to find the unique value under the column2?
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.
this code "Creates a New Workbook" for each item listed in an excel table, then, copy all unique values in their respective Workbook and save it. So, the problem is this. instead of creating a "New Workbook", i want to open a previously created Workbook (template) and do the procedures listed above. Is this posible?
Sub Copy_To_Workbooks() Dim CalcMode As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Dim foldername As String Dim MyPath As String Dim FieldNum As Integer Dim FileExtStr As String Dim FileFormatNum As Long Set ws1 = Sheets("Sheet1") '<<< Change If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else If ws1.Parent.FileFormat = 56 Then FileExtStr = ".xls": FileFormatNum = 56 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If End If..............
I need to create a drop-down (or a combo list) list based on a column in a separate worksheet (in the same workbook). The values in the column have many duplications, and I simply want the combo box to return a unique list.
After trying this using various options like drop-down boxes, combo boxes etc it does not seem to work. A drop-down box will return a complete list (including all of the duplicates). In addition the drop-down was linked to a cell and the value returned to the cell was a number based on the position in the list when what I actually wanted was the text. For example - my list in Worksheet 2:
PETS Cat Dog Dog Cat Horse Rabbit...
In Worksheet 1 if I choose 'Dog' from my Combo box (or drop-down) along with showing all of the duplicates it returns '3', instead of what I want 'Dog' and no duplicates.
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1 Month value specified in: G1