Complex formula help , please.

[Colin Hayes]

Hi all


I'm wrestling with some complex formula. I'm hoping someone can assist
with a formula or macro to satisfy something I need to do in Excel.

Essentially I need to compare the content of two cells in separate
sheets. Where they are the same and certain other simple criteria are
met , then the content of the cells should be copied from one sheet to
the other.

This is the text to the formula / macro:

IF A2 in sheet 1 is the same as B2 in sheet 2

AND H2 in sheet 2 is equal to 3 or higher than J2 in sheet 2 ,

THEN make E2 in sheet 1 the same as J2 in sheet 2.

OTHERWISE leave E2 in sheet 1 as it is.


Grateful for any assistance with this complicated scenario.

Phew.

 

[Claus Busch]

Hi Colin,

Am Mon, 30 Apr 2012 19:50:05 +0100 schrieb Colin Hayes:

IF A2 in sheet 1 is the same as B2 in sheet 2

AND H2 in sheet 2 is equal to 3 or higher than J2 in sheet 2 ,

THEN make E2 in sheet 1 the same as J2 in sheet 2.

OTHERWISE leave E2 in sheet 1 as it is.

if E2 is empty, you can use:
=IF(AND(OR(Sheet2!H2=3,Sheet2!H2>Sheet2!J2),A2=Sheet2!B2),Sheet2!J2,"")
If E2 is not empty, you need a macro.


Regards
Claus Busch

 

[Colin Hayes]

Hi Colin,

Am Mon, 30 Apr 2012 19:50:05 +0100 schrieb Colin Hayes:


if E2 is empty, you can use:
=IF(AND(OR(Sheet2!H2=3,Sheet2!H2>Sheet2!J2),A2=Sheet2!B2),Sheet2!J2,"")
If E2 is not empty, you need a macro.


Regards
Claus Busch

Hi Claus

Thanks for getting back so helpfully.

E2 on sheet one is not empty and does have content.

Could you help construct a macro?

Many thanks for your time and expertise.

 

[Claus Busch]

Hi Colin,

Am Mon, 30 Apr 2012 20:44:21 +0100 schrieb Colin Hayes:

E2 on sheet one is not empty and does have content.

copy the code into the code modul of sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$2" Then Exit Sub

With Sheets("Sheet2")
If Target = .[B2] And (.[H2] = 3 Or .[H2] > .[J2]) Then
[E2] = .[J2]
End If
End With
End Sub

If you change A2 in Sheet1 and the conditions are true, the code
changes E2.


Regards
Claus Busch

 

[Colin Hayes]

Hi Colin,

Am Mon, 30 Apr 2012 20:44:21 +0100 schrieb Colin Hayes:

E2 on sheet one is not empty and does have content.

copy the code into the code modul of sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$2" Then Exit Sub

With Sheets("Sheet2")
If Target = .[B2] And (.[H2] = 3 Or .[H2] > .[J2]) Then
[E2] = .[J2]
End If
End With
End Sub

If you change A2 in Sheet1 and the conditions are true, the code
changes E2.


Regards
Claus Busch

Hi Claus

OK That's excellent - thank you very much indeed. It does the job
exactly for the target line.

I'll need to try to adapt it so I can use as a formula in a more
traditional macro that I can run though.

This is because it needs to apply itself to all the rows in the sheet ,
one by one. Also the number in B2 will need to match against column A.
This is because it may be in future checks , the order may vary.
(Sorry!).

Perhaps helper columns for column E and some copying and some
pasting-back could be employed.

So if a match for B2 is found in column A then the relevant changes
would be made in that row. The macro would then check the number in B3
against all in column A and so on to the bottom of the sheet. Mind
Boggling!

 

[GS]

Hi Colin,
It's been awhile since we exchanged. Just want to let you know that
Claus has a knack for making formulas that simplify working with
"mind-boggling" issues! I enjoy working through his solutions because
they're always a good learning exercise!

Best wishes...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Colin Hayes]

Hi Colin,
It's been awhile since we exchanged. Just want to let you know that
Claus has a knack for making formulas that simplify working with
"mind-boggling" issues! I enjoy working through his solutions because
they're always a good learning exercise!

Best wishes...

Hi Garry

Nice to hear from you.

Yes I agree. It's the ability to hit nails on heads with such piercing
logic which is always most impressive.


Best Wishes

 

[Claus Busch]

Hi Colin,

Am Mon, 30 Apr 2012 23:04:19 +0100 schrieb Colin Hayes:

I'll need to try to adapt it so I can use as a formula in a more
traditional macro that I can run though.

I thought, you would change E while you enter values in A, sorry.
Try the code below in a standard module:

