Tricky, extract change number from header

[motilulla]

Using Excel 2010
Hello,

I got header with numbers 1 to 14 in range C2:P2, example data text same/change in range C7:P50 (Real data range is C7:P6520)

I need to extract text change number from it corresponding header C2:P2 and result place in the columns S and AF,

Note: starting change header number has to be result below the below the same header number…for example row 7 first & second change start header is 2 so it has to be placed in range S and AF, starting below the header 2, same logic will be applied for all database (if change start 1, result below 1, if change start 2, result below 2, if change start 3, result below 3 and so on…)

Please advise a VBA solution, also formula if it is not complicate.

MrExcel Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2			1	2	3	4	5	6	7	8	9	10	11	12	13	14
3
4																			1	2	3	4	5	6	7	8	9	10	11	12	13	14
5			SC1	SC2	SC3	SC4	SC5	SC6	SC7	SC8	SC9	SC10	SC11	SC12	SC13	SC14			Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha
6
7			Same	Change	Change	Same	Same	Change	Change	Same	Same	Change	Change	Change	Change	Same				2	3	6	7	10	11	12	13
8			Same	Change	Change	Same	Same	Change	Change	Same	Same	Change	Change	Change	Change	Same				2	3	6	7	10	11	12	13
9			Same	Same	Same	Change	Same	Same	Same	Same	Change	Same	Change	Same	Change	Same						4	9	11	13
10			Change	Change	Same	Same	Change	Same	Same	Same	Same	Same	Same	Change	Same	Same			1	2	5	12
11			Change	Same	Change	Same	Change	Change	Change	Same	Change	Change	Change	Change	Same	Change			1	3	5	6	7	9	10	11	12	14
12			Same	Change	Change	Same	Change	Change	Same	Change	Change	Same	Change	Same	Change	Change				2	3	5	6	8	9	11	13	14
13			Change	Change	Change	Same	Same	Change	Same	Change	Same	Change	Change	Same	Same	Same
14			Same	Change	Change	Same	Change	Same	Change	Same	Change	Same	Same	Change	Same	Change
15			Same	Change	Change	Change	Change	Change	Change	Same	Same	Change	Same	Same	Same	Change
16			Change	Change	Same	Change	Same	Change	Change	Change	Same	Same	Same	Change	Same	Change
17			Change	Same	Same	Change	Change	Change	Same	Change	Change	Same	Change	Same	Same	Change
18			Same	Same	Change	Same	Change	Change	Change	Same	Same	Change	Same	Change	Change	Same
19			Change	Same	Same	Change	Change	Change	Same	Same	Same	Same	Change	Change	Change	Change
20			Change	Same	Same	Change	Same	Same	Change	Change	Same	Same	Change	Same	Same	Same
21			Same	Same	Same	Change	Same	Same	Change	Same	Same	Same	Change	Same	Same	Change
22			Change	Same	Change	Change	Change	Change	Same	Change	Same	Same	Same	Same	Change	Change
23			Change	Same	Change	Same	Same	Same	Same	Same	Change	Change	Change	Change	Change	Change
24			Change	Same	Same	Change	Same	Change	Change	Same	Change	Change	Change	Change	Change	Change
25			Change	Same	Same	Change	Change	Same	Change	Same	Same	Change	Same	Same	Same	Change
26			Change	Same	Same	Same	Change	Change	Same	Same	Change	Change	Same	Same	Change	Same
27			Change	Change	Change	Change	Change	Same	Same	Change	Change	Change	Same	Change	Change	Same
28			Same	Change	Change	Same	Change	Same	Change	Same	Change	Change	Same	Same	Same	Change
29			Same	Same	Same	Same	Change	Change	Same	Change	Same	Change	Change	Change	Change	Same
30			Same	Same	Same	Same	Change	Same	Change	Change	Change	Same	Same	Same	Change	Same
31			Same	Same	Same	Same	Same	Same	Same	Change	Same	Same	Change	Same	Same	Change
32			Same	Change	Same	Change	Change	Same	Same	Change	Change	Change	Same	Change	Change	Same
33			Change	Same	Same	Same	Same	Same	Change	Same	Change	Same	Change	Change	Same	Same
34			Change	Same	Same	Change	Change	Same	Change	Change	Same	Change	Same	Same	Same	Same
35			Change	Change	Same	Change	Change	Change	Same	Change	Change	Same	Change	Change	Same	Same
36			Change	Change	Change	Change	Change	Change	Change	Same	Change	Same	Change	Change	Same	Change
37			Change	Change	Change	Change	Same	Same	Change	Change	Same	Change	Same	Change	Same	Same
38			Same	Change	Same	Same	Change	Change	Same	Same	Same	Same	Same	Change	Change	Change
39			Same	Change	Change	Same	Same	Change	Change	Change	Change	Same	Same	Same	Same	Change
40			Change	Same	Change	Change	Same	Same	Same	Same	Change	Same	Change	Change	Change	Change
41			Change	Change	Change	Change	Change	Change	Change	Change	Same	Change	Change	Same	Change	Same
42			Same	Same	Same	Same	Change	Change	Change	Same	Same	Change	Change	Same	Change	Same
43			Same	Same	Change	Change	Same	Same	Same	Same	Same	Change	Same	Change	Same	Same
44			Same	Same	Same	Same	Change	Same	Change	Same	Change	Same	Change	Same	Change	Change
45			Change	Change	Change	Change	Same	Change	Same	Same	Change	Same	Change	Change	Same	Change
46			Same	Same	Change	Same	Change	Same	Change	Same	Same	Same	Same	Change	Change	Same
47			Change	Change	Same	Same	Change	Change	Change	Change	Same	Same	Same	Same	Same	Same
48			Change	Same	Same	Change	Change	Same	Change	Change	Same	Same	Same	Same	Change	Same
49			Change	Change	Change	Change	Same	Same	Same	Same	Change	Change	Change	Change	Change	Same
50			Same	Same	Change	Same	Change	Change	Change	Same	Change	Change	Change	Change	Change	Change
51
52
Sheet9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:P5009	Cell Value	="Same"	text	NO

