I have an excel sheet that has a number of entities that are defined by their 'Job Number'. However, I need multiple rows per job meaning that the column ends up having duplicates, such as in

this picture

Is there a way to combine each group of identical job number so that they are a 'single' row? is what the preferred layout is.

Preferred Layout

Note: Each job is guaranteed to be 3 rows, so it will always follow the same pattern. Is there a way to do this?

  • You can use VBA to merge the cells and center the contents. – Ron Rosenfeld Jul 23 '18 at 1:55
  • Well the reason I want to use a table for this is so that the formulas in other columns can be flood filled through out. I'm not 100% sure how I would go about setting that up in VBA – Maxim Srour Jul 23 '18 at 2:45
  • You cannot merge cells in a Table. Does it have to be one? – Daniel B Jul 23 '18 at 10:20
  • Could you present the desired view in a separate tabular pivot table? – Mark Fitzgerald Jul 23 '18 at 10:53
  • Ideally it would be a table, since there are calculated columns throughout, and given that just adding a 'row' to the end wouldn't extend the formula, it makes it difficult for the users of the excel sheet to work with it. I am essentially trying to simplify the sheet so that they can't make as many mistakes, and to reduce data redundancy. @MarkFitzgerald there is no way for this to be a pivot table. I have experimented with the use of them, but it just wouldn't work – Maxim Srour Jul 23 '18 at 22:20

Try this code:

Sub Test() TitleRow = 1 'if title contain more than one row, change the value 1 to the actual number of rows i = 0 Application.DisplayAlerts = False Do Set StartCell = ActiveSheet.Range("A" & (TitleRow + 3 * i + 1)) Set EndCell = ActiveSheet.Range("A" & (TitleRow + 3 * i + 3)) With ActiveSheet.Range(StartCell, EndCell) .Merge .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With i = i + 1 Loop Until Range("A" & (TitleRow + 3 * i) + 1) = "" Application.DisplayAlerts = True End Sub

enter image description here

|improve this answer|||||

It can be done easily using macro or VBA, Edit below code in VBA as per your requirement.

Range("A1:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection .HorizontalAlignment = xlCenter

|improve this answer|||||
  • Range("A1:A3") is defining the range as an absolute. Since I would need to do it for every single group of rows, how would I go about that? – Maxim Srour Jul 23 '18 at 5:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.