Match and Sort for two range of data on different worksheets?

[Tan]

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.

[Westaradg]

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.
>
>
>
>
>

[kevindmorgan]

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 2001
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:C201").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

[KC]

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).Range("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.
>
>
>
>
>