Need to expand columns based on a value

[AkhilAnilkumar]

Hey guys,

I need help in expanding columns based on the value in one column.

So this is my sheet. Based on the value in column B, columns C-G must show up. For example, if value in column B is 2, column C-G must show up two times (in the same order) and so on.

 

[Jamie McMillan]

Hello,

Can you elaborate a little more?

*It is not the column width that you want to change.
* You are not inserting any new columns.

What would happen if B1 value was 3? Would Columns, C, D & E all show "Bay angle? (And Type of Violation' would disappear??

Jamie

 

[AkhilAnilkumar]

Hello,

Can you elaborate a little more?

*It is not the column width that you want to change.
* You are not inserting any new columns.

What would happen if B1 value was 3? Would Columns, C, D & E all show "Bay angle? (And Type of Violation' would disappear??

Jamie

So columns C-G must multiply depending on the value in column B. I have pasted an example of column B having a value 2 and how I need to excel sheet to be.

If column B is 1, then

Does this clear the question?

 

[Jamie McMillan]

Ok I will do it with formulas, and use row 100 for variables - It will work. Maybe 20 minutes.

 

[Jamie McMillan]

View attachment 56608

Done. I will just talk you through it.

 

[Jamie McMillan]

I used row 9 not row 100.

In cell H1 type =IF(H9>$B$2,"",C1) - make sure B2 has $s to hold the position. (Copy as far as you want)

In cell H9 type the number 2

In cell I9 type =H9 (Copy I9 to J9, k9, L9)

Select H9 to L9 (Copy as far as you want) NB It 'should make M9 to a number 3 - if not change every 5th cell to 3, 4, 5, ...

Went finished and it is OK. you can select all the cells in row 9 and move them to row 100, or anywhere else.

Hope that all works for you.

Jamie

 

[AkhilAnilkumar]

I used row 9 not row 100.

In cell H1 type =IF(H9>$B$2,"",C1) - make sure B2 has $s to hold the position. (Copy as far as you want)

In cell H9 type the number 2

In cell I9 type =H9 (Copy I9 to J9, k9, L9)

Select H9 to L9 (Copy as far as you want) NB It 'should make M9 to a number 3 - if not change every 5th cell to 3, 4, 5, ...

Went finished and it is OK. you can select all the cells in row 9 and move them to row 100, or anywhere else.

Hope that all works for you.

Jamie

I am not able to view the attachment and I am finding it difficult to understand what's happening

 

[Peter_SSs]

You could do it without a helper row like this.

• Put the 5 headings in C1:G1
• Select C1:G1 and drag right as far as you might ever need headings (I have just done 4 lots in the first mini-sheet below)
• Select from C1 to the end of all those repeated headings (V1 in my example)
• Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =COUNTIF($C1:C1,C1)>$B2 -> Format... -> Font tab -> Choose white in the 'Color:' drop-down -> OK -> OK

22 02 01.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Number	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual
Repeat headings

So, with a 4 in B2 you now see this

22 02 01.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Number	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual
2	4
Repeat headings

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:V1	Expression	=COUNTIF($C1:C1,C1)>$B2	text	NO

If we change B2 to 2:

22 02 01.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Number	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual	Bay length	Bay angle	Type	Required	Actual
2	2
Repeat headings

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:V1	Expression	=COUNTIF($C1:C1,C1)>$B2	text	NO

 

[Jamie McMillan]

Maybe that is better.