Transpose Values

Is this what you want? Not sure if you can achieve what you want via formula though.

Please Login or Register  to view this content.

hpatel517,

Quick question. Row 11 column I of expected output shows EF2. Did you intend for that be in row 10 column J? That would appear to fit the pattern.

I assumed yes.

If you would like a formula solution then with a helper column:
In D3 and filled down this formula.

Formula:

Please Login or Register  to view this content.

Then entered in G3:H3 and filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

Then array enter this formula in I3 fill across column K and down until you get blanks.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	As Is						To Be
2	Facility	Cost Center	SA				Facility	Cost Center	SA1	SA2	SA3
3	ABC	6000	AB12	1			ABC	6000	AB12	AB13	AB14
4	ABC	6000	AB13				ABC	6607	CD12
5	ABC	6000	AB14				DEF	6000	AB12	AB14
6	ABC	6607	CD12	4			DEF	6607	CD12	CD13
7	DEF	6000	AB12	5			DEF	8000	EF1
8	DEF	6000	AB14				GHI	6000	AB12
9	DEF	6607	CD12	7			GHI	6607	CD12
10	DEF	6607	CD13				GHI	8000	EF1	EF2
11	DEF	8000	EF1	9
12	GHI	6000	AB12	10
13	GHI	6607	CD12	11
14	GHI	8000	EF1	12
15	GHI	8000	EF2
16