Assist me in developing a macro that would search for unique values in column A and then place a "1" in column N of that row to signify it as the unique value. I am trying to do this in order to get a unique count of projects listed because the list I receive has multiplie occurences of the same project # in column A.
I have an extract that includes unique attempts to complete a transaction. Sometimes these transactions fail 1 or multiple times. Sometimes they are successful in one try. other times they first fail and then are retried and succeed. I am trying to flag failures in a new column that later succeed in the same session. Here is my data:
Date
Order #
Session ID
Mac Address
Status
5/2/14
O123
A100
11111111F
Failed
[Code] ............
I would what these flagged as such:
Date
Order #
Session ID
Mac Address
Status
Successful Retry?
5/2/14
O123
A100
11111111F
Failed
Yes
[Code] ........
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
What I want is a macro that will give me the total of unique values within a given column. I would like the macro to give the total either at the end of the column or with a popup displaying the total.
Suppose in Column F
1
2
3
1
2
3
1
2
3
----
The macro would place 3 at the end of the column as the total of unique values or have a pop up that says 3.
Is there a way i can have a macro find the unique values in these 3 columns and highlight them yellow.
They should find uniques using all 3 columns, not find them within each column.
I included a file which shows what i start off with and what it needs to look like. Also if it matters the contents in the cells are from links. ( i know that matters because you have to put look in values in the code ).
Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has
ACLU0403598
ACLU0403598
ACLU0403598
ACLU0406600
ACLU5165518
ACLU0406581
ACLU0406581
All red items need to be removed and keep only green items. Would also like the entire row the duplicates are in to be deleted.
see the attached file for an example. I am creating a formula/macro for performing the following: If "Y" exists in column J, then replace in the next cell below (columns G and H) with the values from the cell above in columns G and H and then clear the contents from the above cells.
View 2 Replies View RelatedI have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.
The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.
Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?
I am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.
I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.
Still I have to check and manually insert extra missing for every half hour missing but that's bearable.
This formula inserts a "missing" every time it finds a gap.
My question is: How can I insert a row above every cell with "missing"?
How do I do that? Here is also my excel sheet.
CR1000_Meteo_20131113_2_CLEANED.xls
Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
View 7 Replies View RelatedThis is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
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 ?
View 14 Replies View RelatedI had a spreadsheet developed for me at work but the lad who developed it for me has subsequently left and I'll be honest I would not have any idea of macro's or some executables in excel. The only problem I'm having with this is it is not flagging item's as late unless they have gone over 2 months past the target date (would be ideally looking for 1 day past target date for it to flag late). Could anyone just have a quick look at this, I'm sure that this could be a quick fix but I would not have the relevant expertise for this. If someone can rectify the problem could you also just give me a quick explaination as to how this was done as I would like to have an idea myself and not have to continue to bug people with simple problems like this.
View 3 Replies View RelatedI have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
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 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
how to get this with formula:
Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))
Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.
I have number values in G2:G50 with some blanks included - what I'm looking for is a formula that will add all the values together but will only add values that exist more than once one time.
4.24
2832.74
2832.74
8643.73
8643.73
The only things that should get added at 4.24, 2832.74 and 8643.73
I have an ever growing client list that currently has just over 2500 names which I need to track the total number of clients, which is the easy part, the total number of files, and the most important part is how many unique names are on the list. Some clients will appear a number of times on different dates, and I need to be able to track how many clients appear only once in the list.
Adams, John
10/6
Lincoln , Abraham
10/6
Clinton, Bill
10/6
[code].....
In the above example, the answer I am looking for is 2, because Clinton, Bill and Carter, Jimmy both appear only once. My total clients would be 4, though the number number of "files" worked on would be 6.
I have some data and one of the columns is overtime. The data is set up where each employee has a record for each function they perform. So I may have an employee that opens and funds loans. They will have 2 records and one of the fields in the record is overtime. My issue is, in this example, that the OT will be doubled if I just sum the column. I need to sum the unique values only.
View 9 Replies View RelatedI have a spreadsheet into which users enter account details. I have another spreadsheet which is updated periodically which contains a valid list of accounts.
Currently I am using the following code which works perfectly to see if the account is valid or not:
If WorksheetFunction. CountIf(coaRange, checkAccount) = 0 Then
where coaRange is the search range on the valid accounts spreadsheet and checkAccount is the string of the current account to check. If I get 0 I know the account isn't valid and I highlight the incorrect cell. All this is in a loop checking each account. What I would now like to improve is the speed at which the checks are peformed. Sometimes there may be 700 rows with account details in but there might only be 10 different accounts.
I have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:
pepsi
google,samsung
coca-cola
I want them to look like this in one column (preferably via a formula):
pepsi
google
samsung
coca-cola
I would like the above output to be a unique, alphabetized list.
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).
Excel_Forum_Example_Dataset_7.24.14.xlsx‎
I am writing a formula to accomplish the goal of counting only unique text values in a column, based upon two different factors:
*Need to Count unique text values in column named 'CASE_NAME'
*Column 'FISCAL' must match cell A5
*Column 'ZONE' must match cell A3
It is a sample of my table to operate on: excel.jpg
My task is to make a list of mobile phone brand and model name – success, fail, reject, keeping in mind that one particular phone could have been contacted several time during the time of the campaign, but it should appear counted in all stats only once (hint: track phones by their unique Bluetooth code).
With reference to a thread that is nearly similar to my own problem:
http://www.excelforum.com/excel-gene...ml#post2076546
I have a similar problem, though maybe a little more advanced. I would like to do the exact same, but when looking at the sample.xls in the referenced thread, my data can have zero values/blank in the C-column. For instance, if C6 is blank, then the formula will not add the value in the next row. In many cases this would easily be solved through sorting, but the structure of my sheet do not allow for this. So what I am looking for, is a formula for only regarding rows where the cells in column C is <>"".
Maybe this cannot be done in a single formula, and has to be solved through VBA, but having seen some incredible solutions here, I thought I'd give it a try.
I need a formula that will make every make every cell in column A unique. This formula needs to concatenate columns H, M, and N and display the result in column A. As you can see, you can get two instances of the same value (e.g., Dog:Brown:Small for A2, A9 and A10). For this reason, we then need to concatenate a counter number on the end that increases by 1 based on the total number of matching values ABOVE the cell in column A.
For example, I need a formula in cell A12 that concatenates H12:M12:N12 and then counts the number of "Dog:Brown:Small" instances above and then appends the largest incremented value +1. This would result in "Dog:Brown:Small:0004" in cell A12 and "Cat:Black:Large:0003" in cell A13. I will then drag this formula down column A and it will dynamically execute the formula against all column A cells above it.
So here is my dilemma and I haven't been able to find anything to exacly help me out:
I have a large (100k+ records) sheet of sales invoice details.
I need to add a total item line per invoice number to each record ....