Hello

Here is the problem. I have 10 products of which the prices may change each
month. I keep monthly records going back 12 months. It looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90 $100
B $90 $90 $90 $80 $80 $90
C $85 $85 $85 $70 $70 $80
D $60 $60 $60 $60 $60 $70
etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go down to
product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May, Apr,
Mar are all the same. However in Feb the prices are different. In other words
there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the table
and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare prices for
all products (A-H) with the prior month. If they are the same then the code
will remove(.clearcontents) the prior month and then move onto the next
month. If the prices are different then those prices will remain the in the
table and then those prices will be used to compare against prior months to
check for price changes.

In the end the table should contain only those columns where a price change
occured from the previous month.

In the example I give above the remaining columns would be Mar-05, Jan-05,
and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same pricing
schedule. I just want a sequential representation of how prices have changed.

If anybody has any ideas then I would enjoy reading them. I have got halfway
throught the problem but then get stuck. It is simple to see if this months
column is the same as the prior month and delete if the same. But it gets
more complicated. Again, in the table above, you would start at Mar-05 and
clear the contents of Apr-05 as it is the same pricing structure. But then
you cannot move on to comparing Apr-05 with Mar-05. You need to still use
May-05 to compare with Mar-05.

I hope I have made it clear what I am trying to acheive. Please write back
if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex

use a for/each looping macro within another for/each macro

--
Don Guillett
SalesAid Software

"Alex" wrote in message
...
Hello

Here is the problem. I have 10 products of which the prices may change
each
month. I keep monthly records going back 12 months. It looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90 $100
B $90 $90 $90 $80 $80 $90
C $85 $85 $85 $70 $70
$80
D $60 $60 $60 $60 $60 $70
etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go down
to
product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May, Apr,
Mar are all the same. However in Feb the prices are different. In other
words
there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the table
and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare prices
for
all products (A-H) with the prior month. If they are the same then the
code
will remove(.clearcontents) the prior month and then move onto the next
month. If the prices are different then those prices will remain the in
the
table and then those prices will be used to compare against prior months
to
check for price changes.

In the end the table should contain only those columns where a price
change
occured from the previous month.

In the example I give above the remaining columns would be Mar-05, Jan-05,
and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same pricing
schedule. I just want a sequential representation of how prices have
changed.

If anybody has any ideas then I would enjoy reading them. I have got
halfway
throught the problem but then get stuck. It is simple to see if this
months
column is the same as the prior month and delete if the same. But it gets
more complicated. Again, in the table above, you would start at Mar-05 and
clear the contents of Apr-05 as it is the same pricing structure. But then
you cannot move on to comparing Apr-05 with Mar-05. You need to still use
May-05 to compare with Mar-05.

I hope I have made it clear what I am trying to acheive. Please write back
if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex

This seemed to work for me in Excel 2000. Since your entries appear to be
text with a variable number of spaces it was a little tricky defining
whether
the cells were equal or not.

Sub deletedups()
Dim rw As Long, col As Long, x, y, rw1 As Long, col1 As Long

rw = ActiveSheet.UsedRange.Rows.Count
col = ActiveSheet.UsedRange.Columns.Count

For rw1 = 2 To rw
For col1 = 2 To col
' these are optional for check purposes
' x = CDbl(Cells(rw1, col1))
' y = CDbl(Cells(rw1, col1 + 1))

If CDbl(Cells(rw1, col1)) = CDbl(Cells(rw1, col1 + 1)) Then
Cells(rw1, col1).ClearContents
End If
Next
Next

End Sub


--
steveB

Remove "AYN" from email to respond
"Alex" wrote in message
...
Hello

