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

  • Thread starter Thread starter PERANISH
  • Start date Start date
P

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
 
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...
 
Back
Top