Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to compare/sort values in two columns of two worksheets?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to compare/sort values in two columns of two worksheets?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to compare/sort values in two columns of two worksheets?
Otto,
Thanks a lot! pemt "Otto Moehrbach" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to compare/sort values in two columns of two worksheets?
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" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to compare/sort values in two columns of two worksheets?
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 "pemt" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort or compare data from 2 different spreadsheet columns | New Users to Excel | |||
Compare 2 columns - new worksheets | Excel Worksheet Functions | |||
how to compare columns in 4 worksheets | Excel Discussion (Misc queries) | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) | |||
compare columns of different worksheets | Excel Discussion (Misc queries) |