Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match and sort
Hi,
I have some data like this in excel sheet in two columns A and B. I want to match the items in Column B with column A and if it finds any match has to place that item in the same column but in the row where it was matched. A B In the diamond In the diamond In the diamond Trench Safety In the diamond The City The City The City The City Trench Safety Result should be like this: In the diamond In the diamond In the diamond In the diamond The City The City The City The City Trench Safety Trench Safety It should place in the first cell only though it has multiple matching cells in the left column as shown above. Can i do this with some match or sort ..How can i do this |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match and sort
Hi,
Try this. It assumes there only ONE set of like data i.e. "In the diamond" does not appear in two separate blocks. If this not the case, the code will not work correctly! HTH Sub MatchData() Dim ws1 As Worksheet Dim lastrow As Long, r As Long Set ws1 = Worksheets("Sheet1") ws1.Activate With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set rng = .Range("A1:A" & lastrow) ' List of column A entries lastrow = .Cells(Rows.Count, "B").End(xlUp).Row For r = 1 To lastrow ' loop through column B of Sheet1 res = Application.Match(.Cells(r, "B"), rng, 0) ' find match with column A If Not IsError(res) Then ' Match found If r < res Then .Cells(res, 2) = .Cells(r, "B") ' place entry in first row for this match .Cells(r, "B") = "" ' Clear this entry from column B End If End If Next r End With End Sub "vijaya" wrote: Hi, I have some data like this in excel sheet in two columns A and B. I want to match the items in Column B with column A and if it finds any match has to place that item in the same column but in the row where it was matched. A B In the diamond In the diamond In the diamond Trench Safety In the diamond The City The City The City The City Trench Safety Result should be like this: In the diamond In the diamond In the diamond In the diamond The City The City The City The City Trench Safety Trench Safety It should place in the first cell only though it has multiple matching cells in the left column as shown above. Can i do this with some match or sort ..How can i do this |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match and sort
Hi,
The data has been clubbed on posting the message. It should be something liek this: Original List: A B In the diamond In the diamond In the diamond The City In the diamond Trench The City The City Trench Result : A B In the diamond In the diamond In the diamond In the diamond The City The City The City Trench Trench Data is in cells of A and B columns Toppers" wrote: Hi, Try this. It assumes there only ONE set of like data i.e. "In the diamond" does not appear in two separate blocks. If this not the case, the code will not work correctly! HTH Sub MatchData() Dim ws1 As Worksheet Dim lastrow As Long, r As Long Set ws1 = Worksheets("Sheet1") ws1.Activate With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set rng = .Range("A1:A" & lastrow) ' List of column A entries lastrow = .Cells(Rows.Count, "B").End(xlUp).Row For r = 1 To lastrow ' loop through column B of Sheet1 res = Application.Match(.Cells(r, "B"), rng, 0) ' find match with column A If Not IsError(res) Then ' Match found If r < res Then .Cells(res, 2) = .Cells(r, "B") ' place entry in first row for this match .Cells(r, "B") = "" ' Clear this entry from column B End If End If Next r End With End Sub "vijaya" wrote: Hi, I have some data like this in excel sheet in two columns A and B. I want to match the items in Column B with column A and if it finds any match has to place that item in the same column but in the row where it was matched. A B In the diamond In the diamond In the diamond Trench Safety In the diamond The City The City The City The City Trench Safety Result should be like this: In the diamond In the diamond In the diamond In the diamond The City The City The City The City Trench Safety Trench Safety It should place in the first cell only though it has multiple matching cells in the left column as shown above. Can i do this with some match or sort ..How can i do this |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match and sort
Hi,
The following approach is a workaround. It is not elegant enough to move the items of Column B to appropriate rows in the SAME sheet (you would need a vba code for that). The formula suggested here assumes that each group of items (in Column A)occurs only once as a block (i.e., "In the diamond" will not appear again somewhere down, say below "The City"). Furthermore, the data should not start at Row 1 (you can have column headers in Row 1). Let's suppose that your data are in Sheet1 (say in A2:A201 and B2:B11). In another worksheet, say Sheet 2, Copy column A of Sheet 1. In B2 (of Sheet2), enter the following formula and drag down the formula to B201. =IF(OR(A2=A1,A2="",ISERROR(MATCH(A2,Sheet1!$B$2:$B $11,0))),"",A2) Sheet2 is still linked to Sheet1. So any change in Sheet1 will reflect in Sheet2 as well. If you want to make Sheet2 independent, select Column B of Sheet2 -- Copy -- Paste Special - Values. After this you could even delete Sheet1. Regards, B. R. Ramachandran "vijaya" wrote: Hi, I have some data like this in excel sheet in two columns A and B. I want to match the items in Column B with column A and if it finds any match has to place that item in the same column but in the row where it was matched. A B In the diamond In the diamond In the diamond Trench Safety In the diamond The City The City The City The City Trench Safety Result should be like this: In the diamond In the diamond In the diamond In the diamond The City The City The City The City Trench Safety Trench Safety It should place in the first cell only though it has multiple matching cells in the left column as shown above. Can i do this with some match or sort ..How can i do this |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match and sort two different tables | Excel Worksheet Functions | |||
sort & match | Excel Worksheet Functions | |||
Match and sort | Excel Worksheet Functions | |||
How to match and sort | Excel Discussion (Misc queries) | |||
Sum if or some sort of match formula | Excel Worksheet Functions |