Finding What Numbers Are In One Column But Not The Other

Nov 10, 2009

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?

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

I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below

a b c d

1 Tom 333 2 Sam 22 3 Sam 22 4 John 5 5 Sam 22 6 Sam 1 7 Tom 3 8 Tom 333

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.

Is there a function in excel that can look at an array of values and find the two values that equal a number.

I have a number 159. I have an array of 100 numbers but only two of them added together can equal 159. Rather than sort through every possible combination is there a function in excel that can do that?

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.

Create a form where i would enter 2 numbers into 2 seperate boxes, and those numbers would be highlighted/bolded/shown in red from a list of 30 rows of numbers x 20 numbers in each row. these 2 numbers will not be the same all the time, below is just for an example

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

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

I am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?

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.

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?

In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.

for example

in column A i could have "Hello how are you 01237232 I am fine"

I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.

The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.

I have an excel file which contains two sheets. In one sheet there are some numbers in a single column. In the other sheet again there are certain numbers span across rows and columns (a number may be duplicated). I need to check whether there are numbers which exist in both the sheets. If such a number is found it may be marked (say with a color) in both the sheets.

I have got the following issue. I have got a large list of values in a column. I need to detect the the ones which are in non-consecutive order and display the difference in single numbers. For example:

1 fine 2 fine 3 fine 7 - 4,5,6 10 - 8,9

In other words I need to find the missing values and get them displayed.

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.

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.

I am trying to find duplicate numbers in sets but so far I can only highlight the ones that are in exact order. I need to find each set that has the same numbers, in any order. Example..

I will provide an example of sets of 3. But I get 3, 4 usually but sometimes 5 or 6.

I get them from different people.

Person A- 234, 569, 498, 849, 848,343,567,347 etc...

Person B- 432, 596, 677, 566, 565,433, 455 etc..

Now I need to find each set that has the same numbers, any order. Like 234 from A and 432 from B would be the same, so I would need to highlight them 2 sets. But I can not figure out how to do this. For Excel to highlight it they have to be 234 and 234. Does not recognize same numbers, different order.

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.

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.

Please find the attached MS Excel 2010 file <average set.xlsx>.

There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.

Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.

The Column H and Column I also same as like above , but here negative set of numbers starts first.

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

I have a mass of data which look something like this:

table removed

and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.

So basically for example I would want the top row to now read:

table removed

and then apply the same procedure to the other ranges below this.

Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.

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.

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

I'm working on a spreadsheet with about 400 rows and 10 columns. All the numbers in the columns are formatted as text, which doesn't allow me to use AutoSum. I have selected the cells and formatted them as number with no decimals, but AutoSum still doesn't work because the formatting hasn't changed and they're still formatted as text. I know if I double click each cell, the cell changes the format from text to number, but that will take a lot of time to change 400 cells in each of the 10 columns. What can I do to quickly reformat the cells from text to number?

I have copied column A from a PDF file. It contains entries like this, all in the same column:

5 0.002HP Direct- 26845 7/1959 Airesearch

This column contains an ID # (5), a description (0.002HP Direct), a part # (26845), a date (7/1959), and a manufacturer (Airesearch). All this info is in one column. This column is 11,000+ entries long.

I have column B which is 45,000+ entries long, and contains ONLY part numbers, like this:

325111

I would like to compare column B to find the matching results from within the text of column A, and put these matches into column C.

augmenting current code to select the column after a column. Current code is:

Code: If Sheets(data_sheet1).Cells(2, iCol).Value = "Stations" Then TargetCol = 1 If Sheets(data_sheet1).Cells(2, iCol).Value = "Agency" Then TargetCol = 2 If Sheets(data_sheet1).Cells(2, iCol).Value = "Advertisers" Then TargetCol = 3 If Sheets(data_sheet1).Cells(2, iCol).Value = "Product Code" Then TargetCol = 4 The next line should find the column after "Product Code".

I have a working formula that gives me the last value in a column using Index formula below. How can i slightly adapt the formula to find the maximum value in that column instead?

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.

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.

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).