Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


How To Find The Most Common Numbers

I have 300 number groups and each group has 22 different numbers (from 1 to 80).

Can I find the most common 5 or more numbers in these groups by excel, macro or any program?

for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Find Common Numbers Between 2 Columns
I need to find out common numbers between columns. Each column has multiple number entries seperated by a comma. For example:

Column A: "5592,12222" and Column B: "1,2,3,4,5592,123123" and Column C: "3, 4"

I need to find out any numbers that are found in more than 1 column. Hence, the result should be something like "3,4,5592". If no match is found then maybe can indicate by coloring the cell. My data is over 50,000 rows and 30 columns. I need to do this for each row one by one.

View Replies!   View Related
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.

View Replies!   View Related
Pull Out The Top Five Most Common Numbers In A Block Of Numbers
I have a large block of numbers in a spreadsheet and I am wondering how I can look at the whole block and pull out the top five most common numbers. So perhaps the number 4 is repeated the most times, it would be number 1, number 18 might be repeated the second most number of times so it would be in the number 2 slot etc etc.

View Replies!   View Related
Check Multiple Locations For Common Numbers
I am currently trying to automate some excells workbooks that my company uses. The one i'm working on is a listing of all the change orders we have. Unfortunatly there are roughly a 1000 rows, each containing the information for the change- change #, Date opened, part numbers, change description, status, date closed.

Order 0001 - 10/22/08 - 0156, 7251, 9901 - delete bag - closed - [blank] -
Order 0002 - 10/22/08 - 0018, 0612, 0875 - add notes - open - [blank] -

What I want to do is type a part number in, it checks to see if there are any rows that already have that number and have an open status (as apposed to closed) and then tells me where the conflict is. so in the above example if I typed in 0612 it would tell me that number is already in use. I am using excel 2003 if that makes any difference.

View Replies!   View Related
Calculate Highest Common Divisor Of 2 Numbers
Assume Cell A2 + A3 as the fraction: 60/100. How can I get the smallest fraction (3/5 in cells B2+B3). I do not want to use the build in cell format for fractions)
There are a few samples of want I would like to get a s the result (Red Digits). I will appreciate 2 ways: via VBA Code and via Sheet Build-In Functions.

View Replies!   View Related
Find Common Values In Two Lists
I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two.
I am using this formula currently, but is returning an incorrect result--

this is the record number in M2

it is returning
as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions

View Replies!   View Related
Find Out The Most Common Keywords In Column
In column A I have 50,000 cells, each containing 1 to 10 keywords. For example
A1 = "jobs"
A2 = "jobs in milton keynes"
A3 = "it jobs in milton keynes"
A4 = "sales jobs in milton keynes"
A5 = "well paying brickie work in spain"

At first I was trying to find out the most common keywords in column A, and I used the following code to do so

View Replies!   View Related
Find The Lest Common String-value In A Range
Everything is explained inside the attached WB.

View Replies!   View Related
Conditional Formatting - Find Common Material
What i am trying to do is to to determine the common material that is
used among different model do product in a product family. I have the
column C the various part number for the product family. Each product
model is made up of different combination of the parts.

In I3:U3 i have the model number for each product. Under each are the
combination of various part that make up each model. What i need to do
is in column G conditional formatiing that if all the different model
use a particular part (part number). The respective cell in column in
the row will be color. This will help me to determine what are the
parts that are common to all the product.

Column C Column G Column I .........................Column U
Part no Common Product 1 Product 2 Product 3 Product 4
12-1234-56 no color 1 4 0 6
13-2345-45 color 2 3 2 2
14-1234-56 no color 0 2 4 2
14-1234-56 no color 0 2 2 2

View Replies!   View Related
Remove Duplicats And Find Common Data
I have a list of parts in Column A, often times with duplicates in the column.
I have a list of parts in Column B (from another source), again, with duplicates.

What I need is - leaving columns A and B alone;

Column C to be unique Column A parts
Column D to be unique Column B parts
Column E to be parts in common between Columns C and D.

View Replies!   View Related
Find Common Words In Cells And Create New List
I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on.

Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this:

Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439.

Then further down the list, there may be another part listed like this:

Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369.

If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench.

So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together.

I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.

View Replies!   View Related
Find/Count Most Common Words & Phrases In List
I am attempting to take a very large list of keywords, and find the most common words and phrases within them. For example, if I had a list that said:

