is there such formula to take date from different cells and put into another cell

[karoolius]

is there such formula to take date from different cells and put into another cell?
1. is the data
2. is the result i need.
So i need to take year, quarter number and the number from different quarter to another cell.

[sandy666]

just unpivot data

are you still using XL2007 ?

[etaf]

yes
you can use a grid lookup and split the text

so
=INDEX($B$2:$E$3,MATCH(LEFT($G2,4)*1,$A2:$A3,0),MATCH(RIGHT($G2,2),B$1:E$1,0))


A sample sheet would help here

The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[sandy666]

solution for XL2010/2013 (require Power Query add-in) , XL2016 and up - Power Query is build-in

Year	Q1	Q2	Q3	Q4		YQ	Value
1994	5	4	4	3		1994-Q1	5
1995	54	6	1	2		1994-Q2	4
1996	4	2	5	6		1994-Q3	4
1997	1	3	8	5		1994-Q4	3
						1995-Q1	54
						1995-Q2	6
						1995-Q3	1
						1995-Q4	2
						1996-Q1	4
						1996-Q2	2
						1996-Q3	5
						1996-Q4	6
						1997-Q1	1
						1997-Q2	3
						1997-Q3	8
						1997-Q4	5

Please Login or Register  to view this content.

[Czeslaw]

Formula

Please Login or Register  to view this content.

[karoolius]

Thanks.
Do you have any idea how can i make formula for excel to take every quarter of the year into L columnAttachment 824388?

[etaf]

isn't that what the formula is doing

attachment not opening

[karoolius]

Attachment 824389 i will try to attach it again

[etaf]

are you following the yellow banner - or my copy of how to attach?

[karoolius]

REALGDP.xlsx i try to attach the file

[etaf]

How are you attaching files .......
via manage attachments - do not use the paperclip

[karoolius]

okay i tried other way

[sandy666]

you didn't answer
are you still on XL2007?

[JohnTopley]

In L2

=IFERROR(INDEX($F$2:$F$30,INT((ROWS($1:1)-1)/4)+1)& "-" & INDEX($G$1:$J$1,MOD((ROWS($1:1)-1),4)+1),"")

in M2

=IFERROR(INDEX($G$2:$J$30,MATCH(--MID($L2,1,4),F$2:F$30,0),MATCH(RIGHT($L2,2),G$1:J$1,0)),"")