Formatting Based On Unique Value?

Apr 18, 2013

I'm trying to visually format my spreadsheet in a way that lumps customers together. Excel gives you the automatic "every other line" two tone variation.

(blue)
(blue)

(light blue)
(light blue)

(blue)
(blue)

[code]....

I've sorted by data by customer name. I want to format all Customer A rows as (blue), Customer B rows as (light blue), customer C (blue), customer D as (light blue) etc. With hundreds of customers and typically between 1-5 entries for each customer, this an impossibly time consuming task. Is there anyway to automate this process?

Customer A (Blue)

Customer A (Blue)

Customer B (light Blue)

Customer C (Blue)

[code]....

View 2 Replies


ADVERTISEMENT

Conditional Formatting To Find Non Unique Values

Dec 14, 2009

I need to find rows in my large worksheet that have 2 vendor names for a single vendor ID. Each vendor should only have 1 vendor ID.

Is there a way to highlight rows that have a single vendor ID for more than 1 vendor name?

View 9 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

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 Values Based On Criteria

Jun 20, 2013

I've got an interesting problem which I haven't been able to find an answer for.

A B
PR Yes
PR Yes
PR Yes
DN Yes
DN No
DN No

I need to count how many unique values in column a have every row in column b the same value. So counting for yes values, PR would equal one but DN would equal zero.

I used this formula to find out how many unique files had yes values, now I need to find out how many files only have yes next to them.

=SUMPRODUCT(($B$2:$B$14="Yes")/COUNTIFS($B$2:$B$14,$B$2:$B$14&"",$A$2:$A$14,$A$2:$A$14&""))

View 8 Replies View Related

Lookup - Trying To Find First Unique Value Based

Oct 28, 2007

I have scoured the Excel boards but cannot find what I am looking for. Need to do a formula approach (No Pivot Table) lookup on a dynamic table that is not necessarily in order and may have blanks in some cells or whole rows. What I would like Excel 2003 to do is report (extract) the all first occurrences of each organization number that has at least one person with a "Yes" in a corresponding column in the same row. I also need the number of persons that have "Yeses" in each of those identified Org Nos .

Data is laid out as such:

Org No. Name Not in Compliance
8631Steve Yes
8631David
8631Nick
8631Tom
8631Susan Yes
8701Peter Yes
8701Diane Yes
8701Mary Yes
8879Don
8879Bernie
8879Wayne
8900David Yes

The answer is

In one row list the org #'s not in compliance ("Yes"). In the row just below it the number of persons:

86318701 8900
2 3 1

I have a helper column that counts the number of unique org nos, but cannot tie that to those that have Yes in the other column.

In column before Org No i have =IF(COUNTIF(b$33:b34,b34)=1,MAX(a$33:a33)+1,"") copied down.

View 9 Replies View Related

Lookup Based On Two Unique Values

Sep 15, 2009

I've been searching both here and on google but I've been unable to make use of any of the numerous suggestions and examples I've found to do this; given I'm right in thinking that this should be done with a vlookup!

I have a table in which different customers pay different prices for different products. C13 is a cell in which I want Excel to look up the correct value based on A13 and B13 (in this case C3).

I have no experience with lookup functions, which might be the reason why I can't get this to work. I bet it's pretty simple

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

Bring Top 10 Unique Name Based On Multiple Criteria?

Apr 23, 2013

In the below data set i have the data ranked by branch and center by value. I am trying to create a summary page that looks at the data set by branch center and rank and brings back the debtor code. The letters above headers represent the columns.

a b c d e f g
BR
CNTR
DEBTOR NAME
2012
Rank

[Code].....

View 1 Replies View Related

VBA To Generate A New Workbook Based On Unique Field

Feb 4, 2014

I have an excel sheet with over 50thousand entries, each entry is identified by an ID code in the L column. I want a code that creates a new workbook with all entries having thesame ID code. if it's not too much, i want to have a pop-up window that asks me to enter the ID code.

View 9 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

Count Unique Values Based On Another Column

Jul 1, 2009

I am using Excel 2007, and I'm stuck with this problem.

View 4 Replies View Related

Copying A Row Based On Matching Unique Values

Jan 26, 2009

I have the following code which takes a unique value from 'sheet 1', and searches for the value in 'sheet 2'. If there is a matching value in sheet 2 it will copy the full row into sheet three.

I need to extend the code so that all values from 'sheet 1' are copied to 'sheet 3' even if there is no matching value in 'sheet 2'. If there is a matching value in 'sheet 2' however I need the entire row to be copied to 'sheet 3'

Sub moving()
Dim c As Range, d As Range
Worksheets("Sheet2").Activate
For Each d In Worksheets("Sheet1").Range("A1:A500")
For Each c In Range("A1:A15")
If d = c Then
c.Resize(1, 70).Copy
Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Exit For
End If
Next
Next
Application.CutCopyMode = False
Worksheets("Sheet3").Activate
Range("A1").Select
End Sub

View 2 Replies View Related

For Each Unique Value In Col A+B, Removing Lines Based On Column C

Jan 21, 2010

For each unique value of Order Number [col A] + Line Number [col B], I need a program which will locate the first instance of Next Stat [col D] = 530 and the first instance of col D = 540, deleting the other lines. The output will have two lines remaining for each unique col A+B combination, one where col D = 530 and another = 540. The data will always be sorted in date/time order prior to running this program. The actual data set has a varying number of lines, usually 1000+.

View 4 Replies View Related

Lookup Unique Based On Multiple Conditions ...

