A simple question:
I have a column of numbers, for example:
2
5
5
8
8
8
20
50
I would like to know the value that corresponds to (for ex.) 5th position from, of course, the beginning of the column, ie 8.
In the following code, you will find a sorting function with a range is hard coded. Instead of hard coded value which is marked with red colour, i need to find that range value dynamically. How to write a code to find that ?
View 2 Replies View Relatedtrying to find the value of column A (date field) if it exists on the row that the cursor is presently on. I'm trying to check for the value before performing the next function to make sure the user is on a valid row. Cursor could be on any column A to F. Columns are always 6 but the rows can vary.
View 7 Replies View RelatedWhat I have is a list of numbers, many of which are duplicated, in no particular order and I really want to avoid sorting. What I want to find is the lowest position in the list of a particular value. Unfortunaly MATCH only seems to return the first occurance of the value. Not really looking for a VBA solution.
View 8 Replies View RelatedI have in the first column something like this:
GLOBAL DESCRIPTION
GLOBAL DESCRIPTION1 Particular description1
GLOBAL DESCRIPTION1 Particular description2
GLOBAL DESCRIPTION2
GLOBAL DESCRIPTION2 Particular description1
GLOBAL DESCRIPTION2 Particular description2
I want to to have in the second column:
GLOBAL DESCRIPTION1
Particular description1
Particular description2
GLOBAL DESCRIPTION2
Particular description1
Particular description2
So far I have this: +IF(EXACT(A1;UPPERCASE(A1));A1;+RIGHT(A1;LEN(A1)-(FIND(X;A1;1)-1)))
X standing for the position of the first lowercase, which I don't know how to get.
Is it possible to find the location of different characters in a string (using VBA)? Normally I would use InStr of InStrRev option in VBA but my situation is a little more complicated.
The character I am looking for is not always the same, sometimes it is a , or a . or a : or a "blank" etc
I tried to do it like:
dim strChar as string
strChar = "[,.? /]"
SearchPosition = InStr ("cell location", strChar) (searchposition is the name of the function I am trying to make)
This works if I define only 1 character, this way it gives me a 0 as outcome
I have tried to change it to strChar = "[,]" or strChar = "[.]" or strChar = "[ ]" but this doesn't work.
I'm messing around with two shapes, a Cannon and Cannon Ball. With code the Cannon rotates for the angle of fire. What I can't do is position the Cannon Ball at the end of the Cannon when Cannon is at an angle. Long time since I was in school so forget most of my maths.
Code:
Sub IamUseless()
Rem this is work in progress and in NOT WORKING!!!!!!!
Dim xDistance As Double ' is inital x position of object
[Code].....
I have a user form where two command buttons are set with the following code:
Code:
Private Sub CommandButton1_Click()
Frame1.Visible = True
Frame2.Visible = False
[Code]....
Now, when cmdbtn1 is clicked, frame1 is visible and vice-versa.
However, the position of both the frames on the user form are not on the same position on the user form. Indeed, it takes the position of the design view.
I want a code which will place the frame1 and frame2 at the same place and position on user form, irrespective of its position in the design view.
I am trying to work out how to get nth position of a character in a string. For example in this
Code:
kjishdfiuayigdscka
if I use the formula
Code:
=FIND("i",I$1),
it will give me answer 3. But I want the position of 3rd "i" in the string. The answer should be 12. How can I change this formula to get the correct result?
*ABCDEFGHIJKLMn8OPQRSTUVWXYZAAABACADAE1n1n2n3n4n5n6n1n2n3n4n5n6n7n8n9n10n11n12n13n14n15n16n17n18n19n20n21n22n23n24Position 24567141517192223272834353639404143464748493;223268103048456714151617192022232830343637383941434446485;16;20:24421432364148456791014151718202328303438394041444647485;9;2159173537384645679101416182023273032343839404144474849620253542454645679101416182327283234353738394043444647497131423414748567810141617202528303435363839414244454647
I have selected numbers to play in the table shown above from cell G2:AD7 and celebrated draw result numbers are shown in cells A3:F7
Now, suppose I want to play the numbers, which are in cell G2:AD2 and the draw-celebrated numbers are shown in the cell A3:F3.
Now I want to check cells A3:F3 with G2:AD2 and want to know in which position are the celebrated draw matched with the numbers played.
In this case the result in AE2 will be 3; 22 that means only two numbers are matched with the played numbers. Those numbers are the number ‘6’ which is in the position 3 counting from G2 and number ‘48’ which is in the position 22 counting from G2 again.
The result must be shown in column AE for all the draws as shown in the table above.
I'm looking for a way to split a cell with text into two cells. The first cell can only contain up to 40 characters, the rest needs to go in the second cell. However, I don't want the text to be split in the middle of a word. So basically, if the text is in cell A1, I need to find the closest space to the left of position 40 in A1, and move anything to the right of this position to B1. Example:
Original text:
A1: One green apple and a bucket of small onions that smell nice
I want to avoid this:
A1: One green apple and a bucket of small on
B1: ions that smell nice
I want to achieve this:
A1: One green apple and a bucket of small
B1: onions that smell nice
I'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.
I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.
I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.
The index column is used in some conditional formatting rules, so I would like to keep it anyway.
Find latest delay Position of each number which is in the game
View 9 Replies View RelatedI have this code below that deletes any single occurrence of a customer number but the "Customer Numbers" must be in column A.
I would like to change the code below to instead look for the "Customer Number" column by name, rather than by column A. My "Customer Number" column will always be in row 1, but yet, it will be in a different column letter every time I run the report.
How to find position of lust number in column (non macro preferably).
2
3
1
3
2
1
3
2
1
For number 1 match will return 3 but I need 9 (and I can't sort column opposite)
I have 6 column charts that are supposed to be identical. There are 12 pair of columns per chart. Some chart's pair are centered over the tick and some pair are to the right of the tick and I can't figure out how to make them all the same.
View 1 Replies View RelatedI have e.g. 3 colums, A, B and C.
In column A there is A number on every row.
In column B there is a number on ever row.
In column C I want to compile a new list such that if a number in column B is X (eg 1) the number from column A at the corresponding cell should be copied to column C. I want there to be no empyt cells in column C. So for the list below, the number in column A should be copied to C if its corresponding B cell is 0.
64
0
64
64
0
64
57
0
57
[Code] .......
I am trying to hide a column titled "Test". This column is usually column E, but it is possible it can be moved to column D or F. If I use:
[Code] .........
Then column E is hidden, regardless of whether Test is moved left or right. The reason Test moves is due to the user inserting or deleting a column.
I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document.
Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.
I will be working with an array of data approx 500 rows by 50 columns (eg Sheet1!$A$1:$CV$500) in which I will need to check if approx 1000 unique values exist and in which column they appear in. Each unique value should not appear in more than 1 column. Once identified, I need to return the header value at the top of the relevant column.
My thoughts on approach would be perform a lookup of some sort to identify the column number and then use that within an index formula to return the header value based on the column number.
However, I'm not sure how to perform a lookup to get the column number. A match formula can, as far as I am aware, only lookup against a single column or single row.
Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies View RelatedI have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.
I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.
Multiple commodities and multiple engineers should be separated by definite structure.
find worksheet with input and output (manual) as expected.
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
Is there an easy to use macro or VBA function to delete the rows with "position" column value of a zero? I have both positive and negative values in the column. I tried to put on filter but am not sure how to make it a rule based function: i.e. show only < 0 <.
Date Type NamePosition
2006/09/20CRGJacob11167
2006/09/20CRGTom 0
2006/09/20CRGMark 58371
2006/09/20CRGSue0
2006/09/20CRGDawn0
I am using the Column() Function for referencing purposes... what I want this function to do is return the "letter form" of the column position rather than the "number Form"... is there anyway to change this without switching to R1C1 form?
i.e. Column(B4) yields "2"
I want it to give me "B"
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
I am trying to find the median of a set of numbers on a column that correspond to dates on the left column. I want a monthly median average of the numbers on the right which correspond to the dates on the left. So for example. I want to make an equation that gives me the median of all the numbers on the right if they fall within the range of a certain month(in this case October). I've tried These:
=IF(COUNTIFS(A:A,">=10/1/12",A:A,"