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 415233645 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
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 BuildIn 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 =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 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" 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 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 12123456 no color 1 4 0 6 13234545 color 2 3 2 2 14123456 no color 0 2 4 2 14123456 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 SnapOn, 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 SnapOn, part # 852, mfg Proto, part # 369. If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'SnapOn' 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 SnapOn is an acceptable supplier, I could get a list of all potential SnapOn items grouped together. I'm sorry this is so long. I may have overworded 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 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 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. 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 Replies!
View Related
Find Missing Numbers
I have a file with approx 3600 rows. In Col B there is a 13 digit EAN13 code. I would like to find out which numbers are missing. I have to compare each cell from top to bottom in the Bcolumn 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 812 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 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): $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 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 "nonzero" 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
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: =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 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 e.g. Value Added Processing Business Viability Environment 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 GA001 GA003 Business Viability GA005 GA032 GA065 Environment GA023 Climate Change GA030 GA029 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 drawcelebrated 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 125. 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 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: .............A.......................B................................C.................. I......Invoice No
..
Starting Serial No
...
Ending Serial No 2
ND12
..6011000000013652
. ..6011000000103652 3
ND13
..6011000000116526
....6011000000246977 4
RF4
.
.6006000001255724
..
6006000011249564 5.........RF4...........6005000000023652.........6005000000095624 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 Nos 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 Nos 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
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 14 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 switchboard.com 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. EG.  NameA 324 234 65 NameB 345 67 Footer  etc. 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
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. 242.91 265.71 95.96 113.26 228.16 48.59 64.62 70.59 88.88 146.51 228.99 67.99 40.22 71.51 85.28 654.15
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) or 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: NBC//1 ABC//1 ABC//2 ABC//3 ABC//4 CBS//1 CBS//2 I have a column on another worksheet2: NBC ABC CBS 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
