# Comparing Two Columns And Returning Number Of Cells In One Column With Higher Values?

Apr 8, 2014

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

## Comparing 2 Columns, Highlighting The Higher Number

Sep 1, 2009

I am trying to do is I have column C and column G. For each row, I want to highlight or change the font color of the greater number. I can do it with conditional formatting, but I am only able to do it for 1 row at a time and it will take forever considering there are over 800 rows.

Is there an easy way to compare the two columns and highlight or change the font color of the higher number in that row?

For example:

C G
4 5
8 1
6 2
9 11

So it would highlight the 5 in the first row, the 8 in the second row, the 6 in the third row, and 11 in the fourth row.

## Comparing Two Columns Of Data And Returning Values That Do NOT Appear In Both

Apr 22, 2014

I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.

I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.

I have tried a few test formulas using COUNTIF function, but with no luck.

I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)

New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx‎

## Comparing Values And Returning Different Column

May 31, 2007

I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Column A Column B Column C
Date Number Who
3/23/2007 902-555-2596=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-123-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-123-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-555-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-678-0352=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 902-555-7958=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-555-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 980-555-6621=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 902-555-7958=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
3/23/2007 902-555-7958=IF(\$B2=Sheet2!\$A\$2:\$A\$2001,Sheet2!\$B\$2:\$B\$2001,)
~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~
Column A Column B
902-555-7958Big Bird
902-555-5100Daffy Duck
902-678-0352Elmo
902-555-5752Donald Duck
902-555-7420Bert
980-123-6621Ernie
902-555-2596Grover

## Comparing Values And Returning Info From Separate Cells

Sep 18, 2013

If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).

Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45

## Comparing 2 Or More Columns And Returning A True Value

Feb 10, 2009

I'm quite new to array formulas in excel and was wondering whether or not I can advance on this formula:

=AND(\$C\$5=B7,\$K\$5=G5)

The problem is I would like to compare the entire colums (or array) and return the number of 'TRUE' statements in the array as a value.

=AND(\$C\$5:\$C\$65536=B7,\$K\$5:\$K\$65536=G5)

however, this returns all values as 'FALSE'

example of arrgument:

IF C5:C65535=customer then count how many cells in K5:K65536=completed

## Vlookup Values In 2 Columns Returning Value From The Third Column

Oct 6, 2009

My Data

Service 1 | 100 | 1.5\$
Service 1 | 200| 2.5\$
Service 1 | 300| 3.5\$
Service 2 | 100| 11.5\$
Service 2 | 125| 21.5\$
Service 2 | 300| 30\$

I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.

I tried SumProduct but the problem with my formula was:
Service 1, 200

The formula summed up 1.5 and 3.5 and returned 5

When I wanted to return one value from this table.

## Comparing Two Columns And Returning Any Word Matches Found

Mar 21, 2014

I have two lists, one has 250 items, the other 4500, both contain a String

I need to compare any word within a cell in the short list, against any word within a cell in the long list.

The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.

## Delete Row Of Duplicate Column Cells By Comparing 2 Columns Across 2 Worksheets

Mar 14, 2008

I am looking for a macro to look in Sheet 1 column A and compare the values to Sheet 2 column O. When it finds a duplicate I want it to delete the entire row in sheet 1. I dont want to have to manually sort anything if that's possible.

## How To Add Values For Amount That Are Higher Than Same Row In Another Column

Jan 21, 2010

