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.

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.

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.

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.

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.

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.

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?

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.

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

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

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

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?

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?

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.

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?

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

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.

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

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.

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.

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.

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.

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.

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?

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.

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.

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

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.

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.

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.