Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add number before text that matches criteria Carole Excel Worksheet Functions 3 May 8th 08 09:38 PM
Find something in column a then find if column B matches criteria Darrell_Sarrasin via OfficeKB.com Excel Discussion (Misc queries) 8 November 28th 07 10:40 PM
Looking up a maximum value that matches criteria. VLOOKUP? AndyCotgreave Excel Worksheet Functions 4 October 2nd 07 07:36 PM
Selecting data that matches certain criteria in one column Anthony Excel Discussion (Misc queries) 7 May 17th 07 12:48 PM
SumIf Criteria Matches Debbie Dies Excel Worksheet Functions 4 August 1st 05 11:18 PM


All times are GMT +1. The time now is 07:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"