Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
i need to get all matches in one column for one criteria
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
i need to get all matches in one column for one criteria
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... "PERANISH" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
i need to get all matches in one column for one criteria
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/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PERANISH" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add number before text that matches criteria | Excel Worksheet Functions | |||
Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
Looking up a maximum value that matches criteria. VLOOKUP? | Excel Worksheet Functions | |||
Selecting data that matches certain criteria in one column | Excel Discussion (Misc queries) | |||
SumIf Criteria Matches | Excel Worksheet Functions |