Sort data
Hi,
I have a set of data in 3 spreadsheets and needs to be combined into 1. Each spreadsheet, col B-E is staff details (emp no, name..etc). Data contains in column F onwards are different. ie: Sheet 1 : heading of Col F = Training A, Col G = Training B Sheet 2 : heading of Col F = Training C, Col G = Training D Sheet 3 : heading of Col F = Training E, Col G = Training F Now, How do I combine all the Training A - F to same row in a sheet? Appreciate if someone could give me a shortcut/formula to do it... Thanks a zil.. Jessie |
Sort data
Try this code
Sub combinesheets() Set Sumsht = Sheets.Add(after:=Sheets(Sheets.Count)) Sumsht.Name = "Summary" 'Copy sheet 1 to Summary sheet Sheets("Sheet1").Cells.Copy _ Destination:=Sumsht.Cells 'Get Last Row of data LastRow = Sumsht.Range("B" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 DestCol = 8 'col H 'add sheets 2 and 3 to summary sheet For ShtNum = 2 To 3 RowCount = 2 Set Sht = Sheets("Sheet" & ShtNum) Do While Sht.Range("B" & RowCount) < "" ID = Sht.Range("B" & RowCount) 'Check if ID Number exist in Summary Set c = Sumsht.Columns("B").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Sht.Range("A" & RowCount & ":E" & RowCount).Copy _ Sumsht.Range("A" & NewRow) Sht.Range("F" & RowCount & ":G" & RowCount).Copy _ Sumsht.Cells(NewRow, DestCol) NewRow = NewRow + 1 Else Sht.Range("F" & RowCount & ":G" & RowCount).Copy _ Sumsht.Cells(c.Row, DestCol) End If RowCount = RowCount + 1 Loop DestCol = DestCol + 2 Next ShtNum End Sub "Jessie" wrote: Hi, I have a set of data in 3 spreadsheets and needs to be combined into 1. Each spreadsheet, col B-E is staff details (emp no, name..etc). Data contains in column F onwards are different. ie: Sheet 1 : heading of Col F = Training A, Col G = Training B Sheet 2 : heading of Col F = Training C, Col G = Training D Sheet 3 : heading of Col F = Training E, Col G = Training F Now, How do I combine all the Training A - F to same row in a sheet? Appreciate if someone could give me a shortcut/formula to do it... Thanks a zil.. Jessie |
All times are GMT +1. The time now is 06:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com