How To Delete Patterns From Data

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


ADVERTISEMENT

How To Match Data Patterns Across Multiple Columns

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

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 View Related

Weekend Shift Patterns

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

Regular Expressions Patterns

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

Formulas Recognizing The Patterns

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

Filled Patterns For Xy Chart Lines

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

Creating Patterns For Auto Fill To Follow?

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

Cyclical Patterns - Highlighting Repeating Rows

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

How To Create Patterns When Copying / Pasting Formulas

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

Show & Chart Shift Work Patterns

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

Excel 2003 :: Changing Colour Palette To Include Patterns?

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

Finding Two Patterns In Column And Doing Calculations Depending On Pattern Order Found

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

Updating Values In Variable Ranges Based On Repeating Patterns In A Column

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

Color Patterns: Convert Cell Color

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

Copy All Data From Data Sheet, Sequence Top Down By Spend, Manually Delete All After 50

Sep 18, 2009

I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet.

In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low.

The workaround:

Copy all data from data sheet, sequence top down by spend, manually delete all after 50.

My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above.

Is there a way to do this without VBA i.e. pivot tables etc

View 10 Replies View Related

Rows Similar Data Divided By Blank Row To Delete Data If There Are 6 Or Less

Apr 2, 2014

I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.

View 2 Replies View Related

Delete Adjacent (hidden) Data When Deleting Visible Data

Dec 10, 2009

I have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.

View 14 Replies View Related

Visually Inspect The Data And Then Delete Any Column Containing Irregular Data

Jun 10, 2009

I have 1000's of columns of data to go through, I just need to visually inspect the data and then delete any column containing irregular data. At the moment; I select a section of the first column of data and insert a column chart for that data. I then need to change the source of the chart data to that of the next column, to do this I click on the data within the chart and it will show the data on the worksheet that I have selected by surrounding it in a blue box, I then click on this box and drag it to the next column of data. Clicking on the blue box (which sometimes I miss, and I then have to go back to the chart to activate it again) and dragging it to the next column is getting annoying, especially as I have sooooo much data to go through. Does anyone know if there is a shortcut key for this, or how I could write a macro to do this?

View 9 Replies View Related

Update Data By Ado - Change/delete Data And Then Run Macro For Update Data In Source Spreadsheet

Dec 6, 2006

I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.

Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub

It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used

.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0

after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).

View 2 Replies View Related

How To Delete Certain Data

Sep 7, 2007

I have a workbook which starts with data in column B. The heading of each set of data starts with Turn No. What im trying to do is to be able to delete that row and the following 2 lines under it. It will then show LTP in column B. What I need doing with that is to be cut and pasted into column A.

This is how it looks at moment....

View 9 Replies View Related

Delete Duplicate Data ...

May 16, 2009

I'm a first timer here an I'm hving a problem with data. I have 3 sheets in a workbook, I want to transfer all the data to one sheet. I have 3 columns labelled Number, Name, & Sales, on each sheet some of the numbers and names are the same and I want to be able to match them up and put the sales from each sheet into a new column, so the final sheet will have 5 columns in total, if the numbers and names don"t match I just want to add those to the bottom of the matched ones.

View 10 Replies View Related

Delete The Whole Row In All Sets Of Data

Dec 13, 2009

In the attached workbook, I have identical sets of data in column A separated by an blank cells. I need a macro for user form that searches for an item indicated in the textbox of the form and then delete the whole row in all sets of data.

It is important to keep the blank cells between the sets constant, 4 blank cells between each set, except the first set that starts with 3 blank cells.

View 2 Replies View Related

Delete Colums Containing Data

Mar 15, 2012

with the following problem : i have a matrix and a list with words and i need to delete every column from the matrix which contains one of the words from my list. I have a VBA script which uses entirecolumn.delete but i need one which doesnt rely on this function. The reason i can't use entirecolumn.delete is that it messes up all of my predefined ranges. here is the script i am using :

