Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
Hi all,
I had a tedious problem here. I had two worksheets on e same workbook whereby I have one range of data on Sheet 1 and e other range of data on Sheet 2. [Data given to me]... The data on sheet 1 had info on 1st half of financial year whereas e data on sheet 2 had info on 2nd half of financial year. It appears as below: Sheet 1 (1st half) Sheet2 (2nd half) --------- -------- Company Name Sales Fig. Company Name Sales Fig. BAA Limited 6000 XYZ Limited 1000 ABC Limited 7000 AAA Limited 8000 XYZ Limited 1000 ABC Limited 2000 AAA Limited 8000 BBB Limited 6000 BBB Limited 1000 CCC Limited 500 .......the range goes on for maybe few thousand rows. These 2 range of data here at my example has matching entries but different sequence-- logical bcos different company performS differently at each half of financial year. [Problem Identified]... I have to first sort my data in Sheet 2 (2nd half) to say, top 200 performing companies. The sample data on Sheet 2 above will becomes the one like below after sorted: Sheet 2 --------- Company Name Sales Fig. AAA Limited 8000 BBB Limited 6000 ABC Limited 2000 XYZ Limited 1000 ....goes on till 200 rows. Now I need to match both range of data such that, in our sample here, AAA Limited, BBB Limited, ABC Limited and XYZ Limited also appears in Sheet 1. Sample as follows (**denotes matching entries): Sheet 1 --------- Company Name Sales Fig. BAA Limited 6000 (to delete) **ABC Limited 7000 **XYZ Limited 1000 **AAA Limited 8000 **BBB Limited 1000 CCC Limited 500 (to delete) Here in Sheet 1, I have to delete away entries for BAA Limited and CCC Limited bcos they r unmatched entries, meaning not found in Sheet 2. After deletion in Sheet 1, I have to match manually at present such that AAA Limited in Sheet 1 is my first entry bcos in Sheet 2, AAA Limited is e first entry. [After Analysis] Both Sheet 1 and Sheet 2 should look like this after all the effort on sorting and deletion. Sheet 1 (1st half) Sheet 2 (2nd half) -------- --------- AAA Limited 8000 AAA Limited 8000 BBB Limited 1000 BBB Limited 6000 ABC Limited 7000 ABC Limited 2000 XYZ Limited 1000 XYZ Limited 1000 Could anyone enlighten me on this. Thanks in advance... Tan. |
#2
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
This link may help you. http://www.contextures.com/xladvfilter02.html
"Tan" wrote: Hi all, I had a tedious problem here. I had two worksheets on e same workbook whereby I have one range of data on Sheet 1 and e other range of data on Sheet 2. [Data given to me]... The data on sheet 1 had info on 1st half of financial year whereas e data on sheet 2 had info on 2nd half of financial year. It appears as below: Sheet 1 (1st half) Sheet2 (2nd half) --------- -------- Company Name Sales Fig. Company Name Sales Fig. BAA Limited 6000 XYZ Limited 1000 ABC Limited 7000 AAA Limited 8000 XYZ Limited 1000 ABC Limited 2000 AAA Limited 8000 BBB Limited 6000 BBB Limited 1000 CCC Limited 500 ......the range goes on for maybe few thousand rows. These 2 range of data here at my example has matching entries but different sequence-- logical bcos different company performS differently at each half of financial year. [Problem Identified]... I have to first sort my data in Sheet 2 (2nd half) to say, top 200 performing companies. The sample data on Sheet 2 above will becomes the one like below after sorted: Sheet 2 --------- Company Name Sales Fig. AAA Limited 8000 BBB Limited 6000 ABC Limited 2000 XYZ Limited 1000 ...goes on till 200 rows. Now I need to match both range of data such that, in our sample here, AAA Limited, BBB Limited, ABC Limited and XYZ Limited also appears in Sheet 1. Sample as follows (**denotes matching entries): Sheet 1 --------- Company Name Sales Fig. BAA Limited 6000 (to delete) **ABC Limited 7000 **XYZ Limited 1000 **AAA Limited 8000 **BBB Limited 1000 CCC Limited 500 (to delete) Here in Sheet 1, I have to delete away entries for BAA Limited and CCC Limited bcos they r unmatched entries, meaning not found in Sheet 2. After deletion in Sheet 1, I have to match manually at present such that AAA Limited in Sheet 1 is my first entry bcos in Sheet 2, AAA Limited is e first entry. [After Analysis] Both Sheet 1 and Sheet 2 should look like this after all the effort on sorting and deletion. Sheet 1 (1st half) Sheet 2 (2nd half) -------- --------- AAA Limited 8000 AAA Limited 8000 BBB Limited 1000 BBB Limited 6000 ABC Limited 7000 ABC Limited 2000 XYZ Limited 1000 XYZ Limited 1000 Could anyone enlighten me on this. Thanks in advance... Tan. |
#3
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]() Tan, I love a challenge! I am not an "expert" in Excel, and I am sure some of my methods of doing things could be done by some much easier than I make them, but!!! I wrote this macro, and it does what you want. You may have to modify some things. Here are the assumptions I made from what you wrote: 1. You have data on two worksheets. (my macro uses sheet1 and sheet2. You'll have to change them to suit your workbook) 2. There is only data in columns A and B in each worksheet. 3. There are 200 company names listed in column A on sheet2. (if there are more, you'll have to modify the macro, and replace the "201" values to however many companies are listed +1) 4. There are several thousand companies listed in column A on sheet 1. In my example, I used 2000. Replace all references to "2001" with whatever is the case on your sheet1. (# of companies +1) You will have to prep the sheet by naming some ranges: 1. Name the range A2:B201 on sheet2 "bothcolumns" (unless you have more than 200 companies...then of course, modify the range to fit your case.) 2. Name the range A2:A201 on sheet2 "firstcolumn" (again, make the actual range fit your case) Put the macro in a module, and run it. ==========copy start============ Sub sortdata() Dim x As Integer Sheets("sheet2").Select Range("bothcolumns").Select Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Sheet1").Select Cells(2, 3).Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],bothcolumns,2,FALSE)" Selection.AutoFill Destination:=Range("C2:C2001"), Type:=xlFillDefault Range("C2:C2001").Select Selection.Copy Range("C2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.Replace What:="#N/A", Replacement:="delete", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Application.CutCopyMode = False Cells(2, 3).Select For x = 1 To 2000 If ActiveCell.Text = "delete" Then Selection.EntireRow.Delete If ActiveCell.Text = "delete" Then ActiveCell.Offset(-1, 0).Select Else End If Else End If ActiveCell.Offset(1, 0).Select Next x Range("C2").Select ActiveCell.FormulaR1C1 = "=MATCH(RC[-2],firstcolumn,0)" Selection.AutoFill Destination:=Range("C2:C201"), Type:=xlFillDefault Range("C2:C201").Select Selection.Copy Range("C2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Application.CutCopyMode = False Range("A2:C26").Select Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("C:C").Select Selection.Delete Shift:=xlToLeft Range("A1").Select End Sub ========copy stop======== I really hope it works! I tried it out here, and got what I think you want! Please let me know. Kevin -- kevindmorgan ------------------------------------------------------------------------ kevindmorgan's Profile: http://www.excelforum.com/member.php...o&userid=32232 View this thread: http://www.excelforum.com/showthread...hreadid=520410 |
#4
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
Not sure how many lines you have down from top to and include "Company Name..."
if you do not have these heading rows, try this. I have not tested it myself. Sub main() Dim rng As Range Sheets(2).Range("A1").CurrentRegion.Sort key1:=Range("B1"), Order:=xlDescending Sheets(1).Activate Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown)) For i = 1 To 200 rownr = Application.WorksheetFunction.Match(Sheets(2).Rang e("A" & i), rng, 0).Rows Cells(rownr, 3) = i Next i Range("A1").CurrentRegion.Sort key1:=Range("C1"), Order:=xlAscending Columns(3).Delete lrow = Range("A65536").End(xlUp).Rows Rows("201:" & lrow).Delete End Sub "Tan" wrote: Hi all, I had a tedious problem here. I had two worksheets on e same workbook whereby I have one range of data on Sheet 1 and e other range of data on Sheet 2. [Data given to me]... The data on sheet 1 had info on 1st half of financial year whereas e data on sheet 2 had info on 2nd half of financial year. It appears as below: Sheet 1 (1st half) Sheet2 (2nd half) --------- -------- Company Name Sales Fig. Company Name Sales Fig. BAA Limited 6000 XYZ Limited 1000 ABC Limited 7000 AAA Limited 8000 XYZ Limited 1000 ABC Limited 2000 AAA Limited 8000 BBB Limited 6000 BBB Limited 1000 CCC Limited 500 ......the range goes on for maybe few thousand rows. These 2 range of data here at my example has matching entries but different sequence-- logical bcos different company performS differently at each half of financial year. [Problem Identified]... I have to first sort my data in Sheet 2 (2nd half) to say, top 200 performing companies. The sample data on Sheet 2 above will becomes the one like below after sorted: Sheet 2 --------- Company Name Sales Fig. AAA Limited 8000 BBB Limited 6000 ABC Limited 2000 XYZ Limited 1000 ...goes on till 200 rows. Now I need to match both range of data such that, in our sample here, AAA Limited, BBB Limited, ABC Limited and XYZ Limited also appears in Sheet 1. Sample as follows (**denotes matching entries): Sheet 1 --------- Company Name Sales Fig. BAA Limited 6000 (to delete) **ABC Limited 7000 **XYZ Limited 1000 **AAA Limited 8000 **BBB Limited 1000 CCC Limited 500 (to delete) Here in Sheet 1, I have to delete away entries for BAA Limited and CCC Limited bcos they r unmatched entries, meaning not found in Sheet 2. After deletion in Sheet 1, I have to match manually at present such that AAA Limited in Sheet 1 is my first entry bcos in Sheet 2, AAA Limited is e first entry. [After Analysis] Both Sheet 1 and Sheet 2 should look like this after all the effort on sorting and deletion. Sheet 1 (1st half) Sheet 2 (2nd half) -------- --------- AAA Limited 8000 AAA Limited 8000 BBB Limited 1000 BBB Limited 6000 ABC Limited 7000 ABC Limited 2000 XYZ Limited 1000 XYZ Limited 1000 Could anyone enlighten me on this. Thanks in advance... Tan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to Match and Sort two range of data? | New Users to Excel | |||
MATCH UP DATA IN COLUMNS | Excel Worksheet Functions | |||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
How to match sort and lineup 2 sets of data | Excel Worksheet Functions |