vlookup

[Guest]

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]

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

 

[Guest]

Thanks Dave I'll give it a try.

Gingit

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