Regards,
Moti

 

[KRice]

Here is a formula-based solution:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2			1	2	3	4	5	6	7	8	9	10	11	12	13	14
3
4																			1	2	3	4	5	6	7	8	9	10	11	12	13	14
5			SC1	SC2	SC3	SC4	SC5	SC6	SC7	SC8	SC9	SC10	SC11	SC12	SC13	SC14			Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha	Cha
6
7			Same	Change	Change	Same	Same	Change	Change	Same	Same	Change	Change	Change	Change	Same				2	3	6	7	10	11	12	13
8			Same	Change	Change	Same	Same	Change	Change	Same	Same	Change	Change	Change	Change	Same				2	3	6	7	10	11	12	13
9			Same	Same	Same	Change	Same	Same	Same	Same	Change	Same	Change	Same	Change	Same						4	9	11	13
10			Change	Change	Same	Same	Change	Same	Same	Same	Same	Same	Same	Change	Same	Same			1	2	5	12
11			Change	Same	Change	Same	Change	Change	Change	Same	Change	Change	Change	Change	Same	Change			1	3	5	6	7	9	10	11	12	14
12			Same	Change	Change	Same	Change	Change	Same	Change	Change	Same	Change	Same	Change	Change				2	3	5	6	8	9	11	13	14
13			Change	Change	Change	Same	Same	Change	Same	Change	Same	Change	Change	Same	Same	Same			1	2	3	6	8	10	11
14			Same	Change	Change	Same	Change	Same	Change	Same	Change	Same	Same	Change	Same	Change				2	3	5	7	9	12	14
15			Same	Change	Change	Change	Change	Change	Change	Same	Same	Change	Same	Same	Same	Change				2	3	4	5	6	7	10	14
16			Change	Change	Same	Change	Same	Change	Change	Change	Same	Same	Same	Change	Same	Change			1	2	4	6	7	8	12	14
17			Change	Same	Same	Change	Change	Change	Same	Change	Change	Same	Change	Same	Same	Change			1	4	5	6	8	9	11	14
18			Same	Same	Change	Same	Change	Change	Change	Same	Same	Change	Same	Change	Change	Same					3	5	6	7	10	12	13
19			Change	Same	Same	Change	Change	Change	Same	Same	Same	Same	Change	Change	Change	Change			1	4	5	6	11	12	13	14
Sheet1

Cell Formulas
Range	Formula
S7:AF19	S7	=IF(AND(COLUMNS($S7:S7)>=MATCH("Change",$C7:$P7,0),(COLUMNS($S7:S7)<=MATCH("Change",$C7:$P7,0)+COUNTIF($C7:$P7,"Change")-1)),AGGREGATE(15,6,$C$2:$P$2/($C7:$P7="Change"),COLUMNS($S7:S7)-MATCH("Change",$C7:$P7,0)+1),"")

 

[motilulla]

Cell Formulas
Range	Formula
S7:AF19	S7	=IF(AND(COLUMNS($S7:S7)>=MATCH("Change",$C7:$P7,0),(COLUMNS($S7:S7)<=MATCH("Change",$C7:$P7,0)+COUNTIF($C7:$P7,"Change")-1)),AGGREGATE(15,6,$C$2:$P$2/($C7:$P7="Change"),COLUMNS($S7:S7)-MATCH("Change",$C7:$P7,0)+1),"")

KRice, congratulation formula worked spot-on! I appreciate your time and help

Have a good day and good luck!

Kind regards,
Moti

 

[KRice]

Happy to help, and thanks for the feedback!