# Using Values In One Column To Determine Values In Another

May 3, 2008

I would like to determine the values of cells in column B by the values in column A. I can do that for one cell but not for two or more.

Here is what works:

ADVERTISEMENT

## Determine If Column Of Values Contains Sequence Of Consecutive Matches

Oct 12, 2012

I am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:

=IF(AND(A3="neuron",A3=A2,A3=A1),1,"")

And copy down. I then test column B to see if there are any non-blank cells.

If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.

I can do this with a UDF, but the user has rejected this option.

## Excel 2010 :: Formula To Determine If Duplicate Values Exist In Single Column (excluding Empty Cells)

Jun 29, 2012

1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2

Tab 2, Column A, has Unique ID's (6 digit numeric values)

The user manually inputs the ID's on new rows in Column A

Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's

When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).

One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.

I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.

The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.

## Determine If Value Is Between 2 Values

Dec 4, 2007

at present i have the following code that does as intended.

If number < pr1 Then
number = 0
End If

how would i alter this so that it changes the 'number' if number is between pr1 and another number pr2?

## Determine Values In Cells: Get The Lower Values Between To Cells And Have The Lower Valued Cell Highlighted

Oct 27, 2009

I M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is \$4.25 and cellA2 is \$5.25 i want cell A1 to be highlighted,,is there a way?

## Macro To Determine Values

Jun 8, 2009

I have two columns in my spreadsheet:

Test1 Passed
Test1 Passed
Test1 Failed
Test2 Passed
Test2 Passed
Test2 Passed

I need a macro to find all of the same values in column 1 and then look to see if column 2 all of the values are Passed and if they aren't make a new column with the value that isn't passed so after the macro of formula is run you'll have:

Test Status Overall
Test1 Passed Failed
Test1 Passed Failed
Test1 Failed Failed
Test2 Passed Passed
Test2 Passed Passed
Test2 Passed Passed

## Determine Values In A Range Are Numeric

Apr 13, 2007

I am trying to create a macro that determines if a range I am selecting has any non-numeric fields. If it finds say an cell beginning with a letter a message box appears letting the user know and possibly give the cell and value it found.

## Compare 2 Values & Determine If Outside Of Scope

Oct 19, 2007

I have two numbers I need to compare using and IF statement. The two numbers are virtually the same, and I want the IF statement to round the numbers so any difference under 100,000 is not detected. For example:

Cell A1 = \$32,194,884.45
Cell B1 = \$32,194,884.37

I would like the statement to look at this number as 32.19 (divided by 1M). I am using the below equation currently, but it is not working:

=IF(ROUND(A1/1000000,1)<>ROUND(B1/1000000,1),"CHECK","0")

## Determine The Largest Magnitude Of A Subset Of Four Values

Feb 16, 2010

I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.

I've tried the following but got an error stating it was too long:

= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))

The repeating line needs attention in my mind:

MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))

## Determine Which Values/Cells Sum To A Total Amount

Apr 25, 2008

Is there a function, or how would I write a vba to figure out the following.

I have 86 items all with a different price, which come to a total of \$348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of \$57,673.36

## VBA - Generate Random Column Values From Existing Column Values?

Aug 22, 2013

[URL]

I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.

## Extract Largest Values From Column B And Corresponding Text Values In Column A?

Oct 1, 2013

There should be a formula to extract the largest values and its corresponding values. For example, if column

Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100

The desired outcome

Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100

The second goal is extracting the top 3 largest values using a formula.

Sample file is attached for your convenience. Desired outcome is in GREEN.

## Calculate Mean Of Column B Values Based On Date Values In Column A?

Dec 5, 2013

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4

## Summing Up Values In Column Corresponding To Unique Distinct Values In Another Column?

Dec 24, 2013

I have two columns in excel, col A and col B.What I would want to achieve is following:

Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3

Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).

[URL]

## Counting Values Of One Column Based On The Uniqueness Of Values In Another Column

Jun 11, 2014

Here is a table

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail

[Code] .....

Here is some context

Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).

In this table the number of tickets that were:

submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4

[Code] ....

What I need is a formula that will produce this result.

## Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range

Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

## Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

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.

## Align Matching Values In Columns A And B And Bring Corresponding Values With Column B

May 15, 2014

I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.

C D and E don't necessarily have a value in there but if it does it needs to move with B

## Formula To Determine Required Values To Achieve Target Percentage?

Aug 24, 2014

Data is;

83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.

So, my bullseye percentage = 43209/83300, or ~51.87%.

Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.

Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.

## Finding All Alike Values And Summing Up Corresponding Column Values

Jan 31, 2012

Here's an example of what I need..

I want to

A B
2.5 corn
4 corn
7 green
2 green

