I have an inventory list online that is constantly updated. What I need
to do is check and see which new products have been added and old
products that have been discontinued. Could I merge the old inv. with
the new and run a macro or a Comparison phrase with multiple conditions
that would maybe identify with cell shading or font color the new
inventory that has been added and the old that has been removed. An
easy way to identify the products is by column A which is the mfr_code.


ex. of the mfr_code in column A
ACO-APO30
ACO-MS1/8
AMB-V4801
GEM-GH3910
ect....

Maybe, I could use the Match function with comparing sheets I could use
another column or the same column with conditional formating to color
the cells as an indicator to show which products have been added and a
different cell color for the ones that have been discontinued or a
macro that would do something similiar.

Thank you for your time,

Greg


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

the ones that have been removed would need to be shown on the old list (they
aren't on the new list I assume). The ones added would be on the new list.

Use conditional formatting with Formula is rather than cell value is. Use
the =Countif(sheet2!$A:$A,A2)0

Regards,
Tom Ogilvy

stiv wrote in message
...

I have an inventory list online that is constantly updated. What I need
to do is check and see which new products have been added and old
products that have been discontinued. Could I merge the old inv. with
the new and run a macro or a Comparison phrase with multiple conditions
that would maybe identify with cell shading or font color the new
inventory that has been added and the old that has been removed. An
easy way to identify the products is by column A which is the mfr_code.


ex. of the mfr_code in column A
ACO-APO30
ACO-MS1/8
AMB-V4801
GEM-GH3910
ect....

Maybe, I could use the Match function with comparing sheets I could use
another column or the same column with conditional formating to color
the cells as an indicator to show which products have been added and a
different cell color for the ones that have been discontinued or a
macro that would do something similiar.

Thank you for your time,

Greg


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

If 'A' is the old list then put the new list in say 'D'

in 'B' put a MATCH where you look for the value in A in
the list in D
Set a conditional format where if Match is #N/A the value
in 'A' has a red backdrop ... ie not in the new, dropped

In 'E' put a match where it looks for the value in 'D' in
the list in 'A'
set conditional formatting for D to say a green
background where 'E' is N/A ie a new entry


The following proc assumes the old list in A from A1 and
the new list in D from D1
It adds th ematch formulae AND the conditional formatting
too:

Sub Builder()

With Range(Range("a1"), _
Range("A1").End(xlDown)).Offset(0, 1)

.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC1,RC4,False)),0,MATCH(RC1,RC4,Fa lse))"

End With
With Range(Range("a1"), _
Range("B1").End(xlDown))

.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=$B1=0"
.FormatConditions(1).Interior.ColorIndex = 3

End With

With Range(Range("D1"), _
Range("D1").End(xlDown)).Offset(0, 1)

.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC4,RC1,False)),0,MATCH(RC4,RC1,Fa lse))"

End With

With Range(Range("D1"), _
Range("E1").End(xlDown))

.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=$E1=0"
.FormatConditions(1).Interior.ColorIndex = 35

End With

End Sub





Patrick Molloy
Microsoft Excel MVP




-----Original Message-----

I have an inventory list online that is constantly
updated. What I need
to do is check and see which new products have been
added and old
products that have been discontinued. Could I merge the
old inv. with
the new and run a macro or a Comparison phrase with
multiple conditions
that would maybe identify with cell shading or font
color the new
inventory that has been added and the old that has been
removed. An
easy way to identify the products is by column A which
is the mfr_code.


ex. of the mfr_code in column A
ACO-APO30
ACO-MS1/8
AMB-V4801
GEM-GH3910
ect....

Maybe, I could use the Match function with comparing
sheets I could use
another column or the same column with conditional
formating to color
the cells as an indicator to show which products have
been added and a
different cell color for the ones that have been
discontinued or a
macro that would do something similiar.

Thank you for your time,

Greg


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Thank you for your help,

Patrick that is exactly what I was looking to do. But, when I ran th
code and put the old in column A and the new in column D the results i
each were as follows

column B
=IF(ISNA(MATCH(RC1,RC4,False)),0,MATCH(RC1,RC4,Fal se))

column E
=IF(ISNA(MATCH(RC4,RC1,False)),0,MATCH(RC4,RC1,Fal se))

Row A had 1670 product code entries
Row B had 2322 product code entries

This would work to have Column A look at column D and list in column
the products that do not match which would list old products. (delet
matching products)

Have column D look at column A and list in E the products that do no
match which would list new products (delete matching products)

Thank you for your time,

Gre

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com