Sub Checking()
Dim LRow As Long
Dim i As Long

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To LRow
If .Cells(i, 1) = Sheets("Sheet2").Cells(i, 2) And _
(Sheets("sheet2").Cells(i, 8) = 3 Or Sheets("sheet2") _
.Cells(i, 8) >= Sheets("sheet2").Cells(i, 10)) Then
.Cells(i, 5) = Sheets("Sheet2").Cells(i, 10)
End If
Next
End With
End Sub


Regards
Claus Busch

 

[Claus Busch]

Hi Colin,

Am Mon, 30 Apr 2012 23:04:19 +0100 schrieb Colin Hayes:

So if a match for B2 is found in column A then the relevant changes
would be made in that row. The macro would then check the number in B3
against all in column A and so on to the bottom of the sheet. Mind
Boggling!

don't use the macro. First I have some questions.
If a match for B2 is found, i.e. in A7, is now to check whether H7=3 or
H7>J7 and insert J7 in E7 if conditions are true?


Regards
Claus Busch

 

[Claus Busch]

Hi Colin,

Am Tue, 1 May 2012 11:37:58 +0200 schrieb Claus Busch:

don't use the macro. First I have some questions.
If a match for B2 is found, i.e. in A7, is now to check whether H7=3 or
H7>J7 and insert J7 in E7 if conditions are true?

I hope, I understood your problem.
If you have xl2007 or later then try in row2 of sheet1:
=IFERROR(IF(OR(INDEX(Sheet2!H:H,MATCH(A2,Sheet2!$B$1:$B$200,0))=3,INDEX(Sheet2!H:H,MATCH(A2,Sheet2!$B$1:$B$200,0))>INDEX(Sheet2!J:J,MATCH(A2,Sheet2!$B$1:$B$200,0))),INDEX(Sheet2!J:J,MATCH(A2,Sheet2!$B$1:$B$200,0)),E2),E2)
If you have an earlier version try:
=IF(COUNTIF(Sheet2!B:B,A2)=0,E2,IF(OR(INDEX(Sheet2!H:H,MATCH(A2,Sheet2!$B$1:$B$200,0))=3,INDEX(Sheet2!H:H,MATCH(A2,Sheet2!$B$1:$B$200,0))>INDEX(Sheet2!J:J,MATCH(A2,Sheet2!$B$1:$B$200,0))),INDEX(Sheet2!J:J,MATCH(A2,Sheet2!$B$1:$B$200,0)),E2))
Or try this macro:

Sub Checking()
Dim LRow1 As Long
Dim LRow2 As Long
Dim rngC As Range
Dim i As Long

With Sheets("Sheet2")
LRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row
LRow2 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("B2:B" & LRow1)
For i = 2 To LRow2
If rngC = Sheets("Sheet1").Cells(i, 1) Then
If .Cells(rngC.Row, 8) = 3 Or .Cells(rngC.Row, 8) >
..Cells(rngC.Row, 10) Then
Sheets("Sheet1").Cells(i, 5) = .Cells(rngC.Row, 10)
End If
End If
Next i
Next rngC
End With
End Sub


Regards
Claus Busch

 

[Colin Hayes]

Hi Claus

OK a thousand thanks for your considerable help with this.
It's doing the job fine.


Best Wishes

 

[Claus Busch]

Hi Colin,

Am Wed, 2 May 2012 18:03:55 +0100 schrieb Colin Hayes:

It's doing the job fine.

glad to help and thank you for feedback.
What do you use? Formula or Macro?

Regards
Claus Busch

 

[Colin Hayes]

Hi Colin,

Am Wed, 2 May 2012 18:03:55 +0100 schrieb Colin Hayes:


glad to help and thank you for feedback.
What do you use? Formula or Macro?

Regards
Claus Busch

Hi Claus

Well I'm experimenting with both. The macro causes a long pause while it
processes. The formula works too. Both very good. I could probably build
the formula into a macro too so it's all good.



Best Wishes

 

[Claus Busch]

Hi Colin,

Am Wed, 2 May 2012 22:22:00 +0100 schrieb Colin Hayes:

Well I'm experimenting with both. The macro causes a long pause while it
processes. The formula works too. Both very good. I could probably build
the formula into a macro too so it's all good.

this macro should be faster. There are no nested for each..next
Please test it in a copy of your workbook:
Sub Checking()
Dim LRow1 As Long
Dim LRow2 As Long
Dim rngC As Range
Dim myFind As Long

With Sheets("Sheet2")
LRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row
LRow2 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Sheets("Sheet1").Range("A2:A" & LRow2)
If WorksheetFunction.CountIf(.Range("B1:B" & LRow2), rngC) > 0 Then
myFind = WorksheetFunction.Match(rngC, .Range("B1:B" & LRow1), 0)
If .Cells(myFind, "H") = 3 Or _
.Cells(myFind, "H") > .Cells(myFind, "J") Then
rngC.Offset(0, 4) = .Cells(myFind, "J")
End If
End If
Next rngC
End With
End Sub


Regards
Claus Busch