Here is the problem. I have 10 products of which the prices may change
each
month. I keep monthly records going back 12 months. It looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90 $100
B $90 $90 $90 $80 $80 $90
C $85 $85 $85 $70 $70
$80
D $60 $60 $60 $60 $60 $70
etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go down
to
product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May, Apr,
Mar are all the same. However in Feb the prices are different. In other
words
there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the table
and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare prices
for
all products (A-H) with the prior month. If they are the same then the
code
will remove(.clearcontents) the prior month and then move onto the next
month. If the prices are different then those prices will remain the in
the
table and then those prices will be used to compare against prior months
to
check for price changes.

In the end the table should contain only those columns where a price
change
occured from the previous month.

In the example I give above the remaining columns would be Mar-05, Jan-05,
and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same pricing
schedule. I just want a sequential representation of how prices have
changed.

If anybody has any ideas then I would enjoy reading them. I have got
halfway
throught the problem but then get stuck. It is simple to see if this
months
column is the same as the prior month and delete if the same. But it gets
more complicated. Again, in the table above, you would start at Mar-05 and
clear the contents of Apr-05 as it is the same pricing structure. But then
you cannot move on to comparing Apr-05 with Mar-05. You need to still use
May-05 to compare with Mar-05.

I hope I have made it clear what I am trying to acheive. Please write back
if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex

Alex

Try this

Sub RemoveDupPrices()

Dim rCell As Range
Dim lCol As Long
Dim rRng As Range
Dim dStatic As Double

Const lLASTCOL As Long = 6

Set rRng = Sheet1.Range("A2:A5")

For Each rCell In rRng.Cells
dStatic = rCell.Offset(0, lLASTCOL).Value

For lCol = lLASTCOL - 1 To 1 Step -1
If rCell.Offset(0, lCol).Value = dStatic Then
rCell.Offset(0, lCol).ClearContents
Else
dStatic = rCell.Offset(0, lCol).Value
End If
Next lCol
Next rCell

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Alex wrote:
Hello

Here is the problem. I have 10 products of which the prices may
change each month. I keep monthly records going back 12 months. It
looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90
$100
B $90 $90 $90 $80 $80
$90
C $85 $85 $85 $70 $70
$80 D $60 $60 $60 $60 $60
$70 etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go
down to product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May,
Apr, Mar are all the same. However in Feb the prices are different.
In other words there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the
table and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare
prices for all products (A-H) with the prior month. If they are the
same then the code will remove(.clearcontents) the prior month and
then move onto the next month. If the prices are different then those
prices will remain the in the table and then those prices will be
used to compare against prior months to check for price changes.

In the end the table should contain only those columns where a price
change occured from the previous month.

In the example I give above the remaining columns would be Mar-05,
Jan-05, and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same
pricing schedule. I just want a sequential representation of how
prices have changed.

If anybody has any ideas then I would enjoy reading them. I have got
halfway throught the problem but then get stuck. It is simple to see
if this months column is the same as the prior month and delete if
the same. But it gets more complicated. Again, in the table above,
you would start at Mar-05 and clear the contents of Apr-05 as it is
the same pricing structure. But then you cannot move on to comparing
Apr-05 with Mar-05. You need to still use May-05 to compare with
Mar-05.

I hope I have made it clear what I am trying to acheive. Please write
back if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex

Why not just use Conditional formatting to make the font white of any entry
that equals the one to the right?

Assuming your data is in B2:M11, you select B2:M10, do Format / Conditional
formatting, change 'cell value is' to 'formula is' and then put in B2=C2,
choose a white font and hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Alex" wrote in message
...
Hello

Here is the problem. I have 10 products of which the prices may change
each
month. I keep monthly records going back 12 months. It looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90 $100
B $90 $90 $90 $80 $80 $90
C $85 $85 $85 $70 $70
$80
D $60 $60 $60 $60 $60 $70
etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go down
to
product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May, Apr,
Mar are all the same. However in Feb the prices are different. In other
words
there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the table
and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare prices
for
all products (A-H) with the prior month. If they are the same then the
code
will remove(.clearcontents) the prior month and then move onto the next
month. If the prices are different then those prices will remain the in
the
table and then those prices will be used to compare against prior months
to
check for price changes.