Dim c As Range, d As Range
Worksheets("table1").Activate
For Each c In Range("matrix")
For Each d In Range("listofwords")
If c = d Then

[Code]...

View 9 Replies View Related

Delete Data From Particular Column

Mar 30, 2007

i wrote the below which deletes few data from a particular column, but then its not working when the data increases, i mean yesterday i had data upto 12000 columns and today i had more than this....still macro deleted the same as it did yesterday........is there way where its the last filled row and deletes the data present in the column P.

Sub Deleteddata()
'
' Deleteddata Macro
' Macro recorded 3/30/2007 by kaliappk
'
Selection.End(xlDown).Select
Range("O65536").Select
Selection.End(xlUp).Select
Range("P14842").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("P14841").Select
Selection.End(xlUp).Select
Columns("O:O").Select
Selection.Copy
Columns("P:P").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("O1").Select
End Sub

View 9 Replies View Related

Delete ALL The Data That Is Duplicated

May 11, 2007

I have data like this a column
0
1
1
2
2
3
4
5
5
5
6
7
8
9

I need to delete ALL the data that is duplicated such that the column would read
0
3
4
6
7
8
9

I am aware of the advanced filter feature that shows only unique data - but that still doesn't completely eliminate the duplicated data.

View 9 Replies View Related

Delete The Specific Data

Aug 29, 2007

I am looking for a macro or someway that I can delete data out of a workbook that dont contain specific data. On the example shown below, if in column E it dont say one of the following Nhampton, euston, tring, bletchley, Nhamp Emd, Nhamp NJ, Nhamptn RS, Watford Jn, Bltchly MD, Bedford, Bletch CS or M keynes I need the row totally deleted.

******** ******************** ************************************************************************>Microsoft Excel - Book1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA4=
ABCDEFGH4LTP**BY**751*******5*CNDR**CRE****6On4.472*Bletchley*04+595K087Off11.47**M*Keynes05+055.242K088Hrs07:00**Euston6.296.372N139****Nhampton7.53**10DaysSX**TRAIN*GOES*FORWARD*TO*NEWST.***11From09/07/2007422(350*Nhampton*8.241W5212To07/09/2007**Euston9.25**13***PNB(*30**MINS*)***14***ASSTEuston*10.242N5715****(To*Northampton)***16****Bletchley11.12**Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

How Do I Delete Row If Data In Column E

Nov 7, 2008

What im trying to do is to be able to delete the row and shift cells up if there is data in column E.
I.E lets say e45 has data in it I then need row 45 deleted and rows shifted up.

Also is there anyway I can create a macro that can do this but password protect it.

View 9 Replies View Related

Delete Range Of Data

May 20, 2009

Picture a simple spreadsheet (A1:C10). In column A, the word "delete" appears on every even numbered row (2, 4, 6, etc). Blank (no) data appears on the odd numbered rows (in Column A).

The following code was to sort A1:C10 by column A, bringing all the blanks to the top (now, rows 1 through 5) and "deletes" to the bottom (now rows 6 through 10). I then want to delete all the rows which contain the word "delete."

Selection.Find(What:="DELETE", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A185").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("B184").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

My problem is that each time the macro is run, the word delete will appear on a different row. Can anyone suggest how to fix the above code so that it simply finds the word "delete" (after the sort), then does the equivalent of [END][DOWN], [END RIGHT] (to highlight the desired ranged to be deleted, then executes the delete command.

View 9 Replies View Related

Delete Row If Data Not In List

Oct 20, 2006

I have a spread sheet that has a varying number of rows. I need to compare the contents of Column B for every row to a list (that could also potentally vary). If the contents are in the list then do nothing, if not then delete the row. example below.

I have attempted with a do while loop and if/elseif statements (would just change the macro is the list changes) but all rows ended up being deleted. And I would like to see if it is possible to just change the list in the spread sheet, as the users are not as computer savy.

Column B
abc
red
blue
def
ghi
red
green

List
red
blue
green


Needs to end with
Column B
red
blue
red
green

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved