Dear Sir,

My posting entiltled "Matching Data in Excel 2002" on October 18, 2006 refers.

I would like to put forward a suggestion to add a Data Matching Button to
Excel tool bar as per my discussion on the forum.

I think MS Office 2007 (Beta) is at its finalising stage, I do not know you
still have time to include this if my suggestion is sound and wise.

Anyway please keep in view for my suggestion future versions.

I am using Seagate Infordesktop Software that generate reports in Excel 2002
format. I need to do the matchng of block of data collected from different
sources on the same spreadsheet quite frequently. This data file usually very
big. II am not able to program the spreadsheet as my programing knowledge is
very limited.

If my suggestion is implemented in your future version, it will certainly be
a great help to me and many users around the world.

Kind Regards

Low Seng Kuang
Maslaysia


PS: For your easy reference, the online discussion reads at MS Office
Discussion Group reads as follow:: < Please read from the bottom
.................................................. .................................................. .....................

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

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

A36B58K641

================================================== ===

"bj" wrote:
There are
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





----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions