Tracker
HOME    TRACKER    Excel

# Finding What Numbers Are In One Column But Not The Other

## I have a large list of numbers in two columns. I need to know what numbers are in column A but don't appear in column B. For example if the numbers are 1 2 and 3 in column A but column B only has 1 and 2 I need to know that 3 is missing. the only thing I can think of is doing =IF(A1=B1,"TRUE","FALSE") however typing this a thousand times would not be practical. Is there a macro that can check column A against column B and tell me which ones are missing?

Related Forum Messages:
Finding The Middle Cell In A Column Of Numbers
I am looking for a formulas to first find the middle number in a column of numbers eg 1,2,3,4,5 3 is the middle (similar to median) thats where the calculations start...

it then assigns values of minus to the numbers above the middle and plus values to the numbers below the middle
1 -50
2 -50
3 0
4 +50
5 +50

now when it comes to even numbers eg 1,2,3,4,5,6 if i use median it divide 3 & 4 and comes up with 3.5 ........ i want it to recognize 3 and 4 as the middle numbers
and assign plus and minuses above and below the middle numbers
1 -50
2 -50
3 -25
4 +25
5 +50
6 + 50

Finding Common (repeated) Numbers In Columns Of Numbers
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.

If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.

Finding Non Zero Smallest Numbers
Find smallest numbers in the range ignoring zeros.
I want it returned as value "1", all others - as value "0".
Example: range A1:J1 contains - 5,0,7,3,6,5,8,0,9,3
desirable result in the range A2:J2 - 0,0,0,1,0,0,0,0,0,1

Finding Numbers From String
I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:

Function NumbersInString(Word As String) As Integer
Dim i As Integer
Dim FirstNumberInString As Integer, SecondNumberInString As Integer
For i = 1 To Len(Word)
If IsNumeric(Mid(Word, i, 1)) Then
FirstNumberInString = Mid(Word, i, 1)
If IsNumeric(Mid(Word, i + 1, 1)) = False Then
Exit Function
Else
SecondNumberInString = Mid(Word, i + 1, 1)
End If
End If

Next
NumbersInString = FirstNumberInString & SecondNumberInString
End Function

Finding A Value Between Two Given Numbers In One Cell
I have another post here on this forum, but I'm afaid the formula is getting so complex that nobody is able to fully understand what I want. Instead I want to find a value between two numbers and add it to some IF sentences. It will do what I want, even if it's not that elegant.

I've looked at the SUMIF function but it did not do exactly what I wanted. It finds a number or adds numbers only if they are in range it seems.

What I want is the following:
Return sum between 500 and 1000 in one cell.

Finding An Average Couple Numbers
I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.

I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.

Finding The Sum Within A Range Of Numbers
So I have multiple columns of numerical data,

I have to find the sum of all numbers between 10 and 40 within a column, NOT cell 10 to 40 but the actual number 10 and 40.

How do I do that?

Is it SUMIF? I cannt seem to grasp this.

Finding Which Numbers Make A Total
I do a lot of work in excel to do with accounts and this often needs checking against sage. When the invoices/petty cash sheets are put into sage the total amount is put in, but in my spreadsheets I need to split the reciepts. So I was wondering if there was a formula/VBA code, that if I only knew the total of the invoice would find which cells added up to this total?

Finding Sums Of Different Numbers Of Cells
I am building an inventory simulation and have run into a problem. What i want is, when i change a number in cell H4, i want excel to find the sum of C25 and the cells "H4" up. If H4 is 5 then i need the sum of C20:C25...if H4 is 10 i need C15:C25. Does anyone have any thoughts on how to do this? I have attached a sample sheet to make it more clear.

Finding Country Based On Phone Numbers
I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

Finding The Sum For Values In One Column That Are Connect To A Value In The First Column
I have two columns. One column has UPCs - some of which are duplicates. The second column just has number values. I'm trying to add the sum of all of the numbers in column two which are attached to their respective UPC. For example,

COL A///// Col B
11111111111///// 10
00000000000///// 15
11111111111///// 10
11111111111///// 4
00000000000///// 2

So, I need a third and fourth column to give me the total value for a single SKU(col A) of all the values in col B. In this example the Third column would contain the SKU, and the fourth column would contain the sum of all values in column B that are associated with the single SKU in column three. The third and fourth column would look like this:

COL C///// COL D
11111111111///// 24
00000000000///// 17

Finding Number Closest To Zero (including Negative Numbers)
a spreadsheet in Excel. I have names with scores. Then I have the winning score. I need a formula to find the score closest to zero and to display the name of the winner.

Ex: Names A1:A4 and Scores B1:B4. Winning Score in B6 and list name in B7.
Ana 16
Bob 2
Charles 8
David 11

Winning Score 10

Answer should be 11 which is David, since David is only -1 away compared to the others.

Gathering The Sum Of Negative Numbers & Positive Numbers In A Column
I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.

Finding Last Column
Using and array to go through a series of sheets and do stuff (Thanks Gerald and Von Pookie BTW). I have used code which finds the last row (varies from sheet to sheet), but not the last column (which also varies sheet to sheet).

Finding Last row
LastRow = Range("A" & Rows.Count).End(xlUp).Row

However this code doesn't seem to work for last column...sample...
LastCol = Range("A" & Columns.Count).End(xlLeft).Column

Is there a trick I'm not seeing? Does this only work using the 'Cell' function in VBA? If so, how would the line of code look? I'd really prefer finding the column letter as opposed to using the 'Cell' method if possible.

Finding Column Or #
want to look at a row and find out what column has a Y in it

Finding Column Name
How can i know name of the column.

Example:

Finding Last Used Column
I am currently using the following code to find the last cell in a column that contains data.

lLastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row

Can anyone give me the version of this that would find the last cell in a row that contains data. The code would be used in a loop so I would need the row reference to be a variable.

Finding Names In A Third Column
I have three columns of names. I need to loop through the first two checking to see if any name is in the third column and if so, place a 'found' in a fourth column.

Finding Smallest Value In A Column
In column A, I have a list of names.
In column B, I have a list of values.
I want column C to show me which name has the smallest value from column B.
In other words, if there are 5 names, I want ONE name to give me a value of 10 (for the smallest value), and the rest of the names I want to show zero.

I also have in column D another list of values.
In column E, I want to show first, second and third place amongst the list of values from column D. The rest of the names I want to show zero.

Finding Whether Value In One Column Exists Or Not In Another
I have serial numbers say 1 to 100 in column A. In column B i have values which are text and numbers combined. In column C I have similar values as in B.

Now, if any value in Column B appears in Column C, then in Column D It should tell me Yes or No or 0 or 1.

I basically want to know whether any value from Column B exists or not in column C. I tried Countif and Vlookup but didn't work.

Finding Next Empty Column
I need to append this macro to find the next empty column to place the data in. The orignal VBA works fine, but I need to go into the editor, and repalce the offset number every time I add a new row (weekly).

Here's the orignal coding:

Finding Numeric Value Of Column
how I can get the userform to close when another worksheet is selected. what I really need is for the userform to just show on one worksheet (not close) Is that possible? If not I want to be able to re-size the userform when another worksheet is selected (like getting it to minimise)

Deleting Rows Matching Numbers From Numbers In Column
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?

Delete Numbers And Replace With Numbers From Adjacent Column
Is it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them

Finding Certain Criteria And Moving It To Column
I need to look at column B find anything that Starts with BBX (ie; BBX124343, BBX1348383 ) and move it at the same location only to column A

Finding Largest Cell In A Column
I want to find the largest cell in a column so I can use “auto fit selection” without cutting off type.

Finding The Min. In Column B Between Two Occurances
Is there a way to find the minimum value in column B that corresponds to the two occurances of "Yes" in column A.