Jan 2, 2009

I am looking for a solution other than using an advanced data filter for unique records only.

I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).

Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.

Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?

SEE ATTACHED:

View 9 Replies View Related

Delete Duplicate Row Based On Unique ID In Column A

Feb 23, 2012

I have a set of data I'm trying to clean up. I'd like some code that would look at column A, and if a duplicate is found in A, delete the duplicates entire row. Column A contains a unique identifier code.

View 9 Replies View Related

Auto Numbering Based On Sets Of Unique IDs?

Mar 28, 2012

I have sets of data that need to be numbered example:

B21N232E6Q1B21N232E6Q2B21N232E6Q3B21N29VFAL1B21N29VFAL2B21N29VFAL3B21N2ISFHJ1B21N2ISFHJ2B21N2ISFHJ3B21N2ISFHJ4

View 3 Replies View Related

Counting Unique Values Based On 2 Criteria

Aug 11, 2007

Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............

I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.

View 4 Replies View Related

Count/Sum Unique Values Based On Criteria

Feb 19, 2009

I'm facing a big problem and I can't found a solution for days.

I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

Let me Put to you an example for you been able to understand my problem.

Example considering Range "C2:C20" exluding blank cells

(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))

(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))

They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....

View 9 Replies View Related

Counting Unique Values Based On A Variable

Dec 15, 2009

I'm having difficulty trying to write a formula for the following type of data:

[data] ....

This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...

View 9 Replies View Related

Extract Unique Records Based On 1 Column

Aug 27, 2007

I have a single excel data sheet with 10 rows of header information and then multiple rows and columns of data

I need to extract the 10 rows of header data plus the rows for each unique record in Col A into its own separate worksheet, with the work sheet name being the unique record from Col A

To further add to the challenge, the data in col A may have "/" in so will not comply with excel sheet naming convention so would like an error message to remind me to manually change a sheet name.

I attach an dummy data sheet just to show what I mean!

View 6 Replies View Related

Create Unique Table Based On Criteria

Aug 31, 2007

Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.

View 9 Replies View Related

Count Of Unique Numbers Based On A Criteria

Feb 17, 2008

My data is like:

A1: 5 -- B1: X
A2: 5 -- B2: X
A3: 5 -- B3: Y
A4: 3 -- B4: Y
A5: 3 -- B5: X
A6: 8 -- B6: Y
A7: 2 -- B7: X
A8: 3 -- B8: Y
A9: 1 -- B9: X

I want the count of unique numbers in column A based on the criteria i have on column B.
So the count for X should be 4 and for Y should be 3..

Can i achieve this using formula only?

View 5 Replies View Related

Update One Workbook From Another Based On Unique Criteria

Mar 11, 2008

I have two workbooks. I use excel 2003

I need to update the master workbook with data from the invoices workbook using the following criteria.

In the master workbook column C has unique job numbers.
In the invoice workbook column F has unique job numbers.
Both worksheets are the first worksheet in both workbooks.

If the job number in the invoice workbook is already in the master workbook do nothing.

If the job number is in the master workbook but not the invoice workbook then delete the entire row from the master workbook.

If the job number is not in the master workbook add it to the master workbook.

To complicate matters the master workbook is shared. It is my understanding that shared workbooks can't contain the macros needed ? It would be preferable that the master sheet had them but if not ...

View 4 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

Counting Unique Cells Based On Another Cell

Aug 1, 2008

I have 2 columns that I need to use simultaneously to conclude something.

Column A
Row 1: Dog
Row 2: Cat
Row 3: Bird
Row 4: Dog
Row 5: Dog
Row 6: Dog

Column B
Row 1: See Vet
Row 2: See Vet
Row 3: Get Rest
Row 4: Get Rest
Row 5: Get Rest
Row 6: See Vet

Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.

View 5 Replies View Related

Nested If Statement Based On Unique Reference Numbers

Jan 10, 2014

I have three sheets of data, each sheet has various columns of data, but within each sheet, column one contains a unique reference number (URN), which is a unique six digit number identifying an organisation.

In sheet two I need to create a new column and call this 'Filter Group'. In that column I want to insert a series of IF statements to return a value from all three sheets. The difficulty I'm having is that I can't get the if statements to work specifically for when the URN in sheet one or three match the URN in sheet two.

Sheet two is my main data sheet. Sheet one is called '2 Weeks Previous' and sheet three is called '2 Weeks ahead'. The IF statement I have created so far is as follows:

=IF('2 Weeks Previous'!H:H=1,"ENF grade 1",
IF('2 Weeks Previous'!H:H=2,"ENF grade 2",
IF('2 Weeks Previous'!H:H=3,"ENF grade 3",
IF('2 Weeks Previous'!H:H=4,"ENF grade 4",
IF('2 Weeks Ahead'!B:B<>"",'2 Weeks Ahead'!G:G,
IF('Background data'!H:H=2,"All-Good","NotRequired"))))))

View 8 Replies View Related

Counting Number Of Unique Numbers Based On Another Variable?

Apr 24, 2014

I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.

I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.

However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....

View 14 Replies View Related

Counting Unique Values Based On Condition In Different Column?

Aug 21, 2014

Formula which will count unique values in column A based on condition in Column B which "y" .....

Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....

View 3 Replies View Related

Counting Unique / Different Values Based On Multiple Criteria?

Jan 21, 2014

I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).

Program
Clinic Title
Start Date

[Code].....

formula that will automatically give me that answer?

View 2 Replies View Related







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