excel formulas
excel spreadsheet formulas
excel help
excel formulas help form
formulas for excel

I would like to come away knowing that "excel" and "formulas" are common words within the list.

Currently, I believe this can be accomplished by doing the following:

1. Break down each line into all of its possible combinations.This would mean that the line with "excel spreadsheet formulas" would return:

excel spreadsheet formulas
excel spreadsheet
spreadsheet formulas

2. Once the entire list is broken down into its many parts, use the pivot table feature of excel to determine how common each of the parts is within the entire data set.

So, my questions are these:

1. Do you believe this is the best way to solve my problem? If not, what would be the preferred method?
2. If this is the best method, what function or script would I use to accomplish the first step of breaking down the lines into their individual parts?

Mike Auto Merged Post Until 24 Hrs Passes;It appears I put too many characters in the title of my post. It should read: Common Words - Decomposing Text Phrases

View Replies!   View Related
Find The Formula That Is Used To Find These Numbers
The first 7 digit number is the input to a certain equation. This equation outputs the 7 digit number after the space.

Input (7146127) ==> Equation ( Unknown) ==>Output (2871082)

These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.


View Replies!   View Related
Find Missing Numbers
I have a file with approx 3600 rows. In Col B there is a 13 digit EAN-13 code. I would like to find out which numbers are missing. I have to compare each cell from top to bottom in the B-column to the cell below and find out if there are some missing numbers. If the numbers are incremental then goto next cell. But if there's a gap in the numbers then it should insert the missing numbers with an increment of 1.
I think the logic statement should look like this:

i = 1
For x = 1 to last row
If the gap between B(i) and B(i+1) < 1 then
Insert a row and put the missing value in the cell on the new row.
i = i+1
Next x

The comparison has to be made from the 5 digits in place 8-12 in the code.

Here's an example (i've dimmed the digits that shouldn't be compared):


As you see there's a missing number between the third and fourth row. Here the value should be 733098592074 + the checksum that is a bit tricky to calculate.
Between the fifth and sixth row there are four missing numbers that has to be filled in.
The checksum is OK to leave out at this moment. The important thing is to get the 5 digits.

View Replies!   View Related
Go To Next Worksheet To Find Corresponding Numbers
I have a file with 2 worksheets: Worksheet "Data" contains Store Locations, Store Numbers, Categories, Brands, Sales, Clusters, % Demand, etc.

Under % Demand on Worksheet "Data", I need a formula to look for % Demand from Worksheet "Stores" that related to the same categories, brands and stores numbers.

View Replies!   View Related
Find The Frequency On Numbers
(1) i have a project of a series of numbers running left to right in a random order what i am after is to sort them into order

(2)i want to find the frequenzy on numbers and how often they apear with other numbers compairing one line to the rest all the way threw

View Replies!   View Related
Find Sum In List Of Of Numbers
I have a list of numbers in a column and I need to find which numbers
when summed together equal a figure. I have a list of invoice amounts
that I need to match up with payments (the payments are always made for
several invoices so I need to come up with sums of several invoices to
get to this payment amount).

An example would be I have this in the following section (A1:A10):

I need to find which combination of these figures would sum $1,173.76.

View Replies!   View Related
Find Average Of Last 4 Non Zero Numbers
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.

The sheet looks something like this, numbers are all in one row:

7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32

I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.

View Replies!   View Related
Find The Numbers That Add Up To A Certain Number
I have a worksheet that has a column with amounts in it. I would like to find the numbers in that column that add up to a certain amount.

View Replies!   View Related
Vlookup Cannot Find Numbers
I have a very large database (42455 lines) which has three columns. The column A is a shipment number which is a combination of numbers and letters, column B is a ship date, column C is a dollar value for each shipment. This range (A2:C42455) is named SHIPMENTS. I have a second database right next to it that contains warranty information. Column D has the shipment number, Column E has the date the warranty call came in. These last two columns have only 2788 lines.

I'm using VLOOKUP in column F to match Ship dates with Warranty Call dates. The formula is:


and was dragged down all 2788 lines.

