# Greatest Common Divisor Of Two Numbers?

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

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

View 4 RepliesADVERTISEMENT

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

View 6 Replies View RelatedI 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.

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 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 have data in column C starting cell 2 that runs sometimes to 50, sometimes 80 or more rows. I would like column D to MOD the numbers in C, but in cell E2, I would like to enter the MOD divisor and have Excel use whatever number I put into E2 to MOD column D. What formulas would I use for this? I've attached an eg in case I am not clear.

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.

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

I want to ask if there is a way to find the next greatest maximum without a ton of auxiliary coding....Is there a function which does that in Excel? For example from the following list it would give 45:

1

34

45

26

78

29

7

I am trying to find the formula to find the greatest number/date etc in a column, I'm not sure which one I should be using?

For example I have a list of dates in Column B and I want in cell A1 to show the latest date in that column, so if the dates are

21/12/2008

05/02/2009

it would show 05/02/2009 in cell A1.

I can get the result. It shows #num! in cell F2

I have a set series of numbers as follows:

7550-0101

7536-0130

7538-0131

7535-0128

7536-0126

[Code] ....

I would like to disregard the digits before the hyphen (not always four digits) and check only the values of the last set of digits (also not always 4 digits). I would like to search for the greatest suffix used to find the next available number. In my data above the result would be 132.

Formula that could find the result and return the greatest text value?

I have over a 1000 records broken down by Leaders of which each is roughly 50 records. in those records, answers are either : 4 hours, 8 hours, 24 hours or 48 hours. I need to have a formula return of the 4 choices, which had the greatest value.

I have two columns of data, in another column I have the difference between these two columns, now I want to show the difference as a percentage. Assume cell A is 0.02 and cell B is 0.03 the difference is 0.01 and the percentage of the difference would be 33% or 0.01/0.03, my problem is when cell A is 0.04 and cell B is 0.01 the same formula wouldn’t work in both cases since in the first A<B and the second A>B is there a way that I can take the difference and divide it by the greater of A and B without having to do it manually?

View 6 Replies View RelatedIn the first picture the Pivot Table is showing the profit of each city in 2010 and 2012. Where I used Sort & Filter Largest to smallest, in order to display the cities with greatest profit.

Prosent.png

But when I switch the values to `% of` year (Previous). I get the change from 2010 to 2012 in percentage. But my PROBLEM is when I want to Sort & Filter from Largest to Smallest, cause I want to show the cities that have had the greatest percentage change from 2010 to 2012. But the I only get the same cities, and when I look at ex. 'Gjovik' is has had a 125 % in change and should be in the top.

So how am I able to Sort & Filter from Largest to Smallest the cities with greatest change in percentage?

I have wave data and need to extract out of a column the greatest rate of change i.e. start from a1 if the numbers are going down find the first number that starts going upward...and put in column b...then find the next first number that is going down and put in column b....and so on and so on all the way down the column.

View 4 Replies View RelatedI have managed to get a nice array formula to look up count all the Rs As & Gs in some cells and return whichever there are most of (the cells are linked to/fed from another workbook with cells that have either an R, A or G in - nothing else)

And mainly this is working but on one set of cells it returns nothing as there are 2 As and 2 Gs - what is really annoying is that on a set of cells on a another section of the tab there are 5 A's and 5 G's and that returns an A.

I have values like this in a column

1

1

2

2

2

3

3

3

3

In this column '3' has the maximun number of repetions of 4. I need to get '4' as the output of my code..

I'm trying to use vlookup function in table 2 referencing table 1 using the approximate parameter. However, an approximate vlookup value is by definition the closest *least* value. Is there a trigger of some sort to locate the closest *greatest* value as well?

For instance, if the lookup_value in table 2 is 2.450 but the referenced table 1 only has 2.445 and 2.475, I will only retrieve the respective value related to 2.445 -- how would I retrieve the other value at 2.475?

Essentially, if a lookup_value rests in between two values of whatever is being referenced, I am trying to average those two closest numbers. Is there a function I've overlooked that may help with this?

Although the "Trip Sequence" is same , but I need to separate the Trips if the difference between the "1ST TIME " is more than 3 hours keep the first "Trip Sequence" unchanged , then by either adding a prefix or add 2000000 to the second "Trip Sequence" and 3000000 to the Third, so forth so on until all the rules are applied ( assuming more than 3 Trip Sequences could be also allocated in the database ).

Data Base

TRIP#

1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence

XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002

XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003

XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA3270004

XXX05111835$VV1984GVACDG3270004XXX11451430$KK31133FCOATH3270005

XXX10471700$KK31132ATHCDG3270005

In the above examples , The "Trip Sequence" is same 3270004 but the "1ST TIME" is more than 3 hours difference:-

I want to be able ( ONLY IF "1ST TIME" is in access of 3 hours while the Trip Sequence are same , I want the results as follows:-

TRIP#

1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence

XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002

XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003

XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA5270004

XXX05111835$VV1984GVACDG6270004XXX11451430$KK31133FCOATH3270005

XXX10471700$KK31132ATHCDG3270005

I am looking at some combination of Index, Match and array functions in order to assign a value, in this case a currency, based upon an instance where another data item with the same trade ID already has that currency assigned to it.

Basically I have a list of data where there are a list of trade IDs for each data row/item.

In some cases there will be two rows of data that have a common trade ID.

For example there may be two results that share a trade ID eg 450222, one in EUR, one in another currency eg GBP.

What I need to do is create a formula in other column to the right of my data that recognises that if a trade ID is assigned to a non-Euro currency then to apply the foreign currency to both trades with the common ID.

ie change

Ref 450222 Ccy EUR

Ref 450222 Ccy GBP

Ref 450223 Ccy USD

Ref 450223 Ccy Eur

to

Ref 450222 Ccy GBP

Ref 450222 Ccy GBP

Ref 450223 Ccy USD

Ref 450223 Ccy USD

An offset function can't really be applied as the order of the data is random. My subsequent calculations involve a SUMIFS on GBP, USD etc.

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.

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.

I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.

I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.

I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.

I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.

I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.

See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?

doc list.xlsxâ€Ž

I have attached the excel sheet here for reference.

I would like to sum up the values across rows, so long as they have the terms "FQ1", "FQ2", "FQ3" or "FQ4" in the first row.

In other words, for the row "Total Revenue", I would like to sum up the quarterly values, using VBA, since the number of quarters increase with time.

How should I code up a VBA for that?

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.

I have Column A with file paths from the same main directory.

C:UsersSJohn.AJAXDesktopTimeBills.docx

C:UsersSJohn.AJAXDesktopTimeDChargers hick.docx

C:UsersSJohn.AJAXDesktopTimeDChargers raps.docx

C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstadium.docx

C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstats.docx

I would like to find the last common folder along the entire Column (Time),

remove all to the left, and insert E: in its place. Getting:

E:TimeBills.docx

E:TimeDChargers hick.docx

E:TimeDChargers raps.doc

E:TimeDChargersDodgersstadium.docx

E:TimeDChargersDodgersstats.docx

I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.

Copyrights 2005-15 www.BigResource.com, All rights reserved