Identify And Number Patterns
Nov 3, 2013
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:
2 6
4
2 8
View 1 Replies
ADVERTISEMENT
Jan 28, 2009
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.
Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN
Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE?
---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)
I have attached a scaled down version of the Workbook.
Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
View 7 Replies
View Related
Apr 22, 2009
I want a function that identify the row number, where the date in column A is the same of the active row, but the value in Column B is equal to a different text e.g.=”zz”?
View 14 Replies
View Related
May 5, 2009
E20 with the series of numbers in tenth, and with five numbers on one cell. Between 01-37. How do I write a formula on column R to identify any repeated numbers from E20 with E21? In the chart it shows number 10 is repeated from E21, so I place a check mark on the same row as E20 on column R.
On column S how do I write a formula to identify numbers within E20 to see if there are any consecutive numbers? On E20 there is none, on E21 there is one 10-11, and column S will let me know if there is a consecutive by placing a check mark.
View 9 Replies
View Related
Jan 14, 2008
After looking at other similar post titles I could not find a formula to work exactly proper.
I have attatched a sample of my problem. What I need is for cells E4:J4 to look for a match in cells B4:B16 and give a value of " Yes or No " in cell K4 if a match is found. Then copy down formula through E5:J5 to B4:B16 - E9:J9 to B4:B16. As my examples indicate Cells K4 would = yes, K5 = yes, K6 = yes, K7 = no, K8 = no, K9 = yes. Column D is the results, Columns E-J represents all combinations of column D.
I have tried the following formulas but they did not work entirely correct.
= NOT( ISNA( MATCH($b$4:$b$16, e5:j9, 0 ) ) )
= COUNTIF(e9:j9,"="&b4:b16)>0
View 9 Replies
View Related
Jan 21, 2008
What I want to do: find the four cell that have the same value "99" in belows configurations
A B C D
1 99
2 99
3 99
4 99
A B C D
1 99
2 99
3 99
4 99
View 9 Replies
View Related
Feb 18, 2014
I have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
View 11 Replies
View Related
Jul 29, 2013
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")
[Code]...
View 2 Replies
View Related
Jan 19, 2010
I'm trying to divide the hours between 2 given times in blocks:
i.e.: monday 0600 - 1400 = 8 hrs
2400-0700 [mon - fri] = 1
0700-1800 [mon - fri] = 7
1800-2400 [mon - fri] = 0
0000-2400 [weekend]
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.
View 12 Replies
View Related
Sep 24, 2009
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)?
View 6 Replies
View Related
Aug 15, 2008
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???
View 11 Replies
View Related
Jun 6, 2007
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?
View 5 Replies
View Related
Aug 20, 2014
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.
View 5 Replies
View Related
Apr 14, 2009
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).
View 9 Replies
View Related
Oct 10, 2011
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.
View 8 Replies
View Related
Jan 24, 2012
I was wondering if there was a way to create patterns when copying and pasting formulas?
For example, here is what I want to do right now:
=AVERAGE(Sheet1!I2:I650)
=AVERAGE(Sheet1!I2:I649)
=AVERAGE(Sheet1!I2:I648)
=AVERAGE(Sheet1!I2:I647)
=AVERAGE(Sheet1!I2:I646)
=AVERAGE(Sheet1!I2:I645)
=AVERAGE(Sheet1!I2:I644)
=AVERAGE(Sheet1!I2:I643)
=AVERAGE(Sheet1!I2:I642)
=AVERAGE(Sheet1!I2:I641)
=AVERAGE(Sheet1!I2:I640)
In other words, have a pattern in the formula?
Or something like this, but filling out cells horizontally.
=A1/A100
=A1/A99
=A1/A98
=A1/A97
=A1/A96
=A1/A95
etc...
Or:
=A1*A1
=A1*A2
=A1*A4
=A1*A8
=A1*A16
=A1*A32
=A1*A64
etc...
View 1 Replies
View Related
Feb 27, 2009
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.
View 3 Replies
View Related
Dec 19, 2011
I'm trying to change the colour palette to include patterns (for charts). Is this possible? We use Excel 2003.
View 1 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
Jul 6, 2006
I have a list of two columns. Here’s an example. The left most column provides the row number.
_ A B
1 1 0
2 2 1
3 2 1
4 3 2
5 4 2.5
6 5 4
7 1 0
8 1 0
9 2 2
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.
Here’s what the updated list would look like:
1 1 0
2 2 1
3 2 1
4 3 1.5
5 4 2
6 5 3.5
7 1 0
8 1 0
9 2 1
View 9 Replies
View Related
Apr 26, 2006
I've reached a problem with colors in Excel: need to convert cell color (get from .Interior.ColorIndex property) into RGB value (to set ForeColor of the button). How can I make it?
View 8 Replies
View Related
Apr 8, 2014
I'm trying to Count duplicate text on sheet 2 and populate the number of times repeated on sheet 1.
On sheet 1 I have A3:A128 and would like the number of times these respective cost centers are repeated in Sheet 2 to populate Column B of Sheet 1.
File attached.
Not sure which formula to use. I tried CountIF but didn't work with 2 sheets.
View 7 Replies
View Related
Dec 3, 2013
I have a worksheet with a large amount of data in it. Every couple of months the data will change and I place this in a different sheet (sheet 2, this comes from a software tool) . Is there's some code that can run a check on sheet 1 compare all values against sheet 2 and highlight the rows that have a different value between sheet 1 and sheet 2? Perhaps paste the rows into sheet 3 with the different value highlighted??
Example of original and changed data attached : example_2.xlsx
View 4 Replies
View Related
Mar 4, 2014
(I cannot attach the files as it is all personal information) Basically its list that looks like this:
2014-02-11 John Doe
2014-02-12 John Doe
2014-02-17 John Doe
2014-02-10 Mary Jane
2014-02-11 Mary Jane
2014-02-16 Mary Jane
2014-02-10 Frank Simms
2014-02-11 Frank Simms
2014-02-18 Frank Simms
2014-02-11 Tony Sly
2014-02-12 Tony Sly
2014-02-17 Tony Sly
Im trying to identify anyone who does not have an entry on 2014-02-17. I've played around with combing IF and VLOOKUP and Conditional formatting but I couldn't seem to get it to work. If it were to find a date, no problem, but to find someone without that date I'm having issues. If there was a way to highlight or identify Mary Jane and Frank Simms since they don't have an entry on 2014-02-17, that would be great.
View 5 Replies
View Related
Nov 9, 2009
I have two worksheets one worksheet contains Insurnace names and Addresses from a hospital. I need to match these up to specific Insurance codes from a billing company worksheet. The bad thing is my billing database has multiple duplicate addresses for different insurances (Yes this does happen where different Insurances have the same address-don't ask me why). So originally I did a VLOOKUP where I looked up the address from the hospital Spreadsheet and matched it to the Insurance Specific code. The pitfall to doing it this way is that it only grabs the first exact match from the Billing worksheet. Is there a way to either automatically identify the possible matches and allow me to choose which one is the exact match or how do I just identify duplicates on my billing worksheet and match them manually? I attached the worksheet.
View 9 Replies
View Related
Oct 2, 2009
I'm trying to identify the last used row in Column A of my DestinationSheet so that I can add data to the row below it, but I'm receiving the following error: Run-time error '91': Object variable or With block variable not set And it's directing me to this line of
View 4 Replies
View Related
Jan 5, 2010
I need help with creating a simple formula.
What I have:
..A B C D E
1 3 4 6 5
2 6 2 3 8
I want to identify that A1, B1, C1, D1 are > B2. My formula will be placed in E1 so then I want a value of 1 to show up in cell E1 indicating the identification was successful. I can't figure out how to make this happen. My formulas so far in E1: =sum(A1>B2)&(B1>B2)&(C1>B2)&(D1>B2) This formula comes up with 0FALSEFALSE instead of the value of 1 I am desiring.
What I want:
..A B C D E
1 3 4 6 5 1
2 6 2 3 8
View 4 Replies
View Related
Mar 30, 2007
I am using ADO connections in my program to insert the datas in the excel. I want to find if the excel is locked by another user. How do i do this in ADO commands.
View 6 Replies
View Related
Dec 28, 2007
I have written a UDF to do some calculations i a workbook. I would like to run a sub, checking the cells in the spreadsheet to see if my formula is present. and if so, then add or change an argument in the formula. My problem is:
How do I write the code so that it will recognize my formula ? Is it possible to attach a tag to a formula which Excel can search for ? I cannot search for the formula including arguments from specific cells, relativ to the formula cell, as the user is free to input the arguments with reference from anywhere in the workbook.
View 3 Replies
View Related
Jan 24, 2008
I am looking for a way to search using macros. Currently i can search/find by cell, but i need to find with mulitple cells. An example is, sheet 1 "A2" and "B3" date needs to be compared to sheet 2 to see if the data is already in sheet 2. Column A is first name and Column B is last name. Need to know if that full name (first and last) is already in second sheet. I can already search for just the first name or just the last name but now the two together.
View 5 Replies
View Related
