Ever had a table that has multiple rows of data associated with the same primary entry (say in column-1) and wanted to format your table so it only shows a single row heading entry for that value, rather than repeating it for each row?
Of course you can merge your rows in a row to achieve this visually, bu that converts that part of your table back to a regular range and no longer a part of your table, which is not ideal.
You can use conditional formatting to achieve this. The following rules assume that your column to group/merge/consolidate is in column-A but you can apply the same type of formatting rules to many columns.
For simplicity, I'm also assuming the default table design style of alternate blue striping with a heading row on row 1 table ends on row-10.
1. Duplicate the default table style to make a custom style, applying it to your table;
2. Modify the custom style to:
a) Change the First Column Format, Fill Colour to White, or whatever colour you like, really
3. Check the First Column checkbox on the design ribbon
3. Apply the following conditional formatting rules to your entire first column
Exclude the quotes on the formulas.
Leave all borders not explicitly stated as grey.
The $ prefixes for absolute reference are important to get right, as shown.
Formula "=COUNTIF(A$2:A10,A2)=1" - Format Borders = Left, top & right
Formula "=NOT(COUNTIF(A$2:A10,A2)=ROUND(COUNTIF($A$2:$A$10,A2)/2,0))" - Format borders left & right; Numbers-Custom = ";;;" (no quotes)
Formula "=NOT(COUNTIF(A$2:A10,A2)=ROUND(COUNTIF($A$2:$A$10,A2)/2,0))" - Format borders left & right; Font = Bold
Formula "=COUNTIF(A$2:A2,A2)=ROUND(COUNTIF($A$2:$A$10,A2),0) - Format borders = left, bottom, right
If you tend to hide rows on your table and want this to work with just the visible rows, add a column to your table, (Let's assume that will be Column-B) with the formula
=SUBTOTAL(103,[@[Column-A]]) replacing Column-A with whatever your column-A heading might be.
Then use the following formulas replacing those above:
"=SUMIF(A$2:A2,A2,$B$2:$B$10)=1"
"=NOT(SUMIF(A$2:A3,A2,$B$2:$B$10)=ROUND(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10=1))/2,0))"
"=SUMIF(A$2:A2,A2,$B$2:$B$10)=ROUND(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10=1))/2,0)"
"=SUMIF(A$2:A2,A2,$B$2:$B$10)=ROUND(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10=1)),0)"
As column-B is a calculation column for formatting only, you can happily hide this column.
I have been playing around with including a user defined function in VBA to return an array with 1s for those rows/cells actually visible in the active window and including the returned range in the formula, but while it sort of works, it's a bit sluggish and doesn't seem to auto-calc every time the sheet is scrolled in the window (may need a scroll even routine to force a recalculation).
Regards
Max
Of course you can merge your rows in a row to achieve this visually, bu that converts that part of your table back to a regular range and no longer a part of your table, which is not ideal.
You can use conditional formatting to achieve this. The following rules assume that your column to group/merge/consolidate is in column-A but you can apply the same type of formatting rules to many columns.
For simplicity, I'm also assuming the default table design style of alternate blue striping with a heading row on row 1 table ends on row-10.
1. Duplicate the default table style to make a custom style, applying it to your table;
2. Modify the custom style to:
a) Change the First Column Format, Fill Colour to White, or whatever colour you like, really
3. Check the First Column checkbox on the design ribbon
3. Apply the following conditional formatting rules to your entire first column
Exclude the quotes on the formulas.
Leave all borders not explicitly stated as grey.
The $ prefixes for absolute reference are important to get right, as shown.
Formula "=COUNTIF(A$2:A10,A2)=1" - Format Borders = Left, top & right
Formula "=NOT(COUNTIF(A$2:A10,A2)=ROUND(COUNTIF($A$2:$A$10,A2)/2,0))" - Format borders left & right; Numbers-Custom = ";;;" (no quotes)
Formula "=NOT(COUNTIF(A$2:A10,A2)=ROUND(COUNTIF($A$2:$A$10,A2)/2,0))" - Format borders left & right; Font = Bold
Formula "=COUNTIF(A$2:A2,A2)=ROUND(COUNTIF($A$2:$A$10,A2),0) - Format borders = left, bottom, right
If you tend to hide rows on your table and want this to work with just the visible rows, add a column to your table, (Let's assume that will be Column-B) with the formula
=SUBTOTAL(103,[@[Column-A]]) replacing Column-A with whatever your column-A heading might be.
Then use the following formulas replacing those above:
"=SUMIF(A$2:A2,A2,$B$2:$B$10)=1"
"=NOT(SUMIF(A$2:A3,A2,$B$2:$B$10)=ROUND(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10=1))/2,0))"
"=SUMIF(A$2:A2,A2,$B$2:$B$10)=ROUND(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10=1))/2,0)"
"=SUMIF(A$2:A2,A2,$B$2:$B$10)=ROUND(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10=1)),0)"
As column-B is a calculation column for formatting only, you can happily hide this column.
I have been playing around with including a user defined function in VBA to return an array with 1s for those rows/cells actually visible in the active window and including the returned range in the formula, but while it sort of works, it's a bit sluggish and doesn't seem to auto-calc every time the sheet is scrolled in the window (may need a scroll even routine to force a recalculation).
Regards
Max
