Can anybody crack this VBA problem?

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
[email protected]
"Alex" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> 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" <[email protected]>
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 <[email protected]>
wrote:

>On Sun, 12 Jun 2005 10:17:04 -0700, "Alex" <[email protected]>
>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