Column A Column B
NO 1
NO 4
NO 7
YES 6
NO 3
NO 9
Yes 2
[Note: the numbers are in column B.]

I basically want to return the # 3 from Column B.

Finding A Cell Based On Column
I'm looping through and finding a cell based on Column A, and I .resize(,5).select and from that selection I want to create a range called "LCrng"

Finding The Nearest Value Of A Column That Is Not Sorted.
if i have a column of number that is not sorted(it cannot be sorted due to other requirements), is it possible to find the cell that reflects the nearest number that i want to look up?

Finding Same Strings In The Same Column But Different Row
Lets say I have a column of data and there are many sub parts.

A

John
Apples
xxx
Banana

Peter
Apples
xxx
Banana
yyy

How do I modify the range. find to locate the 2nd "Apples" , 29th "Banana" and so on or is there another method ?

Finding The Minimum Value In A Column That Comes After The Maximum Value
Say I have 2 columns that in basic form look like this:

Column A Column B
Jan 1
Feb 0
Mar 7
Apr 4
May 15
Jun 2
Jul 5
Aug 4

First I want to look up the max value in this column. This is easy =max(b1:b8)
Then I want to know the minimum value that occurs after the maximum value. Thus the answer would be 2.

Finding Last Cell With Data In A Column
I have the following code. Is there any way to select a range once the last cell with data is found. I would like to be able to select whatever cell in column A is selected with the code below through E2.

Finding Data From A Four Column Table
I have a table (Sheet 1) with four columns data, A,B,C and D. There are about 60,000 entries in them. In Sheet 2, I wish to enter a value in A1 which will be from A OR C columns of Sheet1 and get its corresponding value from B or D (Sheet1) in B1 (Sheet2) with the help of a formula. i.e. IF(A1, Sheet1!A60000:C60000, then B1 = B or D of Sheet1).

Finding The Last Filled Cell In A Column
I have written several pieces of VBA code which produce a sequence of tables on a single worksheet (with the rather original title "Tables"). The code often adds tables to the end of the current set of tables, and to do this, I need to know where the next available space is.

I have a solution which I have been using for ages now, which checks each cell in an appropriate column until a sequence of 3 blank cells has been found as I can guarentee that the tables are at most 2 cells apart. It then sets i=i-3 to give me the location of the first empty cell.

Blankcount = 0
i = 3
While Blankcount < 3
If Cells(i, 3) = "" Then
Blankcount = Blankcount + 1
Else
Blankcount = 0
End If
i = i + 1
Wend
i = i - 3

Extracting Numbers :: Pull Numbers From Another Column
I'm trying to pull some numbers from another column. I want to pull the numbers that have an X separating them like 7X125, 48X192, and 27X90.

Example:

FA, VF-2000-3-7X125-18-A, AFS
FA, VF-2350-48X192-6-RGB, FC
FA, VF-2020-27X90-18-A,RFI, FEX, ACP, 2IT

Finding The Last Populated Cell In A Column Array
I have a column array with various cells in that array populated. In every subsequent cell in that array I want a formula that finds the previously populated cell and that value added a cell that is in the same row but two columns to the left.

Finding The Maximum Value In A Column Based On A Condition
This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.

I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like

Finding A Missing Date And Listing Name From Next Column
I am try to show a list of all rows that have a missing date in column "B" and then show the corrasponding name in the next column "C". I can find the first one on the sheet and how many have missing dates using:

Finding A String In A Column, Displaying YES On The Same Row
I am trying to search for a string of numbers (column 2) in an array, and have "YES" be written on the same line in column 3 if the string is found in the names ANYWHERE in column 1. Please see the desired results on the picture in column 3.

I have tried many things, including SEARCH function which can only work with 1 cell not many, COUNTIF and more advanced functions, but I think have not succeeded because of my lack of knowledge in arrays.

Finding The Nth Value If Another Column Meets A Criteria
here is a sample of the data

150
21
352
422
53
69
755
852
1224
13268
119
242
30
444
543
67.................

