How To Determine Which Numbers In A List Equal Given Sum

Oct 7, 2005

I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total?

View 12 Replies


ADVERTISEMENT

Using Excel To Determine Combination Of Numbers That Equal Set Total?

Jan 30, 2009

Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.

View 4 Replies View Related

Grouping List Of Numbers Into 5 Equal Groups

Nov 2, 2011

I have a list of 600 figures (this may vary) but i need a formula that will look a one particular figure in this list and return if it is one of 5 equal groups. e.g in this example the lowest 125 figures in numeric order would be in group 1 up to the highest 125 figures would be in group 5.

I realise if I limit it to 4 groups then i could use the 'quartile' functions but i am stuck with 5 groups.

View 3 Replies View Related

Excel 2007 :: VBA Code Determine Where To Paste Based Upon 2 Cells In Different Workbooks Being Equal

Feb 24, 2014

Trying to manipulate a macro I recorded to be slightly more dynamic with some VBA code. The macro has saved me a lot of time however each month I have to re-do it due to the date changing and I'm sure there must be an easy way to adapt the below code.

Current Code (it repeats afterwards as there are lots and lots of sheets I need to perform the action on).

Windows("Raw Data.xls").Activate
ActiveWindow.DisplayWorkbookTabs = True
Sheets("Calls").Select
Range("C43").Select
Selection.Copy
Windows("Volume Tracking.xlsm").Activate
Sheets("Offline").Select
Range("AE33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

What I'm after is a way to alter the range highlighted based upon matching a cell in "Raw Data.xls' and a cell in "Volume Tracking.xlsm".

The cell A43 in "Raw Data.xls" contains a month (which changes every month); if the month is Jan then I would want the Range to remain as AE33. If it's Feb then I would want the Range to be AF33 (and so on).

In "Volume Tracking.xlsm" I have the months in cells AE4 (Jan), AE5 (Feb) etc.

Is there a way to locate the column in "Volume Tracking.xlsm" on row 4 that contains the value found in A43 in "Raw Data.xls" and then paste what was copied out of "Raw Data.xls" C43 and paste it in to row 33 of the correct column that holds the matching month?

If not could I add code to say if cell A43 in "Raw Data.xls" = Jan then paste to AE33, if Feb then paste to AF33 and so on.........

Excel 2007

View 9 Replies View Related

Randomize With No Equal Numbers

Apr 30, 2007

I'm trying to get a random sequence of numbers e.g. 1-20 so that no number is the same i.e. no number appears twice.

I used Int(20 * Rnd + 1) + 1 but this gives me same numbers. Any ideas how i can do that?

View 9 Replies View Related

Determine After Filter Row Numbers

Apr 3, 2014

Is there a way to automatically determine the final row number showing after a manual filter operation? Or maybe getting a list of the row numbers showing on the screen.

The project requires a macro that loops through only the rows of a filtered list, not all of the rows in the worksheet.

View 3 Replies View Related

Formula To Highlight If 3 Numbers Are Not Equal

Apr 14, 2014

I have three workers for which I am counting the data from using a countas formula. The cells are A1,B1,C1 and I want to highlight in another formula to the right if these three values do not equal each other, I have tried and if formula referencing each of the combinations of the compares however it does not work in the desired fashion.

View 3 Replies View Related

Find A Combination Of Numbers That Equal A Given Sum

Dec 18, 2007

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

Find All Numbers That Equal To 9 In The Range

Oct 15, 2009

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

Calculate Average Where All Numbers Equal X

Dec 7, 2007

I have done is created an Officer Evaluation Form in Word for my Police Chief and the Scores for the different observations are: N/A, 1, 2, 3, 4 and 5. If for example there are 4 observations and one of the observations is "N/A" for not applicable or not observed and the rest are all 5's I want the formula to ignore the field(s) with the N/A and still come up with an average of 5. The way I have it set up now which is: =AVERAGE(KOW1,KOW2,KOW3,KOW4) it comes up with an average of 3 when I put a N/A in field KOW1 and all 5's in KOW2-KOW4.

View 3 Replies View Related

Determine If Value In List Exists In Another List

Dec 8, 2007

I have 2 sets of data in my excel spreadsheet.

Column A has a bunch of numbers and some duplicates
Column B has the same numbers but lots more and no duplicates
Column C describes what column B number does

All I want is for my formula to return the value in C when A#=b#:b#. The if statement, that I can't figure out how to get it to work right is this. =IF(A2=B2:B1021,C2:C1021)

This is fine if the 1st value is true (meaning a2=b2), the problem is the IF statement returns the 1st value in the array(I think) which in this case is false. An example of the array is below, the result of my formula is always false unless i change B2 to equal A2.

00E200002-Way Mir
00E200012-Way Mir
00E200022-Way Mir
00E200032-Way Mir
00E000E22-Way Mir

View 2 Replies View Related

Determine What My Multiplier Should Be Based On A Range Of Numbers

Nov 10, 2007

this formula and have tried various nested ifs and I can come up with a formula that works. What I am trying to do it to is detirmine what my multiplier should be based on a range of numbers. Let me try and explain lets say A1 is a number and B1 is a number and I need the Formula for C1.

If B1 is between 0 and 5 multiply A1 by 2 if B1 is between 5 and 10 multiply A1 by 4. and so on and so on. Is there an easy formula for this? I know it might be trivial but im stumped for some reason.

View 9 Replies View Related

Determine Average Of Set Of Numbers Based On Scale Of 1 Through 10

Oct 25, 2011

I'm trying to determine the average of a set of numbers, based on a scale of 1 through 10. There are two columns and it would look something like this:

1 3,452
2 2,990
3 564
4
etc.

I want to determine the STDEV here as well but assume I can just exchange the AVG function with that function, once I know how to tie column B (the numbers) to column A (the rank).

View 4 Replies View Related

Determine Amount Of Loaded Numbers In Array

Mar 21, 2012

I have an array with an ubound of 10,000 because I will be loading a different amount of numbers each time I run the macro. Is there a way to tell the amount of digits filled inside the array if it is less than 10,000?

View 4 Replies View Related

Sort Column With Equal Numbers And Linked Text

Jun 8, 2014

I would like to create a formula that will sort the numbers in column B, there are also equal numbers in this column

The difficult part is that there is text in column A, and is linked to the numbers

So if numbers are sorted the names should be sorted in the same way as well.

View 3 Replies View Related

Dividing A List Into Equal Parts?

May 23, 2006

Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups.

Have attached an example.

View 6 Replies View Related

Dividing A List Into Equal Parts.

May 23, 2006

Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups. Have attached an example.

View 4 Replies View Related

How To Determine If Column Has Dropdown List

Aug 29, 2012

I am looping through columns in a sheet. When I reach a column is there any way of determining if the column has dropdown list of values? I was expecting something like "HasDropDown" property somewhere, but it does not appear so. Some of my columns have dropdown values others don't and I need to determine which ones have.

View 2 Replies View Related

Determine If Cell Has Dropdown List

Apr 5, 2013

Is there a way using a macro to determine which cells have a dropdown list and then retrieve the name?

View 4 Replies View Related

Determine If A Country On List Has Been Entered In A Cell?

Apr 18, 2013

I have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.

View 3 Replies View Related

Dividing List Into Equal Groups Determined By Two Values?

Mar 10, 2014

I need to distribute a list of data into 10 even groups of data. Here is the scenario.

I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria

- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible

View 6 Replies View Related

Conditional Formatting - Highlight Cell With Value If Equal Value In List

Oct 15, 2012

I have 3 rows with numbers across several columns, each cell holding one number only. For example:

R1: 1101 - 1102 - 1103 - 1104 - etc.
R2: 2101 - 2102 - 2103 - 2104 - etc.
R3: 3101 - 3102 - 3103 - 3104 - etc.

A little further down I am having a list where the end user will write down the exact same number. Once the same number has been written into the list, I would like the number shown in the top rows (row 1 to 3) to be highlighted in green, basically telling the user that the number has been entered into the list below and is ok now.

How can I manage this via conditional formatting?

View 3 Replies View Related

Search For All Unique Numbers Down A Column And Print List Of Those Numbers In Another

Jun 5, 2014

Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.

View 5 Replies View Related

Relating List Of Random Numbers To A List Of Names To Create Rota

Nov 21, 2013

I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).

