List Unique Entries And Total Cells Based On The Results
Jun 10, 2008
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
View 9 Replies
ADVERTISEMENT
Jul 27, 2013
[URL] ......
In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.
I could do it if it were just one column of data, but struggling trying to do it with several columns.
View 7 Replies
View Related
May 29, 2014
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
View 9 Replies
View Related
Sep 27, 2012
I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
View 4 Replies
View Related
Jul 30, 2009
I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
View 9 Replies
View Related
Jun 16, 2009
I have a dynamic list of names from B2:B500. I want to write a macro that finds all the unique entries from that list and pastes it to AD3:AD501 everytime the macro is run. What would be the code for this procedure.
View 9 Replies
View Related
Dec 23, 2008
I have the below table of data, in Column A there is a list of entries and there may be duplicates in them, what I want to do in column B is be able to list all the unique entries in there and basically remove any duplicates.
AB1DETAILED LISTUNIQUE LIST2Holden 3Ford 4BMW 5Mercedes 6Ford 7Ford 8Audi 9BMW 10Holden 11Ferarri 12Holden 13BMW 14Toyota 15Toyota
View 9 Replies
View Related
Mar 6, 2008
I have a list in Row A that has duplicates. I want to be able to delete both entries (itself and the duplicate). When done I want the list to display only be entries that are unique or better said any entries that never had a duplicate.
View 8 Replies
View Related
Dec 11, 2009
product1
product1
product3
product7
product1
product7
product3
I want to do a lookup and return to a new list just the unique entries, such as this:
product1
product3
product7
I have it working now using a pivot table to get me the unique items, but there must a be a more efficient function to do this.
View 9 Replies
View Related
Oct 26, 2007
I'm having problem with the ISBLANK function. I have attached my workbooks if someone would care to look at them. My macro basically loads two lists from other workbooks (old & new (attached)). It then finds out which entries are unique to each list, and places them in the EXCEPTIONS sheet.
Column C in these sheets should say TRUE or FALSE as to whether the corresponding cells in Column B are blank but it does not work. Book1.xls contains my macro.
Old.xls and New.xls will need to be selected when prompted.
View 3 Replies
View Related
May 1, 2009
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 6 Replies
View Related
Feb 5, 2008
I have a user form with a group of several option buttons. When an optionbutton is selected, programatically, I want a worksheet's named column to be parsed through to find unique entries and then have those entries passed to the form's combobox. I've spent the last hour searching for an answer but to no success
View 3 Replies
View Related
Mar 18, 2008
I am working to develop a spreadsheet that takes user input (from a barcode scanner, that issues a hard return, limiting my information to a single column) in the following order. User ID, this is an Alphanumeric unique ID (AA1AAA) or user id. Followed by a pick ticket ID the value of the pick ticket ID will always be above 100000 (ie 123123) followed lastly by the number of lines on the pick ticket id (this number never exceeds 15)
So an example data set would be:
AA1AAA - User ID
123123 - pick Ticket ID
7 - Number of lines on the ticket
123124 - pick Ticket ID
10 - Number of lines on the ticket
123125 - pick Ticket ID
6 - Number of lines on the ticket
AA1BBB - User ID..........................
View 6 Replies
View Related
May 28, 2014
The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
View 2 Replies
View Related
Sep 21, 2012
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
View 1 Replies
View Related
Feb 5, 2014
I am trying to create a Excel file for my job. I work in a warehouse where I have many different orders all of which are made up of various different numbers of items and priorities. For example:
Order 1 = 45 items, Priority 1
Order 2 = 78 items, Priority 1
Order 3 = 48 items, Priority 2
Order 4 = 34 items, Priority 3
The total number of items is divided by the number of staff to give each staff member a total for the evening.
I plan to have a list of these orders in the excel file.
What I would then like to do is to have each staff member allocated orders so that the their total is reached. The sheet would automatically allocate orders to each person from the list of orders so that based on priority first then by the largest number of items to the smallest.
I think this should be a fairly simple sheet to create and I have a view of creating an application for it if I can get it working.
View 1 Replies
View Related
Jan 29, 2012
I am trying to get an unique list based on the value being the only member of the group.
Sheet1
A B 1 Group A A 2 Group A C 3 Group B B 4 Group C C 5 Group C B 6 Group D B 7 Group E C 8 Group E B
The solution is
Sheet1
D E 2 Solution 3 Group B B 4 Group D B
Is there a way to do this?
View 9 Replies
View Related
Jan 11, 2010
ColA has a range of names.
ColB allocates ColA entries to either of two categories by entering 'cash' or 'credit'.
I need help writing a formula to make a unique list of the names in ColA that have 'cash' in ColB. I'd prefer not to use autofilter.
View 9 Replies
View Related
Jul 27, 2009
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 9 Replies
View Related
Jul 16, 2009
I need a macro that will do the following:
Range: A:A
Look for the cells that do not have a duplicate and place the number in column B.
For Example:
Column A
121
121
122
123
123
124
125
125
Result:
Column B
122
124
View 9 Replies
View Related
Jun 10, 2014
Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.
Please see attached file : Formula.xlsx
View 1 Replies
View Related
Jul 22, 2014
I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.
View 1 Replies
View Related
Jan 6, 2008
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 8 Replies
View Related
Jun 28, 2008
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 3 Replies
View Related
Nov 25, 2011
I was trying to use an if statement to produce results based on 2 cells. The number in the 1st cell can be the number 1 to 5, but each number has critera with it. Say the cell is A1 and i enter the number 1. Then i want to check the value of Cell A5, if this is less than 40 then the cell A10 should read 250 and if A5 was over 40 then it should read 285. struggled to get a formula properly to work.
Here are the values for each number: A1 = 1 with cell A5
View 7 Replies
View Related
Oct 27, 2009
I have a list that is numbered 1 2 3...ect. I want to have a cell that tells me the total number of entries. I have a I'm not sure how I could do this.
1
2
3
4
5
6
1
2
3
Total = ?
If I do a normal sum it would just give me all of those numbers added up. I want the total to read 9 (in this case) My list changes all the time so I want that total to tell me how many entries I have.
View 3 Replies
View Related
May 6, 2009
Using Excel 2002. Here's my problem.
Column A contains the month (as text)
Column C contains an employee name.
Column O contains a reason for absence.
Column K is the number of hours of absence.
The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence.
E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X.
This will be used on a seperate worksheet where the name C will be referenced from a validation list.
View 9 Replies
View Related
Aug 14, 2013
So basically I have a spreadsheet that tracks if a patient has turned in there required paperwork within the last 6 months and then changes the cell to white and lets me know how many days they have left until they are due to turn this paperwork in again. Then if it has expired I have a condition format change the cell to Red but I would like to add in the text EXPIRED to the cell. lastly I have Cells that have no data in them gray and I would like to add in red text saying No Paperwork.
I'm hoping this will be my last build of this spreadsheet so I can go ahead and start applying it to the real workbook [URL]
View 12 Replies
View Related
Feb 17, 2010
Im trying to make a formula that lets you type in your current level, and then calculates how much XP you have remaning untill you reach the max level.
The way the forumla would need to work would be something like this:
(E10) Enter Current Level:
(F10) 37 (which is my current level)
F11 - would then add cells from C37 to C70 (calculating the total amount of XP required) and then display in (F11)
Being able to say if, then is far to cpmplicated for me in excel. I'm just trying to make this for a bit of fun and am sure one of you talented people can easily do this.
View 4 Replies
View Related
Jun 9, 2014
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 Replies
View Related