I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc.
I know large does it for one column and not only when the first column matches a designated criteria.

Finding Max Column Width Without A Loop?
I have a range of cells which I wish to print to a .txt document. However, I would like these cells to stay aligned, one on top of the other. I am currently doing that by finding the cell with the widest piece of data for each column, and storing the width that each column needs to be to an array of integers. Then, when printing out the range, I simply add spaces to each piece of data its width is the same as the max column width. I am finding the max column width using the following loop:

'find the width for each column and store in col_width()
For cur_col = 1 To total_cols
'skip the tag switches column
If cur_col <> 3 Then
max_data_width = 0
For cur_row = 1 To total_rows
cell_data_width = Len(Find_String_Diagnostics(diagnostic_range.Cells(cur_row, cur_col)))
If cell_data_width > max_data_width Then.................

Finding Last Cell In A Column, Select, Copy/paste
I am making a worksheet that I intend to use to track my money. When I first open the worksheet, it opens on a tab where I can click a button to report a type of transaction. For example, if I make a withdrawl from the bank for \$50, I click the button, it takes me to the sheet that tracks my bank-related stuff, selects a cell and opens up a form, at which point I type in what the transaction consisted of. However, the sheet also tracks what is in my wallet, so I'd like to finish reporting the bank transaction in the form, and have a button to click that reports the wallet part automatically.

So, essentially what I need to do is select several non-contiguous cells that are in the last row of the bank sheet, copy them, switch to the wallet-tracking sheet, and paste them in a row that is one past the last row of that sheet. The paste should keep the cells next to each other, even if they were non-contiguous when they were being copied.

Finding Minimum Difference Of All Elements In One Row Or Column?
I need to find the minimum difference between any two elements in a row or a column. While it's easy to do for a 3-4 elements by doing subtractions for all elements in the array, doing it for more elements leads to a very long formula.

For example, I need to find the difference between any two elements between C5 and C9: ....

Finding All Dates In A Column That Are In The Range Today To A Week From Now
Im trying to search a column (A), that has a list of dates (not in order), for the row in which the dates are equal to or greater than today and less than or equal to a week from today. I then want the information contained in the rows with these dates to be transferred to another sheet and ordered by date.

Finding Duplicates :: Find Duplicate Cells In A Column
I have many rows of data. How can I find duplicate cells in a column?

Finding Withing Part Of Unknown Sized Column
im trying to find and delete records within a column if they occur twice. this works great right now but I want it to exclude the top 8 rows... i think it might have something to do with the LookAt:=xlPart constraint ...

Finding Duplicate Entries In Column Considering Indirect Referencing
I wonder if there is any easy way of findinig (numerical) duplicate entries in a column? Some cells are empty, in case this might cause a problem. I do not wish to delete duplicate rows automatically, just to find them. Why not just sort it? Because indirect referenceing is used where each row corresponds to a separate spreadsheet in the workbook. What I need is to find the duplicate so that I manually can erase one of the spreadsheets for the particular case and adjusting a reference list.

Finding Data Based On Row & Column Criteria
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX

Result data(criteria base on Month & type)
JanApril
BananaXXXX
Apple
Orange

Show Sum Of Numbers In One Column IF Another Column Has A Specific Category
I have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E).

So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.

Sum Numbers In Column If Date In Other Column Matches
I have a spreadsheet which will be completed by numerous users, with a worksheet reserved for each area. The spreadsheet is to record the number of days lost to training etc on a weekly basis.

Each worksheet has 3 columns – column A DESCRIPTION, column B WEEK COMMENCING DATE and column C DAYS LOST.

The table will be completed by the manager’s as the info becomes available to them.

I will be collating the data on another worksheet and need a formula that will look in column B for all instances of 01/10/07 and then sum the corresponding cells in column C, then do the same for 08/10/07 and so on.

I have attached an example of a page.

I thought it may be VLookup or Sumif, but I don’t know how to go about it.