View 3 Replies View Related

Excel 2007 :: Create List Of Barcodes From List Of Numbers On Worksheet?

Feb 25, 2014

Is it posible and how to create a list of barcodes from a list of numbers on the worksheet?

Strokescribe seems to have some ind of solution but the data can't come from the worksheet.

View 8 Replies View Related

Excel 2013 :: Can Sort List A-Z Then Numbers At Bottom Of List

Jun 5, 2014

Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.

Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001

View 2 Replies View Related

Adding List Of Numbers But Only Numbers With $ Infront

Aug 18, 2014

[URL] .... If you take a look at the picture, what I'm trying to do is add the list of Prices up so that I can differentiate the amount received in $ and in £.

I need to add up all the numbers that don't have a $ in front into the 1 cell, and all the ones with the $ in front into another cell.

FEdLkuj.jpg

View 2 Replies View Related

Script For Generate 6 Numbers Of These Numbers From The List?

Mar 15, 2014

Is there any script that I generate 6 numbers of these numbers from the list? so they do not recur after another example 7,12,13,19,26,33

View 2 Replies View Related

Formula Needed To Make List Of Same Numbers From A List

Mar 21, 2014

A
B
c
d

[Code]....

So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3

View 3 Replies View Related

Manage A List- List Full With Numbers In A Column

Nov 29, 2007

1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.

Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)

PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.

2- I have another list with 2 columns which arranged in order. I mean:
A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.

My request is I want to make this list 1 column as arranged.
A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.

3- Macro will add a new line to the end of the above list with this format:
Z00001xxxx

Z00001 is static
xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits.
Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)

View 9 Replies View Related







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