I have two columns (let's say column A and C), i would like to add the values in column A only if they are higher than the ones in Column C in the same row... how could i do that?

The sheet looks something like this, i would like to add the values in the first column only if they are higher than the ones in the third column and then of course get the sum of them... i would also like to know how many were there in total, but i guess i could do that with a count formula i just need the parameters

\$ 67,100.00 \$ 110,000.00 \$ 165,100.00 \$ 146,034.00 \$ 239,400.00 \$ 220,100.00 \$ 90,184.69 \$ 147,843.75 \$ 168,500.00 \$ 51,011.25 \$ 83,625.00 \$ 95,100.00 \$ 89,860.32 \$ 147,312.00 \$ 198,200.00 \$ 159,121.36 \$ 260,854.69 \$ 26,000.00 \$ 129,941.44 \$ 213,018.75 \$ 235,700.00 \$ 88,869.38 \$ 145,687.50 \$ 95,600.00 \$ 27,772.54 \$ 45,528.75 \$ 43,000.00

## Comparing 2 Column (pair) And Returning Count

May 30, 2014

Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.

Column A
Column B
Column C

K
28
3

K
28
3

K
28
3

[code]....

## Comparing Text Values In Two Cells And Adding 1 To Result Column

Apr 14, 2013

I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.

So it seems I need four different functions depending on what word is in A1. In the column next to these lists of words I have the date which I'll need to use to give me the sums of same, good, and bad for each day. But first things first. I think I need to use a combination of IF functions and MATCH function, but I can't figure out anything that works. Below is what I'm hoping it will look like when I've got the right formulas.

First column
Second column
Date
Same
Good

[Code]....

## Excel 2003 :: Average Only If All Cells Contain Number Higher Than Zeros

May 29, 2013

I am trying to do the following.

Monday=0
Tuesday=5
Wednesday=10
Thursday=0
Friday=5
Saturday=0
Sunday=0

Averages
Monday=0
Tuesday=5
Wednesday=7.5
Thursday=0 (I have got a value return of 7.5)
Friday=5
Saturday=0 (I have got a value return of 5)
Sunday=0 (I have got a value return of 5)

I need to work out averages for cells higher than zeros, in other words, I need to ignore those.

Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.

I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").

I would also like to hide the annoying #DIV/0! error.

## Comparing Customer Names In Rows And Returning Text Data In Different Cells If Same

Jan 26, 2010

I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:

Sheet 1

A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase

I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:

Sheet 2

A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes

Does this involve IF, MATCH or VLOOKUP formulas?

## Comparing Values In Two Columns?

Jul 13, 2013

There are two columns A and B and values there in

A B
1 1
2 2
3 3
4 4
9 5
10 6
11 13
15 15

As you can see there are certain numbers that are missing between column B and A. I want to know which of the numbers that are present in column B are missing in column A ...any handy calculation ?

## Comparing Columns For Values Greater Than Each Other

Apr 27, 2009

I have two columns A and B. I need a count of how many times a value in B is greater than the same row in A. I don't want to insert a new column (e.g. for if statements to add up).

Can I insert a countif at the top to do that? I tried various countif variations but could not find the right one. Additionally any other option that does not involve adding new columns/rows.

## Comparing Two Rows Replacing Values In Columns

May 29, 2014

I am facing a problem in evaluating a problem. I am referencing from the screenshot of excel file. I want to replace the values of Column-C for which column-A matches with column -G. And, the remaining will remain same.

Capture.jpg

I am also attaching the excel file.

## Print Unique Values By Comparing Two Columns

Feb 24, 2014

Am looking for a technique to print unique values by comparing 2 columns

Below is example

Col A
Col B
Col C (Output expected)

1
4
1

[Code]...

## Excel Macro For Comparing 2 XLS Files With N Number Of Worksheets And Columns

Feb 24, 2014

Need a excel macro which can compare 2 xls files and highlight cells which are different in the 2 files its like a slightly advanced one >a prompt which asks file 1 after selecting file a prompt whichasks for file 2 ..then on running it should highlight cells in file two which are different from file one Both file have same layout ie if file one has n columns in n worksheets file 2 will also have same . So the comparison should be done for each worksheet in both file and cells should be highlighted ..

## Copy Rows Based On Comparing Values In Columns

Nov 26, 2012

I'm new to macros and VBA so I am wondering if it is possible for a macro to compare values in column B of sheet2 and column B of sheet3 and for the values that DON'T match i.e. unique values to column B of sheet2, copy row from sheet2 to sheet4? This will aid my processing time drastically if I can copy all the rows from sheet 2 to sheet 4 where the model number in column B of sheet 2 doesn't appear in column B of sheet 3!!

## Comparing 2 Columns With Numbers / Letters And Alphanumeric Values

May 13, 2014

I have 2 columns i want to find out which items match in each column and put the matching value in column c. I have tried Vlookup and continue to get an N/A .. I tried countif and I get either an N/A or a value error. I have tried turning the cells into text but that is not working either..

column A has about 1700 rows and column B has about 4000

MOST CELLS ARE 6-7 VARIABLES.. satrting with either 01, 02, 03 with 4-5 letters following or have a 6 digit number or 6 letter value.

## Comparing 2 Columns And Pasting Third Column

Jan 25, 2014

I have a requirement as shown below, what i want is to have another column which will display the priority of each case name as per the case id value.

The expected result is shown in TABLE 2, TABLE 1 is the requirement, I just want to have some excel formula or vba that can do it.

REQUIREMENT-
CASE_ID
CASE_NAME
REF_CASE_ID
PRIORITY

[Code] ..........

## Add Values Of Cells From Different Columns To New Column Only If Cells Are Not Blank

Feb 28, 2014

I have an excel file and what i want is to sum two different columns to a third one, however there are some important notes:

1. Column A has dates

2. Column B has numbers f.e. 25, 30, 35 etc. , which have to be added to dates of column 1 , so a new date will be created in the new column

3. Colums A and B are not next to each other.

4. Some cells of column A and column B are blank , actually columns are sth like this

A B C
5/2/2014 25
blank blank
8/5/2014 35
10/9/2014 30
blank blank

When a date value occurs in a cell of column A , then always a number will occur in column B

When a cell is blank is column A , then the respective cell of column B will also be blank

What i want is to add values of column A and B to create column C automatically ( each column has 1000 cells ) , however in case of blank cells , i want a blank cell in column C, not something like #####

Apart from this , i want in the future , when i expand column A and B , when values are entered there, i want column C to be created automatically.

## Vlookup Returning Multiple Values In Different Columns

Apr 18, 2014

I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.

Book2.xlsx

## Macro - Comparing Values In One Column

Nov 13, 2009

I'll try my best to explain what I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:

Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:

A
0
300
550
1200
1700
1900
2200
2000
1850

[Code]....

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.!

## Macro: Comparing Values In One Column

Nov 13, 2009

I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:
Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:
A
0
300
550
1200
1700
1900
2200
2000
1850
1750
1600
1500
1950
2500
4000

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.

## Comparing Cells And Return Number?

Apr 23, 2013

Thing is, I have an excel sheet for a private competition in sports games. How can I do a function, that automatically calcutes points for every "bet" against the game result?

For example:

If game finishes 4-3 I'd like to compare it for every players guessed score and give points according to this:

1. If the bet is completely correct (4-3) -> Return 10 (points). In case of tie (like 3-3, -> Return 20 (points)
2. If the bet has a correct winner, with correct goals for eather team (4-x / x-3) -> Return 4 (points)
3. If the bet has a correct winner (home/visitor) -> Return 3 (points)
4. If the bet has incorrect winner but amount of goals for eather team correct -> Return 1 (point)
5. If the bet is completely wrong -> Return 0 (points)

At least for me, this sounds more like rocket science but just wanted to check if this is anyway doable.

## Matching Two Columns Returning Location In A Column

Apr 26, 2014

I have 2 columns:
VAL0085597220EXP0000207731
VAL0122473124EXP0000207864
VAL0169412496EXP0000207879
VAL0173317027EXP0000208330
VAL0172476750EXP0000208703
VAL0173317028EXP0000209148
VAL0047743340EXP0000209666
VAL0176241996EXP0000209668
VAL0169594337EXP0014327602
VAL0047743872EXP0014327702
VAL0158185837EXP0014329002

I am trying to match where these are located in another column

## Comparing Two Column For Matching Number But The Items Compared Also Contains Letters

May 2, 2006

I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:

column a = m454 column b = fsh454-1
m543 fst998-2
m998 fsm434-1

my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.

## Get First Higher Number From A Matrix

Apr 7, 2009

I need a function that can get, from a matrix, the first number that is higher than the number in a given cell.