Thus we have a grid of 248 cells Each employee enters their surname/Work Ref into any of the cells where they would be willing to work I would then like to consolidate this Text information into a single sheet allowing me to see staff availability or holes in the schedule.I do not have any code to post at this time, I have tried using a For Each construct i.e. Range("B2:o32") If CL "" Then This finds the entry finds the entry, but I can't find a way to append sheet1 with the data as I don't know how to find the same location. Select the data range that you want to use, and then click Kutools Advanced Combine Rows to enable the Advanced Combine Rows dialog box. In the Advanced Combine Rows dialog box, click the column name that you want to combine other data based on, and click Primary Key, see screenshot: 3. See screenshots: Sometimes, you want to combine the rows based on duplicate values in another column, the Advanced Combine Rows of Kutools for Excel also can do a favor for you, please do as follows: 1. Note: If you want to use this code, you’d better make a copy of the file to avoid destroying the data, and this code is only applied to two columns. And then select the column name you want to sum the values and click Calculate Sum or other calculations as you need. Click Ok to close dialog, then you can see the duplicates are combined and the corresponding data in another column are added up together.
My issue is collection rather than interpretation, I am hoping to end up with a single sheet of 248 Cells and any single cell may contain any number of surnames, well up to 50 anyway. Value End If Next End Sub This seems to do the trick but I now need it to work through the other sheets in the book, I think I'll post a new question Cheers Mike54 You can run a two-level loop, with one variable going through the columns and another one going through the rows.I need to send out a worksheet to a group of about 50 people the worksheet shows the days of the chosen month for example Dec 2014, one Row for each Day.Across the top are the eight different shifts to , to etc.The following article explains these two processes.I have an xls file with 8 worksheets of names and other information for students.The following VBA code also can help you consolidate duplicate rows in the worksheet, but at the same time, the original data will be destroyed, you need to backup a copy of the data. Hold down the ALT F11 keys, then it opens the Microsoft Visual Basic for Applications window. Click Insert Sub Combine Rows() 'Update 20130829 Dim Work Rng As Range Dim Dic As Variant Dim arr As Variant On Error Resume Next x Title Id = "Kutoolsfor Excel" Set Work Rng = Application. And then click another column name that you want to combine the data, and click Combine to choose a separator to separate your combined values, see screenshot: 4.Could I use the Address Function to identify the CL and use that to replicate the location on Sheet1?Or would I be better to construct a routine that uses the Cells property to step through the 248 cells, at least that way I would know the location of each cell (R, C) I tried using the built in Consolidation feature but it's not designed for Text.I don't think I have ever used the Match function so I'll need to instigate that. If the dimensions of the matrix in the summary match the dimensions of all other sheets, you can use the same coordinates to write on the "summary" matrix.As all the sheets are identical, if Sheet2.range("C4") contained the name "Jones" and Sheet3.range("C4") contained the name "Smith" Then Sheet1. Lets say you have cell A1, where the surname "Jones" is. something like this should get you started: "SUMMARY" Then For i = 1 To 8 'shifts.