I have created a spreadsheet to find cyclical patterns and my next task was to sort these patterns from highest value to lowest.
Next I need to start from the first row of the sorted spreadsheet and find the first row working down that repeats. Once I find his row I have to stop the search and highlight both the original and repeated rows. If the first row has no repeat it has to move to the second row and do the same operation until it finds a row that does repeat and so on.
Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5.
For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1.
See the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.
I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.
I am creating several worksheet where the first two rows are repeated so that I can see those two rows as I proceed down the sheet. That feature works on some but not all sheets. What am I missing? I even tried copying the pages that are correct to another worksheet and that format doesn't copy to the next worksheet.
I download an extract from our company's website (.csv). After that, I need to do certain actions (such as deleting certain rows etc.).
The parts I am struggling with in VBA is: - I need to trim B:B and give back the values to the same cells it came from; - I need to add three columns behind the data: a date (same for all rows), a simple sum of two cells (A2-D2), and a vlookup.
I vaguely remember that I need to build a loop until the end of the rows (number of rows varies with every download, column headers are the same always), but I cannot remember how.
sorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.
I would like to know how do I skip a row if the data in that row is the same as previous rows?
Say I have a column of names, and John is in row 1 and 5, and I want to store this column of names in array, but I dont want to have two Johns inside it. How do I skip row 5 then?
I have a spreadsheet with one column. For example, it contains
A A A A B B C C C D D
I want to highlight the A's and don't highlight the B's and then highlight the C's and then don't highlight the D's and so on. I want to highlight or not highlight the entire row.
Range from AK1:AK8 to AT1:AT8. These are eight rows, total eighty entries. Can a formula or conditional formitting determine if there are duplicates values in eighty entries . The need is not column-based rather row based.
See attched spreadsheet. I would like excel to highlight the rows where a value (model) = *.* (ie: not a whole number). I think the attached spreadsheet explains it better.
I am using a 2007 excel spreadsheet and on parts of it I can't just highlight one horizontal row of cells, it wants to highlight three down. I suppose that I have asked it to do this but I can't for the life of me undo it.
I have tried unmerging cells, cutting and pasting somewhere else, but nothing works.
Can anyone help me please as it's driving me to distraction?
I have a program that compiles a whole bunch of annual data.
The data is in columns A:J
I would like to have a program that loops through column A's entries and if the entries are odd then have the cells in Columns A through J of that row be highlighted with colour index RGB (193, 205, 205).
I found this spreadsheet on here and I have been trying to customize it to what I need. I am trying to have scores from skins match highlighted. I want only the minimum score to be highlighted but if there is another duplicate minimum score I don't want it to highlight anything. I also need to find a way to count the skins won by each player and have it off to the side.
For those not familiar with golf a Skin is a game where you try to get the lowest amount of strokes on a specific hole. Ex- 4 people play the hole one. P1 scores 4, P2 gets a 3, P3 and P4 get 6. The skin would go to P2 who has the lowest score on that hole.
Highlight lowest number in each column not highlighting if there are duplicates starting at L6 down to L11 and for each column till AC. And the same for the group just to the right on attached file.
On row 13 and 14 it tells me who won a skin. I want to tally up the total skins won by each player. so if Joe's names shows up twice on R14 I want it to tell me somewhere in the sheet Joe = 2
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula... =INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
1. On one I have sheet named leave with five columns
2. Second sheet has calender
Sample file is attached for reference sample.xlsx
What I want is that as i enter leave dates on sheet "Leave" corresponding rows on calender sheet should highlight and important thing is if i change the name row highlight should change automatically. This i could achieve with following function
[Code] .....
The problem here is that it does it once but for second time it does not show...
I have the following code for identifying the 1,3,2 patterns from sheet1 and it copy pastes those rows into sheet2. I was wondering if there is a way to modify this code to delete those patterns once it has identified them instead of pasting them into sheet2. In other word,s sheet2 needs to contain every other data except those identified patterns.
VB:
Sub test() Dim myPtn As String, txt As String, m As Object, n As Long, LastR As Range Dim ws1 As Worksheet, ws2 As Worksheet, myStep As Long, i As Long, t As Long myStep = 10000 '<- change here if needed. Set ws1 = Sheets("sheet1") Set ws2 = Sheets("sheet2")
A person on this board did this for me with this so far and I need to be able to view more than 16 rows. I would like to view all the rows it finds. I have 62,000 rows on information this far and will exceed 500,000 so you can see my need to see all involved to save hours of sifting through data. I would also like a place up top near the box (column G.H)(3,4,5) another box for me to enter a new number combination to search. Presently I have to go through the data to find the first one which is also time consuming. I would like to be able to put say for example in the area I am mentioning:
I got the first 3 blocks working but got stuck with the 4th one.
It should count only those hours between saturday morning 0000 and sunday night 2400 if it concerns a weekend day. and actually these hours should not be calculated in the first 3 time blocks.
Hope the title isn't too broad. Every time I think I have got to grips with RegExp I get stuck on something apparently quite simple. So, I have a few queries so I hope OK to bundle them together.
1. How do I remove the word 'of' only if it occurs after certain other words (the other words should also go). E.g.
"Vale of White Horse" should stay the same "Borough of Lambeth" should become "Lambeth" (because 'of' after 'Borough')
I tried "Borough(sof*)" but that doesn't remove either word.
2. How do I remove everything after a '/' (inclusive)? I have tried "/sw+" which removes the first word after '/' but there could be other words
3. How do I remove everything inside brackets (inclusive)?
I am trying to do an "if" statment to look in every 23rd cell to see if it is greater than 0. if it is it populates what is in that cell. That is fine...
The problem is i want to create the "if" statement just a couple of time manually, than drag it down 300 rows and have it look every 23 cells. It does not recognize the pattern???
I have an xy chart for which I would like to format the lines to be different patterns beyond those available in the format series dialog. I recognise that this will mean using VBA and I am happy to do that. I would like to be able to use patterns in the same way as they are used in a column chart.
I recorded a macro to see which properties were changed when the patterns are selected in the dialog for the xy chart and it is clear that it is only the LineStyle of the border and that there is no interior.
With Selection.Border .ColorIndex = 57 .Weight = xlMedium .LineStyle = xlGray25 End With
This would explain why when I use debug.print ser.Interior.Pattern I get -4105 or if I change ser.Fill.Forecolor etc it has no effect.
Is there any way to use user defined patterns to colour the lines in the same way as in a histogram/coloumn chart?
I'm trying to create an auto fill pattern to match B4-M4 then repeat with B5-M5 and continue to 350.
So for example, I'd like to manually enter "B4,C4,D4,E4,F4,G4,H4,I4,J4,K4,L4,M4,B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5" into column A and drag the auto fill box down until I've repeated the pattern to match all the way to M350.
Is this possible? I tried just entering the pattern as I've posted and dragging the auto fill down, but it doesn't fill the numbers properly and ends up with a strange pattern.
I am using Excel 2007 and I need to search for data patterns that are spread across many columns. I am not sure how to search so that criteria is met across all the columns concurrently.
For example
I am interested in data that matches the following criteria ;
Column B has the value 55 Column C has the value 70 Column D has the value 80
Rows of interest are when all the criteria in columns B,C,D are met at the same time (e.g 55 in B, 70 in C and 80 in D).
Split from How To Set Up A Spreadsheet The Right Way
Taking onboard what you suggested I have "rethought" the layout and it is now in a single table format. What had stopped me doing this previously was the fact that I was not able to show 7 days a week for 48 weeks in columns. However thanks to the transpose function all the data is now more user friendly (See Attached Workbook)
You mention the data should be quite easy to manipulate from here, any ideas as I am struggling with the concept of how to display the data in a time series. I assume that it will involve some form of lookup function referenced to the table of times, but thats as far as i can get.
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
