I am trying to get a list of states, based on their expiration date.
I have all 51 states listed, some states have 2, 3 or more licenses, so I created a column that I hid of the state abbreviation, for the use of formulas.
If the license expires in 30 days or less I want to display the state abbreviation. I couldnt figure out how to do an array output, so this is what I am trying to come up with.
=IF(COUNTIF('Kevin Doyle'!F5:F250, "
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:
[Code].....
So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".
Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.
trying to output a 1x10 array of the function FwdRate. The inputs are all 1x10 horizontal ranges of numbers. So for example to calculate FwdRate 1 I need to take the first values from each of the input ranges and enter them into the formula below ! Where am I going wrong as all I get is the VALUE error when I press ctrl+shift+enter ?
Option Explicit
Function FwdRate(Tau As Range, f1 As Range, dtau As Range, vol1 As Range, vol2 As Range, vol3 As Range, m As Range) As Variant
Dim VArray() As Double
Dim i As Integer
Dim n As Integer
Dim dt As Double
Dim X1, X2, X3 As Double
[code].....
I have attached a worksheet which I need to show the last update and last date customer contacted, The formula :
=LOOKUP(2,1/($F2=Data!$D$1:$D$10000)/(Data!$B$1:$B$10000=MAX(IF(($F3=Data!$D$1:$D$10000)*(Data!$M$1:$M$10000=COUNTIF($F$2:$F3,$F3)),Data!$B$1:$B$10000))),Data!B$1:B$10000)
is used to output the last contact and the formula contained within column Av, Both of which contain #N/A
I have a long list of values, a few thousand lines, that I need to input into an array so I can run a formula on the values in the array and then take those results and put them into another array which I can then call to output down a different column. Is there a faster way to accomplish this than the typical For Loop with the activecell.value and activecell.offset commands, that just takes forever.
View 15 Replies View Relatedi am trying to create an Array that will print the stock number as a title on row A. e.g. "Stock 1" in A2 "Stock 2" in A3...etc. here is what i have attempted:
View 2 Replies View RelatedWhat I need is a script or formula with returns multiple results and puts them into one cell.
The Excel is used as a Project Managment Work Sheet for Resources, working on different Projects and their dedicated hours
The raw data table looks like this:
Column A: Project Name (=AllProjectsLists)
Column E: Resource Name (=MasterDataResources)
Column P: values from 0 to 8 (hours)
First I need to check per row the Resource Name. If the Resource Name is a match, than I need to check if in column P the value is bigger than 0. The result goes into a different sheet per row, one result per Resource, but the multiple results should be shown in one cell.
So the result should be something like:
Resource name "Thomas" .... Projects working on: "Project 1, Project 5, Project 13, ..." (in one cell)
Resource name "Mary" .... Projects working on: "Project 3, Project 9, Project 13, ..." (in one cell)
I tried with this one, but it only returns one vale per cell:
=SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1), ROW(1:1))
=INDEX(AllProjectsLists; SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1); ROW(1:1)))
I haven't used List Boxes much previously but decided to use one in this instance because I wanted the capability to make multiple selections. I used the following in a list box called "ListBox_Analysis_Code":
Private Sub ListBox_Analysis_Code_Change()
Dim iPtr As Integer
Dim sTemp As String
How do you write a code that will calculate a formula and output the result in a cell?
For example:
A1 = 1 (Named "Cell1")
A2 = 2 (Named "Cell2")
A3 = Results of macro for Cell1+Cell2 = 3
Is there a way to write it if I also want B3 = B1 + B2 and C3 = C1 + C2, etc.
In column A i am using the following formula to output "Our Apple" when "Apple" is in column D and "Our Banana" when "Banana" is in column E:
[Code] .....
I would like the formula to also output "Big Apple" immediately underneath "Our Apple" when its present, and output "Big Banana" immediately below "Our Banana" when it is present.
The yellow column is how column A should look after the formula edit.
Attached File : Book2.xlsx‎
I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.
B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0
I have attached the sheet also.
Sheet 1.xlsx
I'm using "Mangal" Hindi font for entering the data but I'm not getting Hindi numbers in the output by formula. The output is in English numbers which I want in Hindi numbers. The marks are in Hindi numbers but the output is in English numbers.
The remark in column "bz" should be in English.
How to get the output in Hindi numbers.
Assuming I’ve the following sample data and wishing if the output data could be formatted using some excel formulas, refer to below sample.
Sample data (Sheet1):
StudentA | Lesson1
StudentA | Lesson21
StudentA | Lesson10
StudentA | Lesson5
StudentB | Lesson5
StudentB | Lesson6
StudentC | Lesson9
StudentC | Lesson8
StudentC | Lesson9
StudentC | Lesson15
StudentC | Lesson11
StudentC | Lesson30
StudentC | Lesson13
StudentD | Lesson1
StudentD | Lesson2
StudentD | Lesson3
StudentD | Lesson4
StudentD | Lesson5
StudentD | Lesson6
StudentD | Lesson7
StudentD | Lesson8
Sample output (Sheet2):
StudentA | StudentB | StudentC | StudentD
Lesson1 | Lesson5 | Lesson9 | Lesson1
Lesson21 | Lesson6 | Lesson8 | Lesson2
Lesson10..................Lesson9 | Lesson3
Lesson5....................Lesson15 | Lesson4
.................................Lesson11 | Lesson5
.................................Lesson30 | Lesson6
.................................Lesson13 | Lesson7
..................................................Lesson8
The sample data could be long and a lot, so it's best if the formula can be made flexible to cater for this requirement.
Please refer to the attached file for some sample data.
I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.
I then try and write an if statement
e.g. =If(b1="Friday",1,0)
B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?
I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.
B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0
I have attached the sheet also.
Sheet 1.xlsx - Speedy Share - upload your files here
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
Need to run formula against the output of another formula, but excel is giving me an error because its seeing the formula not the output. Is there a way make excel run the formula against the output?
=IF(B1=1,AND(E1<=5,"WITHIN","NEEDS UPDATE"))
the formula on E1 is =DAYS360(C6,D6,TRUE)
I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.
View 3 Replies View RelatedIs there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?
View 3 Replies View RelatedI know that I an probably asking for too much but I must get the formula some way. OK, I need a formula for this:
lets say I have 3 columns (A,B,C (search criteria). In each Column there is a DIFFERENT number. There is 3 more columns (D,E,F (used to compare).Each column has 3 Different numbers. If 3 numbers match between the two sets of 3 numbers I want Column G to display "CCC". IF 2 numbers match I want G to display "CCH". If 1 number is a match I want G to display "HHC". If none of the numbers match I want G to display "HHH".
I can manually do these but it will take waaaay toooooooooo much time to do. If you can do this for me it would be a great help indeed because I'm trying to do other states. With this formula I can use Automate to do a large quantity of numbers.
is there a formula that would have as it's output the cell address of the
same cell where the formula has been entered.
e.g. In cell A10 I input a formula whose output is the cell reference A10.
I have a formula in column J that is the following:
=IF(I541="",IF(ISNUMBER(SEARCH(LEFT(G541,4)&"*"&RIGHT(G541,4),H541)),G541,""),"")
See screenshot:
I need to replace "G542" in the formula above. For example, in cell J541 shown above, I need it to output "1996-1999|2000-2001" instead of "1996-2001" I need the output to include LEFT(G541,4)&"*"&RIGHT(G541,4)
Trying to look for formula for searching through Range and search through Data to achieve expected res
Data Output ExpectedRange
Pune 123 is city Pune 123 Mumbai 999
Maharashtra 345 is state Maharashtra 345Pune 123
India 678 is Country India 678 Nagpur 666
Maharashtra 666 is state OthersPune 555
Below is the query i used for manual search however need formula to see through Range and achieve output expected
=IF(ISNUMBER(SEARCH("Pune 123",A5)),C5,
IF(ISNUMBER(SEARCH("Maharashtra 345",A6)),C6,
IF(ISNUMBER(SEARCH("India 678",A7)),C7,"Others")))
Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.
View 4 Replies View RelatedIs there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I have column A and column B:
I will be inputting data into column B.
I need excel to check to see if the data I input into column B is an exact match to the data in column A.
If it is an exact match, then column B will remain blank.
If the data in column B is different, I need column B to show the following:
No match: <data>
Example I input in column B the following:
Column A Column B
1. Car Car
2. 4357 9999
3. fsd34d 4erd
4. 98dkf 98dkf
Spreadsheet should show:
Column A Column B
1. Car
2. 4357 No match: 9999
3. fsd34d No match: 4erd
4. 98dkf
(Cell 1 and 4 in column B are empty because they are exact matches to Column A cell 1 and 4)
My questions:
1) How does the excel formula need to be written for this to work?
2) Is there a way to set it so that when I do a mass copy to data into column B that the formula will not be overwritten and it will still check to see if the data I copy and pasted into that column matches the data next to it in column A?
I have a long chain of formulas calculating the return on a particular investment in equipment. We'll call the inputs A, B, and C, and the output (total savings generated) Z. There are several set combinations of values for A, B, and C that I'm interested in generating a summary report for.
There are a number of steps in-between the inputs that I'm interested in, all of which are necessary to get to the output, but is there a good way to have excel chart inputs and outputs without filling out the intervening data?
I suppose the whole thing is essentially a multi-variable, multi-step data table.
I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.
As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.
So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.
I've got the following sumproduct formula (which seems to work):
=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))
B17 is a keyword to be found in the Product_Keywords range
I'd like to modify it so that:
ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1
Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?