Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Excel 2002
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Excel 2002
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Excel 2002
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 -- A36B58K641 "bj" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching Data in Excel 2002
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 -- -- A36B58K641 "Herbert Seidenberg" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update data in Excel | New Users to Excel | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
Excel - Matching data | Excel Discussion (Misc queries) | |||
Does Excel 2002 have a List>Create List option under Data? | Excel Discussion (Misc queries) | |||
Fetching External Data from Excel | Excel Discussion (Misc queries) |