Previous Topic Next Topic

Can you look into it Ivan?

Closed
Abhy - May 24, 2010 at 05:13 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 25, 2010 at 05:59 PM
Hello,

I have two excel sheets which have one column "name" in common. In one sheet, the values respective to that column are multiple and in another sheet they are unique. What I need to do is, I have to find out which values are referred for a single value and to copy them in another sheet respective to the columns value. Leeme show u in e.g.

Sheet1:

Code1 Value1
1 aa
2 bb
3 cc
4 mm



Sheet2:
Code1 Value2
1 dd
1 ee
1 ff
2 gg
2 hh
3 ii
3 jj
3 kk
4 ll

Now the output sheet I need is:
O/P sheet:

Code1 Value1 Value2
1 aa dd, ee, ff
2 bb gg, hh
3 cc ii, jj, kk
4 mm ll

Thanks for your help!

Abhy


1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 25, 2010 at 05:59 PM
Well I guess Ivan was not here or not had a chance to answer 



Sub AddData()
Dim lMaxRowsSheet2 As Long

    Sheets("Sheet2").Select
    lMaxRowsSheet2 = Cells(Rows.Count, "A").End(xlUp).Row
    
    
    Range(Cells(2, "C"), Cells(lMaxRowsSheet2, "C")).FormulaR1C1 = "=MATCH(RC1, Sheet1!C1:C1,0)"
    
    Sheets.Add
    ActiveSheet.Name = "Output"
    Cells(1, "A") = Sheets("Sheet1").Cells(1, "A")
    Cells(1, "B") = Sheets("Sheet1").Cells(1, "B")
    Cells(1, "C") = Sheets("Sheet2").Cells(1, "B")
    
   ' Sheets("Sheet2").Select
    For lrow = 2 To lMaxRowsSheet2
    
        If (IsError(Sheets("Sheet2").Cells(lrow, "C"))) Then
        
            'nothing
        Else
            
            Sheets("Output").Cells(Sheets("Sheet2").Cells(lrow, "C"), "A") = Sheets("Sheet1").Cells(Sheets("Sheet2").Cells(lrow, "C"), "A")
            Sheets("Output").Cells(Sheets("Sheet2").Cells(lrow, "C"), "B") = Sheets("Sheet1").Cells(Sheets("Sheet2").Cells(lrow, "C"), "B")
            
            If (Sheets("Output").Cells(Sheets("Sheet2").Cells(lrow, "C"), "C") = "") Then
                Sheets("Output").Cells(Sheets("Sheet2").Cells(lrow, "C"), "C") = Sheets("Sheet2").Cells(lrow, "B")
                
            Else
                Sheets("Output").Cells(Sheets("Sheet2").Cells(lrow, "C"), "C") = Sheets("Output").Cells(Sheets("Sheet2").Cells(lrow, "C"), "C") & ", " & Sheets("Sheet2").Cells(lrow, "B")
                
            End If
            
        End If
        
        
    Next
    
    Sheets("Sheet2").Select
    Range(Cells(1, "C"), Cells(lMaxRowsSheet2, "C")).Clear
    
End Sub