can there is any function in excel modify table a to table b plz suggest.

table a table b
no dept no dept
1 a 1 a,c,d
1 c -------- 2 e,f
1 d
2 e
2 f

thanks

This requires a VBA macro.

"ITian2009" wrote:

can there is any function in excel modify table a to table b plz suggest.

table a table b
no dept no dept
1 a 1 a,c,d
1 c -------- 2 e,f
1 d
2 e
2 f

thanks

You need a macro for that! Try this one:

Sub test()
Dim uniqnos As Range, nos As Range, currno As Range, currow As Range
Worksheets("tablea").Activate
Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Destination:=Worksheets("tableb").Range("A1")
Set uniqnos = Intersect(Selection, Columns("A"), Rows("2:" & Rows.Count))
uniqnos.Select
ActiveSheet.ShowAllData
Columns("A:A").Select
Selection.AutoFilter
For Each nos In uniqnos
Selection.AutoFilter Field:=1, Criteria1:=nos.Value
Set currno = ActiveSheet.AutoFilter.Range.Columns(1)
Set currno = currno.Offset(1, 0).Resize(currno.Rows.Count - 1)
Set currno = currno.SpecialCells(xlCellTypeVisible)
deptvalue = ""
For Each currow In currno
deptvalue = deptvalue & Range("B" & currow.Row) & ","
Next currow
deptvalue = Left(deptvalue, Len(deptvalue) - 1)
currnopos = WorksheetFunction.Match(nos,
Worksheets("tableb").Columns("A"), 0)
Worksheets("tableb").Range("B" & currnopos).Value = deptvalue
Next nos
End Sub


You must have a sheet named "tablea" and another one named "tableb" before
you run the macro!

Regards,
Stefi

€žITian2009€� ezt Ã*rta:

can there is any function in excel modify table a to table b plz suggest.

table a table b
no dept no dept
1 a 1 a,c,d
1 c -------- 2 e,f
1 d
2 e
2 f

thanks

You don't need a macro for that.
Excel 2007, Pivot Table, formulas
http://www.mediafire.com/file/macmnzdefrz/Concat2.xlsx

Thanks, I'll keep it in mind when upgrading to Excel2007.
Stefi


€žHerbert Seidenberg€� ezt Ã*rta:

You don't need a macro for that.
Excel 2007, Pivot Table, formulas
http://www.mediafire.com/file/macmnzdefrz/Concat2.xlsx