Fill Handle Pattern (not Finding My Pattern)
Jan 31, 2007
Pre-requisite: I would consider myself to be very poor with excel, based on what I've read on this forum and found on my web-searches. I have a worksheet that has a list of data on the left going vertically, then a summary of this data going horizontally across the top. It is not arranged in such a way that transposing the data will do what I want. I am pulling the 5th word out of the title of each block of the vertical data and need to show this word on the horizontal section.
When I use this formula to pull the 5th word: =MID(MID(MID(SUBSTITUTE(A2," ","^",4),1,256), FIND("^",SUBSTITUTE(A2," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A2," ","^",4),1,256),FIND("^",SUBSTITUTE(A2," ","^",4)),256))-2)
I need to increase A2 to A30, then A58 (up by 28 every time) in every instance in that formula. The fill handle increases the values by 1, instead of 28 (even if I do 3 or 4 instances manually) How do I do this? I've run into this problem in other scenarios, and there HAS to be a way to get around it.
View 5 Replies
ADVERTISEMENT
Mar 2, 2014
b2 = a2 * 150
c2 = a2 * 145
d2 = a2 * 140
e2 = a2 * 135
[Code].....
in the first row i want to use the fill handle at 135 and drag rightwards so as to copy/retain the pattern (decrementing by 5, from multiplier 150 until 100)
how would I achieve that?
p.s I also want the formula for columns to work with the fill handle drag downwards.
View 1 Replies
View Related
Aug 1, 2014
I am comparing two years' data, one on top of the other (Rows 1 and 2). For each cell in each row, I am referencing a cell from a different tab. This needs to be done with about 290 data items, so 580 rows.
When I attempt to copy the two rows down, it doesn't go in the correct order (skips a cell reference number). It looks like this:
Excel Forum Q.xlsx
It would save me a lot of time considering each of the 580 rows has about 30 different column with each cell referencing various things.
View 4 Replies
View Related
Aug 29, 2007
how to fill right or down cells when the pattern is correlative....but how can I fill the cells to the right when the pattern is not correlative ??? I need to have a differnt pattern when I fill right...as in the picture. This is, I need it move 4 columns on each cell.
View 4 Replies
View Related
Mar 9, 2014
Attached is a sample worksheet. I have data in column A that I want to drag fill in column C. In column C, once the first 2 results in column A are entered, I would like a blank cell and so on.
testdv.xlsx
View 8 Replies
View Related
Jun 21, 2008
I have a file where comments are used for some cells. The user does not insert any text into these comments fields, but uses the comments feature to display pictures.
A picture is used for the fill pattern of the cell (inserted through fill effects) and that picture is what is displayed when someone moves the mouse over the cell.
The "fill pattern picture" becomes part of the excel file and my question is how can I extract this picture and use it somewhere else.
View 9 Replies
View Related
Apr 30, 2013
I'm attempting to do work on pattern detection and actions based on how said patterns occur in a column. Here is what I would like excel to do for me:
Code:
Pattern W1
Example A1=A , A2=A , A3=B
Pattern L1 Consecutive "A" 3 or more
Example A4=A , A5=A , A6=A
Triggers on specific events:
While L1 tracking variable = 0 -- If L1 is found, mark a variable to -2 then set the corresponding cell in the column over to the value of the variable (-2)
While L1 tracking variable < 0 -- If consecutive L1s are found one after another, then (on each consecutive occurence) multiply the variable by 3, then set the corresponding cell in the column over to the value of the variable
If W1 is found while L1's variable is < 0, then multiply the value of the L1 tracking variable by -1 and add 1, then set the corresponding cell in the column over to the value of the L1 variable, then set L1's tracking variable to 0
If W1 is found while L1's variable is = 0, then set the corresponding cell in the column over to the value of 1
All data is in one column.
View 1 Replies
View Related
Mar 15, 2012
I am trying to enter this formula in cell B2 and i just wanted to know if there was a better and quicker way to do this. I need to do this all the way to BP however my list can extend however the pattern will stay the same.
Ws.range("B2").value=
F & LROW * D1 +
K & LROW * D6 +
P & LROW * D11 +
U & LROW * D16 +
Z & LROW * D21 +
AE & LROW * D26 +
[code]....
View 1 Replies
View Related
May 19, 2008
I need to have a string comparison done in a macro,
I have a files with names similar to "TEVT_GURUPRASAD_WEEK08" and a array of strings having names "Guruprasad,AnilKumar,....etc." I Need to match the name in the array and the name in the filename.
View 9 Replies
View Related
Apr 7, 2009
The spreadsheet I'm working on is kinda hard to describe, but I'll try.
It's a finance calculator of sorts that I need done, and it shows me the total cost of various products. Each of these products require different components, and numbers of components, and also these components all cost different prices. So what I have is the following spreadsheet setup:
View 9 Replies
View Related
Sep 9, 2013
I'm trying to extract specific character from a set of text strings which have irregular patterns. For example:
CJWHCon.D005 - JN Holding Inc Corporate
CUSViWind.D404 - Windows Ringtown Vinyl
My goal is to get only the 'DXXX' which sometimes might be only 'DXX'. I've tried using the following formula but I seem to be going wrong somewhere as I only get 'D'.
=MID(F10,FIND(".",F10)+1,FIND(" ",F10,FIND(" ",F10)+1)-1-FIND(" ",F10))
View 2 Replies
View Related
Jul 15, 2014
I'm trying to repeat a pattern which outputs 4 equal numbers and then increments that number by 1 for another four row as follows
1
1
1
1
2
2
2
2
and so on
how to do this?
View 9 Replies
View Related
Feb 14, 2008
Need a macro that will autofill in an email pattern from a customer list i recieve. So far i have been having to manually do it.. but when have documents over 3k entries.. ouch.
This is what i got.. hope the way i type it in makes sense
A ..........................B ..........................C
First name........Last Name ............Email Pattern
John ..................Smith ...............First name initial, last name@abc.com
John ..................Smith ...............First name initial, dot, last name@abc.com
John ..................Smith ...............First name, dot, last name@abc.com
John ..................Smith ...............First name, initial of last name@abc.com
Is there a macro i can make that will automatically fill in the email based on the criterea found in column C before the @ symbol? then take the required info from Column a and b?
View 9 Replies
View Related
Oct 2, 2008
I am a beginner to VBA and Macros, and I have a fairly complicated macro that I am pressed to make.
I am working with alphanumeric data that is unorganized. Here is an example of what it looks like: ...
View 7 Replies
View Related
Dec 21, 2008
I am trying to come up with a formula that will distribute the numbers as shown in this diagram:
To get the value of each number divide 42,000 by the last number in the pattern. For example the fifth column from the left would be 42,000/18. That means that each number would have a value of 2,333.33. By the time you reach the half way mark, nine in that example, you should have around 21,000, and the other 21,000 should be distributed between the last half (block 10 thru 18). The formulas would have to work on all of the patterns. Can anyone think of a way to match the pattern shown? It doesn't have to be exactly the same pattern just as long as the number of blocks remains the same for each half, and they total 21,000 for each half without going over 42,000 all together. There has to be 11 rows from top to bottom.
View 9 Replies
View Related
Jul 28, 2006
Is it possible to change the borders/pattern of a single cell so that it looks like the image in the attached document? This may seem a strange thing to want to do, but there is a good reason! Merged cells will not suit my purpose. The pattern palette allows a cell to be filled with horizontal lines, but I need there to be just two lines (i.e. dividing a single cell into three boxes).
View 2 Replies
View Related
Apr 12, 2007
I made a code that automatically checks the date and then compares it to the date of the last save. If it is a new date then the colors of the tabs will randomize. It can also be done manually via a button. The problem is that I have now realized that the colors are always have the same pattern. Every day the first color is an orange color, no matter how many times I randomized yesterday...and so on. Is there a way to make the randomness not have a repeating pattern?
Sub MMVII_4_9_b()
Dim NewDate
If Sheets("Scrappaper"). Range("F2") = Date Then
NewDate = 0
Else
NewDate = 1
End If
'Determines whether it is a new day or not
Sheets("Scrappaper").Range("F2") = Date
'Updates the date
If NewDate = 0 Then
'Does nothing if the date is the same
Else
View 7 Replies
View Related
Dec 18, 2007
I'm trying to write 'user-defined' function in Excel, that doing following:
Getting two arguments: cell and 'like' pattern (_ _ , *, e.t.c).
As a result returns data that match this pattern.
For example:
1. Cell = "golf car is really comfort", pattern = "_ _ _ _" will return "golf"
2. Cell = "toyota is number one", pattern = " num*" will return "number one"
View 6 Replies
View Related
Jun 9, 2009
Create a workpattern using something similiar to the Networkdays function. I want to be able to calculate how many working days are between 2 dates if the employee works a non standard Monday to Friday Week. For Example if someone works ever Mon, Tues, Wed how many working days are there for them in June.
View 11 Replies
View Related
Apr 5, 2013
I need to perform data mining from a CSV file,and use correlation function to analyse and generate a pattern from the provide data (CSV) for a particular set of error for a given time sample.
Find the CSV file which contains the data which needs to be analysed and generate a pattern.
eventLog_20121205_140322_00000000002119445219.csv
View 4 Replies
View Related
Jul 14, 2014
I have a formula such as below..
=SUMIF('MBX TO DBS'!$G$1:$G$818,"*livedb01*")
This will sum the values in G1-G818 that contain livedb01 on the "MBX TO DBS" sheet, however this is not exactly what I want.. I want the SUM function to look for the livedb01 string in G1-G818 and SUM the values in D1-D818.
So instead it would look for livedb01 text in G column and take the value from D and SUM those once the text qualifier was found in G column..
Is this possible with Excel?
View 2 Replies
View Related
Jun 10, 2014
I want the first 60 rows of column C to be constant meaning C1, C2, C3, C4..C59 and after 60 rows it should start again with C1, C2, C3.....C59 rather than C60, C61, C62. In other words i+1 but after 60 rows i should be reset to 1 and then again increase by 1. how can i implement these changes
[Code] ......
View 2 Replies
View Related
Apr 10, 2013
I have the following series of formulas consecutively in a single column:
=IF(A3=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A4=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
.
.
.
=IF(A50=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A51=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
=IF(A52=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
.
.
.
Effectively, I am comparing the values in each set of 47 to determine which one is the smallest. The smallest entry will get a YES label next to it and all others will get a NO. The problem I am having is the the autofill feature is not correctly detecting the pattern, even if I manually fill in the first 49 entries (shown above). If I hilight all of the above entries and autofill one down, I get the following:
=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H53:H100),H53:H100,0),1)),"YES","NO")
It should be:
=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
Basically I need the H:H: reference to stay constant, but only for 47 cells at a time. After that, both the lower and upper bound should increase by 47. Is there any way to indicate this to Excel? Manually typing in the new formula every 47 entries isn't practical because the spreadsheet has thousands of entries. How I can accomplish this using a single formula and autofill?
View 2 Replies
View Related
Apr 12, 2013
I created a vba procedure for a particular job but within that procedure I need to perform another task. I've been trying to come up with a regular expression but to no avail. I'm looking to loop through a range of cells for any string where there isn't a space before and after a hyphen. When occurance is matched, then replace with spaces. One exception to the rule would be if data is contained within parentheses, then ignore. There could be zero occurances in string to many.
ie.
abc - def ==> ok, s/b skipped
abc - def (nnn-mmmm ooo) ==> ok, s/b skipped
abc-def (nnn-mmmm ooo) ==> abc - def (nnn-mmmm ooo)
abc- def (nnn-mmmm ooo) ==> abc - def (nnn-mmmm ooo)
abc -def (nnn-mmmm ooo) ==> abc - def (nnn-mmmm ooo)
abc-def ==> abc - def
abc- def ==> abc - def
abc -def ==> abc - def
View 9 Replies
View Related
Dec 12, 2013
I am new in extracting data from excel .
There is a report that contains a various amount data, in one cell it describes the outcome in a summary format of how an issue was resolved.
Is it possible to search a cell of summary text that contains a set pattern with letters & numbers i.e."CL900962" then either place a "YES" or "NO" in another cell if found?
The pattern will always begin with letters "CL" followed by 6 digit number.
View 9 Replies
View Related
Oct 25, 2007
I need a formula which can sum up some value based on some especial pattern which I indicate in the table below:
ABC
12565
21001542
33850
4
5
My question: I want to sum up value from A1, B1, C1, A2, B2, C2. I also need a formula to control how much I want to add. For example, I can instruct the formula to sum A1, B1, C1 and A2 (25+65+0+100) or sum A1, B1, C1, A2, B2, and C2.
View 9 Replies
View Related
Nov 22, 2009
I have Userform1, Textbox1, Textbox2 and CommandButton1. Textbox1 is being manually inputted by users.
I would like that when I click Commandbutton1, that Textbox2 will be populated by whatever value was entered in Textbox1, except that the any string that matches any of the listed pattern below:
009???
017???
088???
078???
003???
663???
will be excluded. "?" here represent any 1 character.
So for example, if the user inputted "AA-90-088100ABC" in Textbox1, when CommandButton1 is clicked, Textbox2 should have the value:
"AA-90-ABC"
View 9 Replies
View Related
Apr 19, 2007
I need to write a pattern for a postcode. I have the below (very basic I know and loses lots of combinations but i'll deal with that later) but no matter what I enter in the box I get mt error message saying the input in incorrect....
View 9 Replies
View Related
Apr 26, 2007
I have a variable that can contain in no predefined order, a number of letters and one two digit number (code). I would like to extract this two digit number from this variable and based on its the value perform a task. I can test for the specific value of this number as there are only about 10 of these 2 digit codes. Im quite happy to have the routine test for the number by comparing them all one by one as there are only 10 it wount take too long.
The best scenario would be to extract the value into another variable which I then could use to construct a "select case" table from which I could then initiate the required task based on that number.
View 9 Replies
View Related
Apr 28, 2007
Using the Find Function in VBA with pattern matching, I want to find the End statements in my code. How do I stop it finding the End If statements, of which there are hundreds maybe thousands. I tried End[ ][ ][ ] and End[!I] etc but they exclude the End statements as well.
View 3 Replies
View Related
