# How To Find The Most Common Numbers

Apr 9, 2009
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 11 Replies
ADVERTISEMENT
Aug 26, 2008

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 6 Replies
View Related
Apr 2, 2014

I have a range of cells containing a string of numbers separated by ';' e.g.

COLUMN A COLUMN B

IDENTIFIER_011122;1942;2011;1869;2642;2729;2731;2655;2678;2341;2347;2354;2332;2506;1809

IDENTIFIER_021122;1942;2011;1869;2642;2729;2731;2655;2678;2207;2209;2217;2220;1880;2506;1809

IDENTIFIER_031122;1942;2011;1869;2642;2729;2731;2732;2648;2255;2207;2217;2220;2387;1880;2506;1809

IDENTIFIER_041122;1942;2011;1869;2642;2729;2731;2732;2678;2207;2209;2217;2220;1880;2506;1809

IDENTIFIER_051122;1942;1971;2011;1869;2642;2729;2731;2648;2678;2342;2354;2355;2207;2209;2217;2220;2398;1880;2506;1809

IDENTIFIER_061122;1942;2011;2169;1869;2642;2729;2731;2732;2678;2341;2343;2347;2354;2207;2209;2217;2220;1880;2506;1809

IDENTIFIER_071122;1942;2095;1869;2573;2587;2648;2655;2673;2207;2209;2220;1880;1809

IDENTIFIER_081122;1942;2095;1870;2642;2648;2673;2678;2341;1880;2506;1809

I am looking to find the top 5 most common/repeated numbers within this range. I have found a formula to work out the largest number within the range:

=MAX(IFERROR(0+TRIM(MID(SUBSTITUTE(";"&B3,";",REPT(" ",LEN(B3))),(ROW(A$1:A$200)*LEN(B3))-1,LEN(B3))),0)) (REF http://www.excelforum.com/excel-gene...e-numbers.html)

How to modify it to work with e.g. the mode function.

I know I could separate all the numbers out using 'text to columns' but I will need to work this out regularly on large quantities of data ...

View 13 Replies
View Related
Mar 21, 2008

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 9 Replies
View Related
Apr 7, 2009

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 6 Replies
View Related
Oct 23, 2013

I have almost 300-400 records of such data which needs formatting. There are n number of IDs in the first column & corresponding data in columns 2 & 3.

For those rows which have identical ID numbers, I need to fill those rows with some unique shade of color. If you look at the next sheet, I have manually colored the rows & that is my desired outcome. The shade of color does not matter. It just needs to be unique for each ID.

View 2 Replies
View Related
Mar 11, 2013

How to find the greatest common divisor of two numbers? I can not use the built in common divisor function.

View 4 Replies
View Related
Oct 22, 2008

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 2 Replies
View Related
Sep 6, 2007

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 5 Replies
View Related
Dec 11, 2013

I have a fairly large timecourse dataset and I need to find all common values within all 3 columns. Also, when I find these 'common values' is there a speedy way to retrieve data in the same row that is associated with these values, instead of going back one-by-one and copying and pasting beside the value that the function has returned?

View 10 Replies
View Related
Feb 1, 2009

Everything is explained inside the attached WB.

View 4 Replies
View Related
Jan 1, 2014

I have 3 sets of data in three columns A, B and C. Data in Column A is the base data, now i have certain common records in columns A, B and C. Considering the column A as the base, I want to find out in Column

D = Common records in A and B,

E = Records only in A not in B

F = Records only in B not in A

G = Common records in A and C,

H = Records only in A not in C

I = Records only in C not in A

J = Common records in A, B and C

if possible also in Column

K = Common records in B and C

L = Records only in B not in C

M = Records only in C not in B

I have about 5000 records in column A, B and C each.

View 4 Replies
View Related
Oct 12, 2009

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"

etc

etc

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 7 Replies
View Related
May 10, 2007

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

=INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)

this is the record number in M2

BU1HAD80

it is returning

BI1METBRSM

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

View 9 Replies
View Related
Mar 29, 2006

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 9 Replies
View Related
Jul 25, 2012

I've found a vb script via Google that displays the most common words in a list followed by a number.

Code:

Sub HTH() Dim vArray As Variant Dim lLoop As Long

Dim rCell As Range With CreateObject("Scripting.Dictionary")

For Each rCell In Range("A1", Cells(Rows.Count, "A").End(xlUp))

[Code] .......

At the moment this results in 1 word followed by a count.

How do I alter this so it shows 2 or 3 words, ultimately showing the most common phrases?

View 1 Replies
View Related
Apr 24, 2013

Problem description: I need to put together twelve arrows each comprised of four components; field point, front insert, shaft & fletch and nock. Therefore, I have forty-eight components in an Excel table. Each of these components may differ slightly in weight. My objective is to mix and match all of the components to create as many arrows as possible of the same exact weight.

Two questions. 1) How should I be thinking about setting up the problem? 2) Which Excel function returns the results I'm looking for?

Total weight

Point

Insert

Nock

Shaft

?

100.1

109.7

20

179.1

100.1

110

View 5 Replies
View Related
Aug 18, 2009

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 9 Replies
View Related
Dec 4, 2013

Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:

Capture.PNG

- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on.

- I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.

I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?

View 4 Replies
View Related
Apr 1, 2014

I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears

View 5 Replies
View Related
May 29, 2005

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 9 Replies
View Related
Jun 25, 2008

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

excel

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 3 Replies
View Related
Oct 24, 2013

The old thread is here: [URL] ....

There are three sheets in the workbook, Project, Tasks and Details and the expected resulting sheets are RESULT, In_Tasks_but_NOT_in_Projects and In_Details_but_NOT_in_Projects .

But now what I am looking for:

1. Copy the Projects data as is in the RESULT sheet.

2. Then in the Tasks sheet, if the ID matches paste the matching rows under the data from Projects (as in the result sheet with Orange colour)

3. If the ID is present in Tasks but NOT in Projects then copy it into the In_Tasks_but_NOT_in_Projects sheet.

4. Then If the ID and the Name in the Details tab matches with the data in the RESULT sheet then paste it under the ID and Name (as in the result sheet with Green colour)

5. If the ID does not match the ID in the results sheet then copy that row into the In_Details_but_NOT_in_Projects sheet.

The result of the current macro that RHCPgergo worked with are in the last sheet.

The formatting and colour of the rows doesn't matter, it is more of nice to have.

View 14 Replies
View Related
Feb 11, 2013

compare some list of values in order to fiind the common values.

View 1 Replies
View Related
Jul 14, 2013

I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.

******** language="JavaScript" ************************************************************************>

Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

(A)boutA1=ABCD1This WeekLast WeekMissingAdded

21002103110311002310271032103210274103310331034104051035103410361055610371036

165571038 81040 91055 101655 Sheet1 [HtmlMaker 2.42]

View 2 Replies
View Related
Dec 26, 2009

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.

71461272871082

53820056426572

61070709150530

75833373805331

55466908456008

49264716615511

12387558353260

20013928484175

67975657084961

38006283229553

68973249414920

65179634653276

66971872613128

59646203411273

69785611400836

58284602307740

64723009925464

57930863495088

58816593932906

62736335606616

52119154778580

60043359437317

61921553996320

51507747216899

13197956063818

64210347411256

65399671193442

55656585589500

55148886441637

30850639587521

69081133766387

54139417804940

53408393698534

62310293191095

30512556590168

14514168265124

89078208134056

85177364363577

68714858216023

17584065849687

52691521917548

9840524785846

59268687494601

89374963791119

45989738961270

65040033345415

15061518946940

55392084949214

45103824265242

84181944909073

View 9 Replies
View Related
Apr 9, 2014

##UPDATED## Hello

understanding the IF forumlas to calculate rows with whole numbers in them for the last columns

CBOOK1.xlsxBOOK1.xlsx

View 5 Replies
View Related
Apr 22, 2014

I want to create a formula that values if the number in the cell is in the biggest 25 numbers in the column and also check in another column the same thing.If the number is within the 25 biggest from the first column or the second then to give me the number of the cell. I created a formula but it is very large in order to apply for the 1st to the 25th biggest number. The formula is:

IF(AND(or(K16=LARGE($K$16:$K$152,1),I16=LARGE($I$16:$I$152,1)),K16<>0),K16,"") but only for the 1st biggest number of the columns.

View 2 Replies
View Related
Nov 27, 2008

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 7 Replies
View Related
Jul 28, 2013

I have a workbook that has 3 columns (G, H I) with numbers from 1 to 7 in each column. I want to tell excel that if a row has a particular 3 numbers in these cells then change it to a specified number. I want to do this for the entire workbook. Is this possible?

View 3 Replies
View Related
Aug 19, 2014

a macro finding the max number in col B in each day (hr 0- hr 23) and in col C put a "1" next to it.

View 10 Replies
View Related
Jan 4, 2006

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

$17,213.82

$4,563.02

$85,693.42

$1,166.01

$725.90

$580.09

$2,243.75

$240.16

$207.70

$725.90

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

View 14 Replies
View Related
May 12, 2008

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 14 Replies
View Related
Feb 24, 2007

(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 9 Replies
View Related
Apr 17, 2008

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.

Endif

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

7330985920714

7330985920721

7330985920738

7330985920776

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 9 Replies
View Related
Nov 8, 2006

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:

=VLOOKUP(D2,SHIPMENTS,2,False)

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 5 Replies
View Related
Feb 14, 2007

Example:

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 9 Replies
View Related
Mar 2, 2013

I am trying to find some number which i have from a list of numbers available in various rows, for eg

11

7

11

13

17

i am tryng to find 11,13,14,15,16.... 38 from columns given i.e. 7,11,13,17....

View 3 Replies
View Related
Mar 16, 2014

I have two spreadsheets. Spreadsheet 1 has 6 columns and column E has a list of numbers. It runs from E2:E739.

Spreadsheet 2 has 9 columns and column A has the numbers I need matched against. Column A runs from A9:A5027. How can I find the numbers from Spreadsheet 1, Column E that are also in Spreadsheet 2, Column A. Once matched I would like to highlight the ROW on spreadsheet 1 that has a match.

View 3 Replies
View Related
Aug 7, 2009

I currently have: ...

View 8 Replies
View Related