Forestq

Active Member
Joined
May 9, 2010
Messages
482
Hi,

in one sheet I have data like:

A1: Audi B1:A3
A2: BMW B2:Z300
A3: Ford B3:Fiesta
A4: Audi B4: A4
A5: Ford B5: Mondeo

in second sheet I have unique value:
A1: Audi
A2: BMW
A3: Ford

Now I want to insert into second sheet in B1, B2... all value from sheet1 for Audi, Ford etc...:

B1: A3, A4
B2: Fiesta, Mondeo

I have just:

Code:
Application.ScreenUpdating = False
    
    'sheet with unique id
    Dim LIST_WS As Worksheet
    Set LIST_WS = Sheets("DATA")
    
    Dim x_rows As Long
    x_rows = LIST_WS.Range("B65536").End(xlUp).Row
    
    'sheet with informations
    Dim DATA_WS As Worksheet
    Set DATA_WS = Sheets("Issues")
    
    Dim x_rows_2 As Long
    x_rows_2 = DATA_WS.Range("B65536").End(xlUp).Row
    
    Dim Rng As String
    Dim sfullpath As String
    
    Rng = DATA_WS.Range("A2:C" & DATA_WS.Range("A" & Application.Rows.Count).End(xlUp).Row).Address
    
    LIST_WS.Range("D2").Select
    
    Dim i As Integer

        For i = 1 To x_rows


       Next i



could you please help me ?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forestq

Active Member
Joined
May 9, 2010
Messages
482
FYI

Code:
Application.ScreenUpdating = False
    
    'sheet with unique id
    Dim DATA_WS As Worksheet
    Set DATA_WS = Sheets("DATA")
    
    Dim x_rows As Long
    x_rows = DATA_WS.Range("B65536").End(xlUp).Row
    
    'sheet with informations
    Dim LIST_WS As Worksheet
    Set LIST_WS = Sheets("Actions")
    
    Dim x_rows_2 As Long
    x_rows_2 = LIST_WS.Range("B65536").End(xlUp).Row   
    
    Dim i, j As Long
    Dim k&, X&, tbl()
        
        'for UNIQ ID - sheet DATA
        For i = 1 To x_rows
                        
                    k = 0
                'for DATA in sheet ISSUE
                For j = 1 To x_rows_2
                
                    k = k + 1
                    
                     If DATA_WS.Cells(i, 2).Value = LIST_WS.Cells(k, 1) Then
                                              
                     X = X + 1
                     ReDim Preserve tbl(1 To X)
                     tbl(X) = LIST_WS.Cells(j, 3)
                     
                     End If
                                
                Next j
                
                DATA_WS.Cells(i, 4).Value = Join(tbl, ", ")
                Erase tbl
                X = 0
                
                
        Next i
    
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,265
Messages
5,769,128
Members
425,519
Latest member
jriddle19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top