Out of the 2788 lines it did not find dates for 301 of them. Some of those 301 have Shipment numbers that are only numbers (no letters). As a test I copied one of the "unfound and all numbers" shipment numbers and did a control+F for all of column A and it found it. Clearly VLOOKUP is missing some of the Shipment numbers. I made sure that the Shipment number in column A matches the number in column D by cancelling out of the Find window and clicking on the found cell. Then I made sure there are no stray spaces tacked onto the end of the number and there were none. I hit enter.

At this point excel starts calculating again and when I go back up to the VLOOKUP (In column F) it has now found the data I've asked it for originally. I've not changed any of the values of either cell...All I've done is use Control+F to manually find the Shipment number in SHIPMENTS and then verified that there were no spaces in the found cell by clicking on it and using the arrow keys. Once that was done I hit enter.

Somehow this sequence of actions allows VLOOKUP to now find the cell.

View Replies!   View Related
Macro To Find Middle 3 Numbers
Dim varUserInput As Variant
varUserInput = InputBox("Enter 3 Digit Number:", _
"Project Number", "")
If varUserInput "" Then Exit Sub

View Replies!   View Related
Find And Replace :: Code Numbers
Hey Have a spreadsheet with about 150 worksheets in.

In Column B of each worksheet is a list of packages


Value Added Processing
Business Viability
Climate Change

I need to add into that other cells to further divide those packages so it will end up looking like

Value Added Processing
Business Viability
Climate Change

I have a separate worksheet which has the 37 different package types along ROW A from Column A to Column AH, then going down underneath each of the heading is the various code numbers as shown above.

Has anyone got a VBA code which will allow excel to search the worksheets, find the package name and then replace it with the package name but also insert the code numbers below it.

View Replies!   View Related
Find Last Used Cell And Align All Numbers In Same Row
Example Table A:

*ABCDEFGHIJKL1No. 1No. 2No. 3No. 4No. 5No. 6No. 7No. 8No. 9No. 10No. 10No. 10221418269124333324171165141728884321000125067775100860615906015712170712180910

Result Table B:
*ABCDEFGHIJKL1No. 1No. 2No. 3No. 4No. 5No. 6No. 7No. 8No. 9No. 10No. 10No. 10263214854241121253210609433610178011412288370151076151767788070121206015907910

What i want here is to align all the numbers considering the last used cell. In example table a; the last number is 0 (F8). Considering that, in the Result table B all the numbers are aligned in row 8 from the bottom to top giving an equal alignment to the table.

View Replies!   View Related
Find The Position Out Of Played Numbers
*ABCDEFGHIJKLMn8OPQRSTUVWXYZAAABACADAE1n1n2n3n4n5n6n1n2n3n4n5n6n7n8n9n10n11n12n13n14n15n16n17n18n19n20n21n22n23n24Position 24567141517192223272834353639404143464748493;223268103048456714151617192022232830343637383941434446485;16;20:24421432364148456791014151718202328303438394041444647485;9;2159173537384645679101416182023273032343839404144474849620253542454645679101416182327283234353738394043444647497131423414748567810141617202528303435363839414244454647

I have selected numbers to play in the table shown above from cell G2:AD7 and celebrated draw result numbers are shown in cells A3:F7

Now, suppose I want to play the numbers, which are in cell G2:AD2 and the draw-celebrated numbers are shown in the cell A3:F3.

Now I want to check cells A3:F3 with G2:AD2 and want to know in which position are the celebrated draw matched with the numbers played.

In this case the result in AE2 will be 3; 22 that means only two numbers are matched with the played numbers. Those numbers are the number ‘6’ which is in the position 3 counting from G2 and number ‘48’ which is in the position 22 counting from G2 again.

The result must be shown in column AE for all the draws as shown in the table above.

View Replies!   View Related
Find All Numbers That Equal To 9 In The Range
I have a range of values in a1:f500 the values are all numbers between 1-25. I then have a value in cell h1. what i need to do is find all numbers that equal to 9 in the range a1:f500 and replace all these 9's with the formula =$h$1 so that i can simply change the values in the range a1:f500 by changing the value to say 12 in cell h1. could someone please text me the vba code that needs to be entered for sheet 1?

View Replies!   View Related
Find And Replace Phone Numbers
I have a bunch of phone numbers that I'm trying to reformat.

Say I wanted to replace any phone number like this: 1##########

With this: ##########

Keeping the numbers intact, just getting rid of the 1 in front of them. Can't figure it out for the life of me.