In the end the table should contain only those columns where a price
change
occured from the previous month.

In the example I give above the remaining columns would be Mar-05, Jan-05,
and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same pricing
schedule. I just want a sequential representation of how prices have
changed.

If anybody has any ideas then I would enjoy reading them. I have got
halfway
throught the problem but then get stuck. It is simple to see if this
months
column is the same as the prior month and delete if the same. But it gets
more complicated. Again, in the table above, you would start at Mar-05 and
clear the contents of Apr-05 as it is the same pricing structure. But then
you cannot move on to comparing Apr-05 with Mar-05. You need to still use
May-05 to compare with Mar-05.

I hope I have made it clear what I am trying to acheive. Please write back
if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex

Ken,

When I took a close look - they weren't really equal...
There were trailing spaces of different lengths. Trim didn't seem to work,
but Cdbl did...

--
steveB

Remove "AYN" from email to respond
"Ken Wright" wrote in message
...
Why not just use Conditional formatting to make the font white of any
entry
that equals the one to the right?

Assuming your data is in B2:M11, you select B2:M10, do Format /
Conditional
formatting, change 'cell value is' to 'formula is' and then put in B2=C2,
choose a white font and hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Alex" wrote in message
...
Hello

Here is the problem. I have 10 products of which the prices may change
each
month. I keep monthly records going back 12 months. It looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90 $100
B $90 $90 $90 $80 $80
$90
C $85 $85 $85 $70 $70
$80
D $60 $60 $60 $60 $60
$70
etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go down
to
product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May, Apr,
Mar are all the same. However in Feb the prices are different. In other
words
there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the
table
and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare prices
for
all products (A-H) with the prior month. If they are the same then the
code
will remove(.clearcontents) the prior month and then move onto the next
month. If the prices are different then those prices will remain the in
the
table and then those prices will be used to compare against prior months
to
check for price changes.

In the end the table should contain only those columns where a price
change
occured from the previous month.

In the example I give above the remaining columns would be Mar-05,
Jan-05,
and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same
pricing
schedule. I just want a sequential representation of how prices have
changed.

If anybody has any ideas then I would enjoy reading them. I have got
halfway
throught the problem but then get stuck. It is simple to see if this
months
column is the same as the prior month and delete if the same. But it gets
more complicated. Again, in the table above, you would start at Mar-05
and
clear the contents of Apr-05 as it is the same pricing structure. But
then
you cannot move on to comparing Apr-05 with Mar-05. You need to still use
May-05 to compare with Mar-05.

I hope I have made it clear what I am trying to acheive. Please write
back
if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex

I'm assuming though that that's a result of posting in text format and
trying to line up the data. The source data I would expect to be clean, and
if it wasn't then it should be sorted out before trying to do anything else.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"STEVE BELL" wrote in message
news:xE0re.5182$2K4.1102@trnddc08...
Ken,

When I took a close look - they weren't really equal...
There were trailing spaces of different lengths. Trim didn't seem to
work,
but Cdbl did...

--
steveB

On Sun, 12 Jun 2005 10:17:04 -0700, "Alex"
wrote:

Hello

Here is the problem. I have 10 products of which the prices may change each
month. I keep monthly records going back 12 months. It looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90 $100
B $90 $90 $90 $80 $80 $90
C $85 $85 $85 $70 $70 $80
D $60 $60 $60 $60 $60 $70
etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go down to
product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May, Apr,
Mar are all the same. However in Feb the prices are different. In other words
there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the table
and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare prices for
all products (A-H) with the prior month. If they are the same then the code
will remove(.clearcontents) the prior month and then move onto the next
month. If the prices are different then those prices will remain the in the
table and then those prices will be used to compare against prior months to
check for price changes.

In the end the table should contain only those columns where a price change
occured from the previous month.

In the example I give above the remaining columns would be Mar-05, Jan-05,
and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same pricing
schedule. I just want a sequential representation of how prices have changed.

