Complex formula help , please.
 

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.

Complex formula help , please.
 

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!H2Sheet2!J2),A2=She et2!B2),Sheet2!J2,"")
If E2 is not empty, you need a macro.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Complex formula help , please.
 

In article , Claus Busch
writes
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!H2Sheet2!J2),A2=Sh eet2!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.

Complex formula help , please.
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Complex formula help , please.
 

In article , Claus Busch
writes
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!

Complex formula help , please.
 

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

Complex formula help , please.
 

In article , GS writes
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

Complex formula help , please.
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Complex formula help , please.
 

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
H7J7 and insert J7 in E7 if conditions are true?


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Complex formula help , please.
 

Hi Colin,

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

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
H7J7 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(Sheet 2!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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Complex formula help , please.
 

Hi Claus

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


Best Wishes

Complex formula help , please.
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Complex formula help , please.
 

In article , Claus Busch
writes
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

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

Complex formula help , please.
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2