View Replies!   View Related
Find A Combination Of Numbers That Equal A Given Sum
If I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?

View Replies!   View Related
Find Where The Sum Of Numbers In A Row Equals A Set Number
Is there any way to find where in a row of several columns, the total will equal a specific number or cell?

View Replies!   View Related
Find Missing Numbers In A Column
i had a worksheet in which i had a column Named Cheque No. i want to print the missing cheque no's in a series of cheque no's.The logic here is that there will be different series of cheque nos for different transactions.Just check out my worksheet.

View Replies!   View Related
Find In A Range Of Serial Numbers
Sheet4 in my book looks like this:

I......Invoice No…..…Starting Serial No……...……Ending Serial No
2…………ND12………..6011000000013652……. ..6011000000103652

note that the last 4 numbers of the series are randomly generated for security reasons and are not part of the range of the the serial No’s

So Invoice ND = 10 consecutive numbers, Invoice ND13 = 14 and Invoice RF4 = 1000 and RF4(again) = 8

The list on this sheet is a mile long

What I need is a “search box” with two windows in it

In one box I will type a serial number and in the second box I will get the Invoice number

The challenge is that the serial number could be between Starting and Ending serial No’s thus if I were to type serial number
6006000001086598 in the first box the answer in the second box will be : RF4

View Replies!   View Related
Referencing To Find Pull Numbers
I want to input numbers into a master sheet by having it referenced to a name. For example; I will be inputting a report on monthly sales, which contains a company name, and then a value. However, this report doesn't always necessarily contain all the companies listed on the master. I want to simply put this report on a page, and have the referencing pull the numebrs to the approriate company on the master page.

In the attachment - I want the numbers from Sheet 2 to be entered into their appropriate match in the Master sheet (sheet 1) - without copy and pasting (becuase in reality, I have 200+ company names and numers I'm dealing with).

View Replies!   View Related
Find And Highlight Specific Numbers
I have a spreadsheet with a stock list. Each item has an individual number. I have a code below, which I have doctored from other various sources. The problem I have is that if I search for number '12' the spreadsheet highlights every cell with 12 in (120 121 122 123 etc...) Is there any way I can change the code so that it returns only the 12 and not every cell that contains 12? The current code is as follows:

View Replies!   View Related
VBA Find Cells Containing Numbers Or Alpha
i need to see what cell in the collum is empty. how can i do that in vb?

View Replies!   View Related
Find Values Not In A List Of Numbers
I have a table with numerous business units in column F. The business units are shown as a number. i.e. Business unit # 00007, 00041, etc. There are around 500 different business unit numbers. I have a list of 60 specific business units that I want separated, leaving a remainder of 440 others. I want these 440 other business units to stand out, ideally with conditional formatting.

View Replies!   View Related
Find Numbers In Text Strings
I have several cells in a column that look something like this:

Cell A1: abc 1234 def ghi
Cell A2: xxxx aa b 245 qqqqq
Cell A3: abcdefg hij kl mnopqr s

Is there an excel formula or combination of formulas I can use to identify:
(1) whether any given text string (such as those above) include numbers, and
(2) what the first number (which could contain 1-4 digits) contained in the text string is?

View Replies!   View Related
Using Vlookup To Find Phone Numbers Online
How can I create a vlookup function to automatically retrieve phone numbers off using my current mailing list?

I have a list of more than 4,000 of my customers. I want to ad in phone numbers automatically. I currently have the first and last name in one column. The address, city, state and zip are all each in their own column.

I have never use vlookup before, nor have I ever used excel other than for data entry. What is the easiest and most efficient way to do this?

I searched this site and seen vlookup mentioned for this type of automatic data entry. There may be another way or method I am not aware of.

View Replies!   View Related
Allocate Numbers To Letters And Find Average
formula to do the following:

Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter.

So in a 5 cell range the values could be:

blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).

View Replies!   View Related
Find/replace Leaving Numbers As Text
I have a column of invoice numbers, formatted as text, some of which have first character "0" & last character "C" e.g. "012345C". I want to get rid of the "C" leaving e.g. "012345".

I have tried Find "C" / Replace " " with the undesirable result being that the leading "0" disappears too. Find "5C" replace "5" gives the same.

