I don't think you're going to find a formula that does this kind of thing.
Maybe you could use a macro that does the work?????
If you want to try:
Option Explicit
Sub testme()
Dim InWks As Worksheet
Dim OutWks As Worksheet
Dim myInRng As Range
Dim myCell As Range
Dim res As Variant
Dim DestCell As Range
Set InWks = Worksheets("sheet1")
Set OutWks = Worksheets("sheet2")
With InWks
Set myInRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myInRng.Cells
If IsEmpty(myCell.Offset(0, 1).Value) Then
'skip it
Else
If IsNumeric(myCell.Offset(0, 1).Value) Then
'keep looking
res = Application.Match(myCell.Value, OutWks.Range("a:a"), 0)
If IsError(res) Then
'not there, so addit
With OutWks
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
DestCell.Value = myCell.Value
DestCell.Offset(0, 1).Value = myCell.Offset(0, 1).Value
End If
End If
End If
Next myCell
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Gingit wrote:
I have the following file:
Sheet1
February to
Style # date Revenue ($)
ABB $$
ACD $$
DED $$
DEE $$
CAA $$
FEE $$
FEH $$
Sheet2
March to
date Revenue ($)
ABB $$
DED $$
DEE $$
FEE $$
FEH $$
Each month new styles are added in sheet1. How can I get a formula to look
up a style in sheet1 and if it is new to sheet 2 add it. But only add it if
it has revenue associated with it.
Thanks,
Gingit
--
Dave Peterson