Help with OFFSET

[EvansB2]

Hi there,

I have a table below that consists of a list of projects in column A and on each row values are input representing year on year costs. I would like to put Table 1 in a list format as shown in Table 2. These are examples and the reality is that I have a lot of data to re-format and I'm thinking that an OFFSET formula would assist in this?

Table 1

	A	B	C	D	E	F
1	Project	Year 1	Year 2	Year 3	Year 4	Year 5
2	A	£500	£100	£300	£0	£500
3	B	£0	£200	£300	£600	£0
4	C	£0	£0	£450	£450	£800
5
6
7

<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" span="6"> <tbody> </tbody>

Table 2

	A	B	C	D	E	F
1	Project	Year	Value
2	A	1	£500
3	A	2	£100
4	A	3	£300
5	A	4	£0
6	A	5	£500
7	B	1	£0

<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" span="6"> <tbody> </tbody>

Any help would be appreciated.

Regards

Ben

 

[steve the fish]

Heres one way. I put Table 1 in A1:F4 of Sheet1 and the headers of Table 2 in A1:C1 of Sheet2. Then in Sheet2 A2,B2,C2 i placed these 3 formulas:

=INDEX(Sheet1!$A:$A,CEILING((ROW()-1)/(MATCH("zzz",Sheet1!$1:$1)-1),1)+1)
=MOD(ROW()+MATCH("zzz",Sheet1!$1:$1)-3,(MATCH("zzz",Sheet1!$1:$1)-1))+1
=INDEX(Sheet1!$A$1:$Z$1000,MATCH(A2,Sheet1!$A$1:$A$1000,0),MATCH("Year "&B2,Sheet1!$A$1:$Z$1,0))

They work for 1000 rows and 26 columns in Table 1. Drag down until the results start to error.

 

[EvansB2]

Thanks for quick reply Steve,

Can you tell me what the "zzz" means?

Regards

Ben

 

[steve the fish]

Its just to find the last column used in the header row.

 

[EvansB2]

Ok.

I have applied formulas and in Sheet2 columns A & B work great, however in col C it returs "#N/A". I have double checked and nothing is amiss.

Can you think of any obvious errors I may have made?

Regards

Ben

 

[sandy666]

another option is PowerQuery
(PQ add-in - Excel 2010/2013, built-in - Excel 2016 and higher)

Source							Result
Project	Year 1	Year 2	Year 3	Year 4	Year 5		Project	Year	Value
A	500​	100​	300​	0​	500​		A	1	500​
B	0​	200​	300​	600​	0​		A	2	100​
C	0​	0​	450​	450​	800​		A	3	300​
							A	4	0​
							A	5	500​
							B	1	0​
							B	2	200​
							B	3	300​
							B	4	600​
							B	5	0​
							C	1	0​
							C	2	0​
							C	3	450​
							C	4	450​
							C	5	800​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Project"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(Table.ReplaceValue(#"Unpivoted Other Columns","Year ","",Replacer.ReplaceText,{"Attribute"}),{{"Attribute", "Year"}})
in
    #"Renamed Columns"[/SIZE]

 

[steve the fish]

Ok.

I have applied formulas and in Sheet2 columns A & B work great, however in col C it returs "#N/A". I have double checked and nothing is amiss.

Can you think of any obvious errors I may have made?




Regards

Ben

What does this produce?


=MATCH("Year "&B2,Sheet1!$A$1:$Z$1,0)

 

[EvansB2]

Steve, my mistake, I didn't have a space between thesecond exclamation mark, i.e. "Year".

All sorted. Thanks again.

Ben