Matching Data in Excel 2002

[Mr. Low]

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

 

[bj]

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.

 

[Herbert Seidenberg]

Here is a semiautomatic way without VBA
Sort BlockA and BlockB independently.
Assume A2=C2="R1". The first row has to match.
Name the first and third column RefA and RefB respectively.
Conditionally format RefA and RefB respectively with
=AND(INDEX(RefA,ROW()-1)>INDEX(RefB,ROW()-1),INDEX(RefB,ROW()-1)<>0)
=AND(INDEX(RefB,ROW()-1)>INDEX(RefA,ROW()-1),INDEX(RefA,ROW()-1)<>0)
and format them with a red pattern.
Click on the first red cell and its data and insert blanks.
Repeat.
Adjust the 1 in the formula if "R1" is not at A2.

 

[Mr. Low]

Dear Sir,

Thank you for your instant reply.

As I do not have any groundwork on programing, I find it very difficult to
understand these steps.

At present MS Office 2002 - 2007 still do not have a simple and easy to use
button at the tool bar for this data matching function. I would like to put
forward to a suggestion to Microsoft to develop this button in the coming
version.

I have the folowing ideas in my mind to share with the community:

This button will be something like the present "Sort Button", maybe we
could call it a "Matching Button". This button will allow the matching of the
lines in different blocks of data at the same worksheet with common matching
refrencece.

The matching function could be done for lines of data up to 4 to 5
highlighted blocks or more. Lne with the same matching refrence from
different block must appear in the same line after matching, with blank cells
for unmatched lines.

The steps of the matching is first to click at the Matching Button, then
select the first block of data to be matched , then select its matching
refrence column say ( B2..B500). After this, select the second block of the
data to be matched with, then select its matching reference say (K2.. K500),
and finally select output refrence in (B2..B500) to be in ascending or
decending order.

The final output will have the line with identical matching refrence appear
at the same row of the worksheet without altering its original content.

I believe this Matching Button is not difficult to designed under today's
advanced technology. It certainly would help the users with very litlle
programing background to do the matching task at their work place. Also this
button would prevent errors ( say key in a wrong cell refrence etc) when the
task is done by programming the spreadsheet.

I hope my input and suggestion is helpful for the Microsoft as well as the
community.

Kind Regards

Low Seng Kuang
Malaysia

 

[Mr. Low]

Dear Sir,

Thank you for your instant reply.

As I do not have any groundwork on programing, I find it very difficult to
understand these steps.

At present MS Office 2002 - 2007 still do not have a simple and easy to use
button at the tool bar for this data matching function. I would like to put
forward to a suggestion to Microsoft to develop this button in the coming
version.

I have the folowing ideas in my mind to share with the community:

This button will be something like the present "Sort Button", maybe we
could call it a "Matching Button". This button will allow the matching of the
lines in different blocks of data at the same worksheet with common matching
refrencece.

The matching function could be done for lines of data up to 4 to 5
highlighted blocks or more. Lne with the same matching refrence from
different block must appear in the same line after matching, with blank cells
for unmatched lines.

The steps of the matching is first to click at the Matching Button, then
select the first block of data to be matched , then select its matching
refrence column say ( B2..B500). After this, select the second block of the
data to be matched with, then select its matching reference say (K2.. K500),
and finally select output refrence in (B2..B500) to be in ascending or
decending order.

The final output will have the line with identical matching refrence appear
at the same row of the worksheet without altering its original content.

I believe this Matching Button is not difficult to designed under today's
advanced technology. It certainly would help the users with very litlle
programing background to do the matching task at their work place. Also this
button would prevent errors ( say key in a wrong cell refrence etc) when the
task is done by programming the spreadsheet.

I hope my input and suggestion is helpful for the Microsoft as well as the
community.

Kind Regards

Low Seng Kuang
Malaysia