View Replies!   View Related
Find Cells Contaning Text Not Numbers
I need to find cells containing text (Alpha characters only) in a single column, an return the row(+1 then -1). The column contains Group names (Unknown) , and under each name follows numeric data until the next group name. The cells are not formatted other than BOLD names. I need to exctract the numeric data in groups.


I have manged using the following code, but this always assumes there is an "e" in the name, but would prefer a more foolproof method. (I used "e" cos it is ok so far, BUT this could change in the future)

Sc = "A"
Sr = "1"
With Worksheets(1).Range(Sc & Sr & ":A500").................

View Replies!   View Related
Conditional Formating To Find Largest Numbers In A Selection
I am trying to create a macro that works with a range of cells and identify the five largest numbers in the range. The following are the steps.

1. I select a range of cells and run the marco.

2. The macro will identify the largest number in the selection and make the cell color red.

3. Then it will find the cell with next largest number and turn the cell orange...and so on till the 5th largest number.

View Replies!   View Related
Parse Through A Column Of Numbers And Find A Repeating Pattern
I need a function or VB code to parse through a column of numbers and find a repeating pattern. The column has some initial numbers that do not fit the pattern and I need those returned along with the repeating pattern.

View Replies!   View Related
Find Numbers From A List That Make A Specific Total
I have a list of numbers and want to see if the sum of any of them exactly makes up a specific larger number. Any quick way to do in excel? Eg do any of the below together make the exact total of the number at the bottom.


View Replies!   View Related
Macro Or Formula To Find The 2 Most Frequently Occuring Numbers
I need to know from the combinations below which are the 2 numbers that appeared the most. Example....

View Replies!   View Related
Find Duplicate Table But With The Numbers In Each Of The Cells Doubled
I have a table that is 94 columns *12 rows each cell has a different number I need to find a way to duplicate the table but with the numbers in each of the cells doubled.

View Replies!   View Related
Find Number In A Cell With More Than 2 Numbers Separated By Comma
When the entry in a cell is of the format e.g 3,4 is there any way to find the value 3 or 4 in that cell?

Attempts like:

Cells(i, 1).Value = variable (e.g variable=3)


Set cell_found = Cells.Range("A1:A10").Find(variable, lookat:=xlWhole)

do not find the cell with the 3 (or 4). The above work fine when 3 or 4 are alone in the cell. Is there anyway to find these values in the cell?

View Replies!   View Related
Return A 4th Element From 3 Common Ones
My main report (Report 1) has part number, serial number, and work order information, among other things I need. Report 2 contains this information as well, but also has a work order item number, which is needed in Report 1. Otherwise, Report 2 has nothing else of interest.

What functions will allow me to get the item number information I want from Report 2 into Report 1?

I have spent a lot of time trying various combinations of VLOOKUP, SUMPRODUCT, IF, etc with no luck. There is probably a very simple (I hope!) thing that I am overlooking.

View Replies!   View Related
Sum Cells By Common Date
I have a spreadsheet that lists two columns - impressions and clicks - by date. Sometimes the same date is listed in multiple rows. What I would like to do is add the columns so that I have a total number of impressions and a total number of clicks by each date.

For example my data could look like:
Date || Impressions || Clicks
01/01/09 || 5 || 6
01/01/09 || 3 || 4
01/01/09 || 4 || 7

and I would like it to look like this:
Date || Impressions || Clicks
01/01/09 || 12 || 17

A shortened spreadsheet is attached. The actual one is for the past year with over 1000 rows, which is why I'm hoping someone can point me in an easy direction to do the summing.

View Replies!   View Related
Merging Data In Common
I have two spreadsheets that have one set of data in each column in common.... simply put it is a number.

From the one spreadsheet, I want to pull the column that is to the right of the number in column and merge it with the other data.

I am trying to figure out a formula that will accomplish this... I have attached a file to show the two spreadsheets and my end result I would like to have.

View Replies!   View Related
Build List From Common Cells
Got something I need to do that I have never done.

I have a column on a worksheet1:

I have a column on another worksheet2:

In the column next to the above on worksheet2, i want to pull the numbers in for that (the numbers are contained in ONE column):
NBC | 1
ABC | 1 2 3 4
CBS | 1 2

Im not sure how to go about this?
In the example I attached, on sheet 2 column B, that is where I want the numbers to build to.

View Replies!   View Related
Copyright © 2005-08, All rights reserved