If anybody has any ideas then I would enjoy reading them. I have got halfway
throught the problem but then get stuck. It is simple to see if this months
column is the same as the prior month and delete if the same. But it gets
more complicated. Again, in the table above, you would start at Mar-05 and
clear the contents of Apr-05 as it is the same pricing structure. But then
you cannot move on to comparing Apr-05 with Mar-05. You need to still use
May-05 to compare with Mar-05.

I hope I have made it clear what I am trying to acheive. Please write back
if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex


If I understand you correctly, you wish to blank an entire column (except for
the date), if the price list for items A-J is identical with the price list in
the preceding column.

You can do this with conditional formatting.

Select your data area -- for example B3:G12
Format/Conditional/Formatting Formula Is:
==SUMPRODUCT(--(B$3:B$12=C$3:C$12))=10

Format/Font and change the color to white (or whatever the background color
is).

If you want to do this in VBA, then:

==========================
Range("B3").Select
With Range("B3:G12")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SUMPRODUCT(--(B$3:B$12=C$3:C$12))=10"
.FormatConditions(1).Font.ColorIndex = 2
End With
=========================

You may not want to clear the contents, assuming you are not deleting the
entire columns, to possibly facilitate looking up the price of a given item on
a given date in the past.


--ron

On Sun, 12 Jun 2005 17:42:25 -0400, Ron Rosenfeld
wrote:

On Sun, 12 Jun 2005 10:17:04 -0700, "Alex"
wrote:

Hello

Here is the problem. I have 10 products of which the prices may change each
month. I keep monthly records going back 12 months. It looks like this...

Prod May-05 Apr-05 Mar-05 Feb-05 Jan-05 Dec-04
etc...
A $100 $100 $100 $90 $90 $100
B $90 $90 $90 $80 $80 $90
C $85 $85 $85 $70 $70 $80
D $60 $60 $60 $60 $60 $70
etc..

The above table would extend back to Apr 04 (i.e. 12 months) and go down to
product J. (i.e. 10 products).

In some instances the prices do not change month to month. e.g. May, Apr,
Mar are all the same. However in Feb the prices are different. In other words
there was a price change in MARCH-05 .

I have tried in vain to write some VBA code that will go thorugh the table
and leave only the months where price changes occurred.

So the code would start in the first column (May-05) and compare prices for
all products (A-H) with the prior month. If they are the same then the code
will remove(.clearcontents) the prior month and then move onto the next
month. If the prices are different then those prices will remain the in the
table and then those prices will be used to compare against prior months to
check for price changes.

In the end the table should contain only those columns where a price change
occured from the previous month.

In the example I give above the remaining columns would be Mar-05, Jan-05,
and Dec-04 as they show different pricing structures.

Looked at another way, I don't want any columns that have the same pricing
schedule. I just want a sequential representation of how prices have changed.

If anybody has any ideas then I would enjoy reading them. I have got halfway
throught the problem but then get stuck. It is simple to see if this months
column is the same as the prior month and delete if the same. But it gets
more complicated. Again, in the table above, you would start at Mar-05 and
clear the contents of Apr-05 as it is the same pricing structure. But then
you cannot move on to comparing Apr-05 with Mar-05. You need to still use
May-05 to compare with Mar-05.

I hope I have made it clear what I am trying to acheive. Please write back
if you need more information.

As always in this support community, any guidance is warmly received.

Best Regards


Alex


If I understand you correctly, you wish to blank an entire column (except for
the date), if the price list for items A-J is identical with the price list in
the preceding column.

You can do this with conditional formatting.

Select your data area -- for example B3:G12
Format/Conditional/Formatting Formula Is:
==SUMPRODUCT(--(B$3:B$12=C$3:C$12))=10


TYPO IN THE ABOVE FORMULA. SHOULD HAVE ONLY A SINGLE "=" SIGN AT THE START:


=SUMPRODUCT(--(B$3:B$12=C$3:C$12))=10


--ron