I'd like a macro to find all corns, add up the column A for each corn and place the sum beside the last corn in column C

Then do the same with green, etc.

## Sum The Values In Cells Based On The Values Other Column

Apr 25, 2006

I want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:

=SUMIF(D2:D110,D112,E2:P110)

My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?

## Summing Up Values Of Right Column By Looking Up Values In Left

May 18, 2007

If I have two columns like so with Processor speed in the left and number of processors on the right:

SPEED NO OF CPU
7282
7282
7282
7281
7282
7301
7301
7302
7301
7301
7301
7301
7312
7312
7311
7311

SPEED ranging from 75 to 2000 or so Mhz, as many or as little multiples of each speed. I wish to go through this list using a seperate list of distinct values, and count the total number of CPUs for that SPEED. So, for the extract above I could manually tell you that there are a total of 8 processors running at 730Mhz

It would be nice to have a list of distinct vaues with a total count of CPUs to the right of each SPEED

730 8
731 17
756 9
834 2 etc

## Summing Values Of One Column Based On Values Of Another Column?

Feb 21, 2013

I am trying to sum certain values in one column based on the values of another column. I made a few tables, generated some random numbers, and then I started writing up an if-then statement to see if I could get what I am trying to do to work. However, I was having difficulty getting the program to run, because I was declaring more than one variable (I think).

So for instance, I want cells in column G to sum forces in column C when a joint in column E matches a joint in column A. Similarly, I am trying to do the same thing for columns F and B.

I have attached a picture, which shows what I would like for the program to do automatically (rather than me doing it all manually)! I didn't include the code I wrote, because it was so minimal

Attachment 51660

## Find High And Low Values Of Column According To Values Of Another Column?

Jul 23, 2014

Ccheck the attached sheet for the explanation.

Book1.xlsx‎

## Filtering Values In One Column Based On Values In Another Column

Apr 7, 2014

I'm currently working on a spreadsheet that contains 30,000 leads and their respective companies and i need to filter out any leads that work for companies present in a different list of companies (1,834 companies). Basically, i need to filter column A so that it only contains companies present in column B. Is there a way to do this using the advanced filter?

## Match Values In One Column To Values In Another With A 3rd Column Included

Nov 26, 2009

I have about 360 records in Column 1 and less columns in Column 2 (333 in this case). All the values in Column 2 are in Column 1. I need to be able to match those two columns up while keeping the data in Column 3 intact with it's corresponding value in Column 2. FYI I searched and found this thread: ttp://www.excelforum.com/excel-work...n-another.html

But my problem is that I have to tie in the values with column 3 to their corresponding values in column 2. In order to illustrate, I have attached an excel file in which I did the operation manually. (note: all values in column 2 are indeed in column 1 but not in the example as I have shown for the sake of convenience).

## Sort Values In Column B To Match Values In Column A?

Jul 8, 2013

I have two DB extracts for 2012 and 2013 with customer id's that no not repeat across the year - columns A and B. I need to arrange this from:

2012
2013

270806
330011

330011
350529

350529
369916

to:
2012
2013

270806
0

330011
330011

350529
350529

0
369916

So the major idea is that the customers that are extinct in 2013 would appear as 0 in the Column B; customers that are the same would be aligned vs the previous year and new id's would appear on the separate row.

## Find / Replace From Column A Values To Column B Values VBA

Mar 6, 2014

I have a workbook with 20 sheets in English Language, but I must to translate in Russian. Translating has done by human translator, so translation is good. I must change phrases and words in english to russian. I can use find/replace function but doing by hand it's very tie consuming. I need VBA or macro who takes values in column A, finds it in entire worksheet and changes it to matching value in column B. Is it possible and how I can do it?

## Summing Values In Column Based On Values In Other Column

Nov 18, 2009

I have a vba form with three comboboxes. The first combobox contains a list of employees, the 2nd a list of work activities and the third a list containing hours. As each employee enters the data for their name, activity and hours these values are saved to sheet6 starting at A10, B10 and C10. I want to be able to sum the hours for each employee who enters data. So the completed user form could look like the following:

HTML Employee (A) Activity (B) Hours (C)
Joe Swept 1.25
Raked 1
Bill Mowed 2
Cleaned 1
Joe Trimmed 1

So as you can see there could be a blank cell in column
"A" under Joe and Bill and Joe could come back later and
enter more info. What I want on sheet6 say starting at A30 the value
Joe would be entered and in B30 his total hours 3.25. In A31
Bill would be entered and in B31 his total hours 3 and so on for other
employees

## Copy Values From Column A Based Upon Values In Column B

Jan 2, 2010

If values in column B are alike or if only one value exist in column B, I would like to copy values from column A over into columns D and all other columns to the right of D,

Before and after examples are below....

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