View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default Matching Data in Excel 2002

assuming block a is columns A and B
and block b is columns C and S
and the data to be compapered starts in row 3
I would first sort Block A
Then sort block B
and run a macro similar to

sub sortwithblanks()
r = 3
10 if cells(r,1) = 0 and cells(r,3) = 0 then goto 99
if cells(r,1)=cells(r,3) then goto 20
if cells(r,1)cells(r,3) then range(cells(r,1),cells(r,2).select
if cells(r,1)<cells(r,3) then range(cells(r,3),cells(r,4).select
Selection.Insert Shift:=xlDown
20 r = r+1
99 end sub

There are, of course, many ways to do this.

"Mr. Low" wrote:

Dear Sir,

I need to match each row of the data from block A with each row of data from
block B with the common reference.

May I know how to do it.

My illustration is as follows:

Before Matching
Block A Block B
Ref Data Ref Data
R1 xxx R6 xxx
R6 xxx R1 xxx
R8 xxx R2 xxx
R9 xxx R3 xxx
R2 xxx R4 xxx
R3 xxx R7 xxx

After Matching
Block A Block B
Ref Data Ref Data
R1 xxx R1 xxx
R2 xxx R2 xxx
R3 xxx R3 xxx
R4 xxx
R6 xxx R6 xxx
R8 xxx
R7 xxx
R9 xxx
< Ummatched data will have a empty column in its right or left as illustrated

What formulas do you use and what are the key board steps please ?

Thanks

Low

--
A36B58K641