how to compare/sort values in two columns of two worksheets?

[pemt]

dear all,

i need to combine two worksheets into one and display the 3rd one.

worksheet1 worksheet2
name value1 value2 name value3 value4
a 4 6 e 2 8
b 12 18 f 30 60
c 45 55 g 8 25

if value1>=value3 and also value2<=value4, display the row in worksheet1 and
the row in worksheet2 into the same row in the 3rd worksheet.
So, the display should be
a 4 6 e 2 8
b 12 18 g 8 25
c 45 55 f 30 60

how to formula this in excel?

thanks a lot!
pemt

 

[Otto Moehrbach]

pemt
Formulas will not do this for you. The following VBA macro will. I assumed
your sheets are named One, Two, and Three. Change this in the code as
needed. I also assumed that only one row at most in sheet Two would meet
the criteria for each row in sheet One. If this is not so and you want all
rows that meet the criteria copied, delete the line of code "Exit For". HTH
Otto
Sub aCombine()
Dim Dest As Range, i As Range, j As Range
Dim rOneColB As Range, rTwoColB As Range
Sheets("One").Select
Set Dest = Sheets("Three").Range("A2")
Set rOneColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
With Sheets("Two")
Set rTwoColB = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
For Each i In rOneColB
For Each j In rTwoColB
If i.Value >= j.Value And _
i.Offset(, 1).Value <= j.Offset(, 1).Value Then
i.Offset(, -1).Resize(, 3).Copy Dest
j.Offset(, -1).Resize(, 3).Copy Dest.Offset(, 3)
Set Dest = Dest.Offset(1)
Exit For
End If
Next j
Next i
End With
End Sub

 

[pemt]

Otto,
Thanks a lot!
pemt

pemt
Formulas will not do this for you. The following VBA macro will. I assumed
your sheets are named One, Two, and Three. Change this in the code as
needed. I also assumed that only one row at most in sheet Two would meet
the criteria for each row in sheet One. If this is not so and you want all
rows that meet the criteria copied, delete the line of code "Exit For". HTH
Otto
Sub aCombine()
Dim Dest As Range, i As Range, j As Range
Dim rOneColB As Range, rTwoColB As Range
Sheets("One").Select
Set Dest = Sheets("Three").Range("A2")
Set rOneColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
With Sheets("Two")
Set rTwoColB = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
For Each i In rOneColB
For Each j In rTwoColB
If i.Value >= j.Value And _
i.Offset(, 1).Value <= j.Offset(, 1).Value Then
i.Offset(, -1).Resize(, 3).Copy Dest
j.Offset(, -1).Resize(, 3).Copy Dest.Offset(, 3)
Set Dest = Dest.Offset(1)
Exit For
End If
Next j
Next i
End With
End Sub

 

[pemt]

Otto,
Thanks again.
It worked beautifully for me. Just one more question: if I have more than 3
columns in one row, like "a 4 6 address age", is it fine just to
change "3" to "5" in the code?
It's the first time I used macro, I wonder, for me as a non-programmer, is
there any interface or add-ins to deal with the similar questions I asked
before? or is it possible to convert the code you wrote into some
interface/add-ins, so I could use it for the similar analysis without
changing the code?
Honestly, I had to search for VB language to understand what your code means.
pemt

 

[Otto Moehrbach]

Yes, change the "3" in "Resize(,3)" to a"5". However, you also have to
change the "3" in Dest.Offset(,3). Dest is set to Column A and that's where
the first set of data is pasted. Dest.offset(,3) is Column D. The new
destination would be Column F so that 3 would have to be changed to 5 also.
Otto