Every month we have to reconcile our checks and then enter them into our application. We have an option in our application that we can do Check Number ranges. We are trying to find an away to have excel to put the checks in ranges either by a formula, a micro or a pivot tables. We have over 700 checks each month to reconcile.
View 9 RepliesI have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies View Relatedhow to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
I have a worksheet that needs customers split into different price bands
here we go
I have 13 customers all from the same group (a)
1 customer in group (b)
1 Customer in group (c)
& 80 independant customers (d) worksheet only has 9 (1,2,3,4,5,6,7,8,9)
Column a is a list
D17 is the drop down list
D21 is the starting price
C2:D5 is the pricing multiple to be applied
What I am trying to achieve is when an independant company is chosen it defaults to d for pricing.
I am creating an income calculation sheet to qualify my borrowers. I have different "types" of income in regards to hourly pay, bonus pay and overtime pay (examples)
I am using check boxes for my worksheet for the income that my underwriters want to use (example OT plus regular pay or Bonus plus regular pay or just regular pay alone can all be different options) they would then just check the box for the combo they want to use. (my check boxes came from the developer tab and I have them formatted to show true/false for checked or unchecked.)
Intro to my problem: We have 3 separate time frames for each type of pay. (I have 2012 Bonus, 2013 Bonus and then 2014 year to date bonus.....and the same for Overtime pay and regular pay, etc.)
My underwriters can only use ONE of the 3 yearly options.
Excel problem: Im thinking I need to go conditional formatting for this, but I need something to pop up if they accidentally have 2 boxes checked in one category. (So if they accidentally mark a box to use 2012 OT AND 2014 OT year to date, thats a problem and will throw the #'s off) So Im thinking there is some way to conditionally format my true/false results from my check boxes. If 2 out of 3 say true, the cell should black out or something along those lines....
I attached a screen shot of how my worksheet looks as of now : Income example excel forum.docx
What I want is to find out whether a value in a node can be found from a group of other nodes. I've tried lupike this, but it doesn't seem to work properly.
=IF(ISNUMBER(SEARCH(C1;$A$2:$A$200));"WAS FOUND"; "NOT FOUND")
I guess one solution could have been using concatenate function, but why on earth can't I use the function like this: concatenate(A1:A5)? Why do I have to put each term individually?
I have a 90 optionbuttons on a sheet in groups of three. I need to check to see if one of the option button value in each group is true.
View 9 Replies View RelatedI starting to develop a macro for grouping specific information together. part of it I need to group data based on date ranges. My plan was to, in one sheet label the start and end ranges by month (not every range starts and stops at the beginning and end of the month)
For July I would have 7/1/2012 in one cell and in another 7/28/2012
For august I would have 7/29/2012 next to it 8/25/2012, etc
I need to take those ranges and in my FY2013 sheet column I where all of my dates are listed, in the next column J if the date says 7/1/2012 to label it July, if date says 7/30/2012 to label it August, etc.
Cells in row C have numbers (number of days between date a and date b)
I want to group the days so I can run a usable pivot table as follows:
Up to 7 days between update date and today (ie 1 week) 8-14 days between update date and today (ie 2 weeks) 15-21 days (ie 3 weeks) 4 weeks +
My attempt is as follows but only give two results and not 4?
=IF(OR(C2<=7),"1-7days",IF(OR(C2>=8,C2<=14),"8-14days",IF(OR(C2<=21,C2>=15),"15-21days","Over4weeks")))
What is the simplest way to do it in VBA?
View 3 Replies View RelatedHow a single-cell formula to check that 2 transpose arrays are equal.
For example, A1:A5 are {1,2,3,4,5}
AND
B3:B8 are {1,2,3,4,5}
Is there an array formula in C3 for example, that will check (i.e. say TRUE) if corresponding ranges are true i.e. check in this cell that A1=B3, A2=B4,...A5=B8.
How to generate random 50 number with 10 group..
View 6 Replies View RelatedI have different sets of number from six digits to a max of 15 digits
eg.
012345
001005869
Z000000A
K1234567
CF0000009
K880000000
M000000123
Y001234505
AL56231
5649852A
What I want is to get the proper category for this number depending upon there digit for eg.
number starting with Z and of 8 digits belongs to group A
number staring with Y ending with 5 and 10 digits belong to group B
number ending with A and which does not starts with any alphabet has 8 digits belongs to group C.
similary ...
A user need to just key in the number, press tab (or any other button) and group for that number
must be displayed (depending upon the compostion of that numbers).
What I can think of is to first seprate each digits from the given number, verify it and then filter them through the groups to get the final group.
I'm looking for another excel game changer (for the work I do anyway).
I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?
Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal $257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.
What I am trying to do is to be able to paste 2 telephone numbers into one cell and subtract the last 4 digits of each from the other. for instance entering 4063219225-4063219297 into one cell subtracting the last 4 digits of each number to come up with 72 (9297-9225).
View 9 Replies View RelatedI would like to move a number from a group, automatically and post in a different colume. Like remove the 4 from 439 to a different colume.
I am looking to count group numbers that are bound to a nomenclature number. So I have made an example below. The nomenclature number is bound to 1. So I want to count all the 1's that are bound to 0912, which would be 5. and the same goes for 1234. the 2 is bound to the nomenclature 1234, so i want to count all the 2's that are bound to 1234. Which would be 3.
Group
nome
1
0912
1
1
2
2
2
1234
1
1
I have a worksheet that has 2 columns on it 1 is numbers and the others are
names. I want to be able to export a group of names from one of my programs
and then have excel match the name and dump the corresponding number into the
spreadsheet. I beleive I can use vlookup but I am not certain
Windows 7, Excel 2007. a formula to find the next lowest sequential number within a group.
Here's an example data set, which represents transactions by date and category:
Col A is a user entered Date which will not be in chronological order (unfortunately a sort is not an option)Col B is a user entered CategoryCol C is a formula that gives the chronological sort order based on the date.
The formula in C2:C9 is =COUNT($A$2:$A$9)-(RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1)+1Col D is a formula to determine if the row is the first chronological transaction within the Category
The array formula in D2:D9 is =IF(MIN(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"Y","")
This might not be a necessary column, but can be used as a helperCol E is where I would like a formula to give the next lowest value in Col C for the Category in Col B (the order number in Col C for the previous transaction within the same category).
I have entered in the values that the formula should return in Col E in the table below
A
B
C
D
E
[Code]...
If the current line is the first transaction of the category, the formula should return the value in Col C for that line (so E2 would equal the value in C2, which is 2).
So the first part of the formula could be straightforward, using the helper column:
=IF(D2="Y",C2,
Here's the tricky part: If it's not the first occurrence of the category, then it should return the value of Col C for previous transaction (or the next lowest number in Col C that is within the same category).
E3 would return 4, since the previous transaction for Category A was line 9
C3 = 6, and of all the values of Col C for Category A (2, 4, 6, 8), 4 is the next lowestE4 would return 6, since the previous transaction for Category A was line 3 C4 = 8, and of all the values of Col C for Category A (2, 4, 6, 8), 6 is the next lowestE5 would return 1, since it is the first transaction in Category BE6 would return 1, since the previous transaction for Category B was line 5 C6 = 5, and of all the values of Col C for Catebory B (1, 5, 7), 1 is the next lowestEtc.
I'm thinking I need an array formula to find the minimum value within the category, where that value is less than the value of Col C in the current line. Just not sure how to write that...
I have this set of number..
987654.
What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.
How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
298
2/9/2014 12:28:02 PM
37
77
[Code]....
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies View RelatedI have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies View RelatedMy store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
Need a formula that will count the number of occurrences of that same number in three different ranges. The number that is to be compared to is in a cell that is a result of a formula.
Example:
Lowest number found in three ranges: 30 (Answer is in A1)
Question: how many times does that number appear in the three ranges?
Syntax:
=Countif(Range1,range2,range3=A1)
Has to equal the cell reference value (A1), not a plain number (like 30)
I have a whole list of numbers (e.g. 7, 23, 567) and I would like to be able to tell which range they fall within and return the category.
MinMaxCategory
1275Group A
276375Group B
376600Group C
601825Group D
8261,075Group E
Therefore 7 and 23 would be Group A, and 567 would be Group C. I can do as a long nested IF, but I'm sure there must be a better way.
I have many columns of data, each containing numbers ranging from zero to 100. I am trying to determine the formula to count how many of these numbers fall into different defined ranges;
e.g. how many pieces of the data are numbers between 90-99, how many are numbers from 80-89, 70-79, 60-69, etc continuing on down.
I attached a sample workbook which may be clearer: there is a tab (titled "data") with 3 example columns of data & there is a tab (titled "counts") where I hope to get counts for each range to be generated by a formula rather than count them myself.
Not sure if the following is possible, but this would be even faster if each column didn't have to first be sorted ascending/descending for the formula to be effective (as I have hundreds of these columns).
=IF(OR(Q136<O132,Q136>O132),Q136-O132,O132-Q136)
This does not seem to be working for me. I need to check one number against another and if the one cell is larger then the other subtract it or just the reverse.
I have a list of numbers that I have to concatenate with another number depending on if it falls within the parameters and I don't know how to do a lookup on that. I currently am using a complex if/or/and statement which is too dificult to debug and extremely complex like this: if(OR(AND(a>=1,a<=10),AND(a>=20,a<=30)),concatenate(a,"123"),if(OR(AND(a>10,a<20),(a>30,a<40))),conc atenate(a,"456"),"a is not in the parameter")
is there a way to do a lookup for this?
Something else to throw into the mix, there are some ranges that shouldn't be there and return nothing so from 40-55, there shouldn't be a result.
also, there are some that are just one number (56 gets 789 but then at 57 it goes back to abc)