VBA To Translate Number Range To Category
Apr 9, 2009
I have 3 columns of data, column C has a number representing a length of time in months. I need to add a number to the end of each row (Column D), depending on the number in column C. Example:
If column C has a number between 0 and 3, the number to go in D would be 1. If column C has a number between 4 and 6, the number to go in D would be 2. Here are the list of possiblities (only 5):
0-3 = 1
4-6 = 2
7-9 = 3
10-12 = 4
12+ = 5
This will form part of a macro, so a VBA solution would be ideal. I thought of having a named range and looking up each row with a list of all possiblities. But i see that as rather crude. I'm hoping someone has a more logical solution they could share.
View 2 Replies
ADVERTISEMENT
Feb 3, 2014
How to translate a specific number into a specific letter related to the number itself.
I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.
So if the letter
B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course
B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course
P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course
P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course
and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.
The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.
and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course
and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.
View 9 Replies
View Related
Dec 9, 2009
I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).
I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.
For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?
It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.
View 9 Replies
View Related
Sep 18, 2013
Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
View 2 Replies
View Related
Oct 6, 2009
I'm trying to make my formula work. It is working now but I need to add one more criteria and I can' figure it out. I need to get a number of entries that fall into this categories:
John Smith09/02/2009Closed
=sumproduct(--(A1="John Smith"),--(B2>=Today()-90),--(C3="Closed"))
Now I need to get a count of entry either if there is a date >= Today()-90 or if it says "Closed" in C1. But I only need one of the two and I can' figure it out.
View 9 Replies
View Related
Jun 18, 2008
I am using a vlookup and have a problem. I am assigning a category to an item number based on the first two characters of the item number. For example item number 60123 would equal scrap because of the first two characters of 60. But the item number can begin with either a number or letter. Here is the formula I am using that works for item numbers that begin with numbers:
=VLOOKUP(VALUE(LEFT(E2,2)),Sheet3!A:B,2,FALSE)
It works fine until I reach a item number that begins with a letter, then I get the dreaded #Value error. If I take the value out of the formula then it works for the letter based number items but not for the number based item numbers.
View 9 Replies
View Related
Dec 13, 2013
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
View 2 Replies
View Related
Feb 12, 2014
Index of the VBProj.VBComponents("ThisWorkbook") component?
I need to refer to that component but not by its name because the file I use, goes to some users that have MS Excel installed with another language and their Excel translate the component's name in that language so
VBProj.VBComponents("ThisWorkbook") should be VBProj.VBComponents("[that-language-translation]")
View 3 Replies
View Related
Aug 11, 2008
I have been sent a spreadsheet containing the following Macro, which is fair enough, only thing is, I don't understand the Macro, its too high for my extremely basic knowledge of VBA.
Private Sub CommandButton1_Click()
Dim rng As Range
Dim i As Integer
Dim sht1 As New Worksheet
Dim sht2 As New Worksheet
Set sht1 = Sheets(ActiveSheet.Range("D2").Value)
Set sht2 = Sheets(ActiveSheet.Range("D3").Value)
Set rng = sht1.Range("First_Line")
i = 0
Do Until sht1.Cells(rng.Row + i, 1) = ""
sht2.Range("Managers_Name") = sht1.Cells(rng.Row + i, 1)
sht2.PrintOut
i = i + 1
Loop
End Sub
translate this for me, step by step, as to what this Code is doing?
View 2 Replies
View Related
Feb 29, 2012
I have a spreadsheet with thousands of rows of text that I need converted from one language to several other languages.
Is there a macro I can use that taps into some free service, running down my page and giving a foreign language conversion for each cell?
[URL]
View 14 Replies
View Related
Dec 14, 2006
How would I go about putting the following formula into VBA code? The .008 would be going in field O8. =IF(AND(M8>5,M8<10,N8>5,N8<10), 0.008)
View 5 Replies
View Related
Feb 9, 2007
It looks like the above has been inserted into my current workbook (by mistake). How do I delete it all (lots of the options have the remove option disabled)
View 3 Replies
View Related
May 8, 2007
I am trying to find a way to make a certain number equal a certain name. For example 1111 will equal Name1 in another cell and 1112 will equal Name2....
View 5 Replies
View Related
Apr 23, 2014
I have excel workwook with Month sheet Jan,Feb,Mar....Dec and YTD
In each monthly sheet i will have either "yes"or "No" in cell B66:K75.
Below is what i need to do in YTD sheet cell B66:K75. Each cell in TYD B66:K75 will have 12 digit number
Need to go thru each monthly sheet for each of the above cell and find if Yes or No and translate that into number (yes = 1, no = 0)
Example:
If B66 in Jan, Feb and Mar is Yes and B66 in Apr thru Dec is No then the number in YTD in cell B66=111000000000
View 5 Replies
View Related
Nov 23, 2009
I am trying to create a formula that can translate true calendar dates when a date is entered IE 2/15/09 if with the normal formaula, it alwways calulates 30 days or even on 31 day months.
I need th formula to calculate actual dates and know when there is a 31 versus a 30 day month or 28/29 for February.
View 9 Replies
View Related
Mar 20, 2008
I have several hundred points in cartesian x,y,z coordinates. I used an Excel add-in program to calculate the equation of best fit to the points. The equation is:
z=A*x+B*y+C
where (for this case):
A= 2.29091
B= -1.77338
C= -192.494
My objective is to define the several hundred x,y,z points in cartesian coordinates about a new cartesian coordinate system whose origin is the centroid of the data points and whose z plane (ie z=0) is equal to the best fit plane defined above (when referenced from the original coordinate system).
Using Pro/e (a cad program), I was able to create the new coordinate system using the x, y, and z intercepts from the best fit plane, and then was able to redefine the points about the new coordinate system. After the points were redefined in Pro/e, I was able to export the points back to Excel; however, I would prefer to only use Excel / VBA because I prefer the calculations be transparent for optimal accuracy and validation.
View 9 Replies
View Related
Mar 25, 2013
Lets suppose that in a drop down box I have the words : "I" "want" "to" "create" "a" " sentence".
Well what I would like to do is have each word as selected in the drop down box, placed in another cell creating a sentence.
If you picked the words in order the cell should read: I want to create a sentence or it could read any combination of these words depending in the order they were picked. So it could read: I sentence want to create a sentence if the words were selected in this order.
View 14 Replies
View Related
Mar 17, 2013
How do I translate this to a vba code?
=INDEX('Workers List'!D3:D13,MATCH('Workers List'!C4,'Workers List'!C3:C13,0),1)
what i have done is
test = Application.Index(Sheets("Workers List").Range("C3;C13"), Application.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)
But i keep getting an error, may i know whats wrong with it?
View 5 Replies
View Related
Dec 21, 2008
I have some data for international dialed numbers, I need to translate this into a country or destination name.
for example if the dialled number begins with 001, the is the united states etc..
I have all of the required codes, but they all differ in length.
ie 0033 is spain but 003356 is a spain mobile.
I was thinking that i could use VLOOKUP to find the relevant text to display, but this does not work with the varying lengths of the codes.
View 9 Replies
View Related
Apr 19, 2014
In Excel 2013, how do I translate all characters of selected cells to Unicode?
There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
View 1 Replies
View Related
Apr 19, 2014
In Excel 2013, how do I translate all characters of selected cells to Unicode? There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
View 10 Replies
View Related
Jan 22, 2013
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
View 7 Replies
View Related
May 8, 2013
I need a sum of top 5 numbers category wise. I have attached a sheet in which I would be requiring Sum of Top 5 for the month of May. I have shaded cell where i would be requiring a formula...
Also I may want to include other category as well.. For e.g. sum of top 5 for the year 2011 as well... I have tried every thing i could from Sumif to sumproduct to Large...
View 9 Replies
View Related
Nov 16, 2008
The title isn't very good I know; I don't know the right terminology to explain the problem properly, but i'll give it a go: In column A I have a list of names, all of them appear multiple times. In column B there is a corresponding percentage. I would like to create a formula that takes the average of all the percentages that have the same corresponding name, or 'category'. I don't want to just go through and do it manually since there are roughly 32,000 different values.
I did have one idea; in column C I pasted in a list of all the possible names in column A and then in column D I used countif to find out how many times each name recurred. If both columns A and C are in alphabetical order, I thought I could use the normal average function, by doing, for example AVERAGE(B1:BD1), but this didn't work. Is there some special way in which you can use a cell name (i.e. D1) instead of a number in a cell range? I tried brackets, speech marks etc. but nothing worked. I have searched around a bit on this forum to see if my question had already been answered, and did some extensive googling, but I couldn't find a solution.
View 3 Replies
View Related
Aug 30, 2007
I have 4 columns:
id (A), number (B), category (C) and month (D)
I think this could be done using VBA or formula's,
I understand how to SUM up based on criteria, but I
haven't a clue where to start to see if something was
present/ not present in the previous month and category and vice versa
If I decide a month to look at in E1, say February.. then i need
to calculate 4 things:
1) was an id in February and not in January (for this category) if so then sum up (B) per category (C) for February
2) was an id in January and not in February (for this category) if so then sum up (B) per category (C) for January
3)was an id in February and not in January (not in any cateogry in jan) if so then sum up (B) per category (C) for February
4)was an id in January and not in February (not in any category in feb) if so then sum up (B) per category (C) for January
I need to do this for each category (C)
My data would be something like this (I cant download addon from this PC, i have it at home)
id____number____category____month
1_____10_________A___________january
2_____20_________A___________january
3_____30_________B___________january
4_____40_________B___________january
1_____50_________A___________february
2_____60_________A___________february
3_____70_________A___________february
5_____80_________A___________february
so if I chose to put February in E1,
I would need the results something like follows:
category__new to category__left category__n__left totally
A___________(70+80)__________none____________(80)________none
B___________none____________(30+40)__________none________(40)
View 9 Replies
View Related
Jun 15, 2007
Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.
Have attached a sample xls..
View 5 Replies
View Related
Aug 25, 2006
Designated Cell = 7
1 10 .034
15 25 .072
35 45 .089
Output Cell = ???
I am trying to find a formula for the output cell. Suppose the designated cell = 7 due to formulas above it. So that number is always given but can change. I then need a formula for the ouput cell that says if the designated cell is greater than or equal to 1, and less than 10, then the output cell should equal .034. So suppose the designated cell = 18.2. Then I would want the output cell to be .072 since it is greater than or equal to 15, and less than 25. The template I am using has 23 rows of numbers like above. I didn't know how to do it for 23 rows though. If/Then doesn't work because it is 23 statements long (well over the seven limit) and the vlookup only works when the cell is equal to a number and not a range.
View 9 Replies
View Related
May 5, 2014
I have a list of patients with different categories. Count values of HBA1c which are less than 6.5 and those greater than 6.5 ONLY and only for those of the Category named "PreGDM" but not for other categories.
View 14 Replies
View Related
Jul 25, 2014
I have created a simple account list with - date - detail - category - amount.
How can I automatically list each transaction in date order by category on other pages. Each category is represented by a letter - e.g. 'T' for travel. In this example I want to list all travel transactions in a separate list in date order on another page for easy printing.
I have made a Summary page for each category but cannot work out how to make a detailed list for each category.
View 13 Replies
View Related
Dec 23, 2008
I need a formula to count list items if the date is between a specified parameter. I am trying to count how many help tickets were logged for a particular group or bureau during a specific week.
How many AA's between 12/1/2008 and 12/8/2008?
Bureau Date Opened
AA12/1/2008
AA12/1/2008
AC12/2/2008
AA12/6/2008
AB12/12/2008
AE12/15/2008
AC12/16/2008
AD12/17/2008.........................
I know how to count a list of items when you identify what you want it to count (I11 (Access)) and where (Application (Named Range)) but I can't figure out how to include the date parameter.
I J
Access23 =COUNTIF(Application,I11)
Acrobat2
ADMIN4
View 4 Replies
View Related
