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

Create List Of Unique Entries Based On Several Columns

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

Identifying Unique Entries Based On Unique Entries In Another Column?

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

Excel 2007 :: How To Get Dropdown Box Results To Show Unique Values And Non-Blank Entries

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

Unique List Would Grab All Unique Entries

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

Macro To Paste Unique Entries Of A List Into Another List

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

Unique Entries & List

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

Remove ALL Non-Unique Entries From List

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

Lookup And Return To A New List Just The Unique Entries

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

ISBLANK: Finds Out Which Entries Are Unique To Each List, And Places Them In The EXCEPTIONS Sheet

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

Filtering For Unique Entries Based On One Column

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

Fill ComboBox With Unique Entries Based On OptionButton

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

Add Numbers To Running Total Based On Unique ID

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

Obtaining List Of Unique Values From List Based On Some Criteria?

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

Create A List Of Results Based On Criteria

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

Allocate Values From A List Based On Total

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

Unique List Based On Count?

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

Unique List Based On 1 Criteria

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

How To Count The Number Of Unique Values In A List, Based On Another Value

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

Unique List Of Cells Not Duplicated

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

Formula For Total Cost Based On List Of Hours For Each Employee Times A Table Of Rates

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

Dropdown List Always Shows Unique Values From Column Based On Lookup Value

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

Count Unique Cells Based On Ajacent Cells Having Values

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

Total Cells Based On Adjacent Cells

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

Using If Statement To Produce Results Based On 2 Cells

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

Calculate Total Entries

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

Calculating A Total, Based On Values In Other Cells

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

Generating Text For Cells Based On Results Of Formula?

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

A Formula That Will Sum The Total Of Different Cells Based On An Initial Variable

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

Total Percentage Of True Cells Compared To Total Number Of Cells

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







Copyrights 2005-15 www.BigResource.com, All rights reserved