i need to get all matches in one column for one criteria

[PERANISH]

Hello dears

I having datas as follows(Example)

Sheet-1 Sheet-2
Col-A col-A Col-B
AA-01 AA-01 ABC-01
AA-02 CC-01 ABC-02
CC-01 AA-01 ABC-03

I want result like below in Sheet-1
Col-A Col-B Col-c
AA-01 ABC-01 ABC-03
AA-02
CC-01 ABC-02

Request you help on this.

Awaiting your help please

Regards
Peranish

 

[Sheeloo]

Try this macro... It will copy values from Sheet2 to Sheet1...
1. If your sheetnames are different then change the names in the code.
2. If you have header rows then change 1 to 2 in both statements given below
in the macro...
for i = 1 to lastRow1
for j = 1 to lastRow2

3. To run the macro
Press ALT-F11 to open VB Editor
Choose Insert|Module
Paste the code below in the module
Press F5
Click OK

Here is the macro...
Sub copy()
Dim lastRow1, lastRow2 As Long
Dim i, j, k As Long
With Worksheets("Sheet1")
lastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With Worksheets("Sheet2")
lastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = 1 To lastRow1
k = 2
For j = 1 To lastRow2
If Worksheets("Sheet1").Cells(i, 1) = Worksheets("Sheet2").Cells(j, 1) Then
Worksheets("Sheet1").Cells(i, k) = Worksheets("Sheet2").Cells(j, 2)
k = k + 1
End If
Next j
Next i
MsgBox "Processing Complete"
End Sub

It can be made faster, with some changes, if your data in both sheets are
sorted...

 

[Ashish Mathur]

Hi,

You may want to refer to my my link on the following site to study a
suitable example

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com