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.

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

Convert Rows With Repeating Data To Separate Rows

Aug 5, 2009

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.

Repeating Rows

Jun 23, 2008

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.

Repeating Command Until End Of The Rows

Nov 23, 2009

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.

Index And Match With Repeating Values Without Repeating First Found Name

Apr 20, 2006

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.

Skip Repeating Rows Of Data

Nov 25, 2013

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?

Convert Repeating Rows Into Column Head

Mar 26, 2009

I have an excel spreadsheet containing data in two column layout :

States, Cities

UP, Agra
UP, Lucknow
UP, Kanpur
Punjab, Amritsar
Punjab, Ludhiana
Punjab, Jalandhar
Punjab, Patiala

How can I covert it in following layout i.e convert repeating rows into column headings ?

Punjab, UP

Ludhiana, Agra
Jalandhar, Lucknow
Patiala, Kanpur

Highlighting Rows ..?

May 24, 2007

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.

Transpose Rows Of Data With Repeating Reference Point?

Jun 27, 2014

I have a set of data, for example:

Code Manager Level
abc 123 ab1
def 456 cd2

What I need is to create output that looks like this:

A B C

abc Manager 123
abc Level ab1
def Manager 456
def Level cd2

So essentially for each code, I need two separate rows that say what heading and the value is.

Highlighting The Duplicates In Eight Rows:

Dec 15, 2008

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.

Highlighting Rows Where A Value Is Not A Whole Number

Sep 18, 2009

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.

Highlighting More Rows Than I Want To - How To Stop Please

Nov 28, 2009

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?

Conditional Highlighting Of Rows?

Jun 24, 2014

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).

This is what I have below, but it is not working.

[Code] .....

Highlighting Lowest Score Without Duplicates Highlighting And Counting Player Skins?

Feb 15, 2013

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

Index And Match Function Across Multiple Rows With Repeating Names?

Jun 4, 2014

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?

Auto Highlighting Rows Based On Data On Another Sheet?

Jan 13, 2014

I have two sheets

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...

Excel 2010 :: Highlighting Rows In VBA Based On Multiple Criteria And Sum Function

Apr 25, 2014

I am trying to write a macro in VBA excel 2010 that compares 2 sheets.

The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2

AND

on that same row if column 6 on sheet 1 = column 3 on sheet 2

highlight green

** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...

Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID

475-T
03/03/2014
Connect
\$562.95
\$19.95
\$543.00
6G000001450835

[Code] .......

Results >

Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID

475-T
03/03/2014
Connect
\$562.95
\$19.95
\$543.00
6G000001450835

[Code] ..........

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]...

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

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.

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)?

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???

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?

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.

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).

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...

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.

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.

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.