Need help with formula!

[jyroxxor]

Hi all,

I have existing data on the left. I need the outcome shows on right? Not sure how to derive it. Can you guys pls help me out? Thanks!

	A	B	C	D	E	F					A	B	C	D	E
1	Date	GL	Description	SouthAsia	NorthAsia	Europe				1	Location	SouthAsia
2	Jan18	1111	Sales	2000	3000	4000				2
3	Jan18	1112	Exp	1000	1500	2000		to		3	GL	Description	Jan18	Feb18
4	Jan18	1113	Net Sales	1000	1500	2000			4	1111	Sales	2000	3000
5	Feb18	1111	Sales	3000	4000	5000				5	1112	Exp	1000	1500
6	Feb18	1112	Exp	1500	2000	3500				6	1113	Net Sales	1000	1500
7	Feb18	1113	Net Sales	1500	2000	1500

<colgroup><col><col span="2"><col><col span="3"><col><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

You want the numeric values in the order they occur in the data, not in any particular order, right?

 

[jyroxxor]

You want the numeric values in the order they occur in the data, not in any particular order, right?

Yeah

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	K	L	M	N
1	Date	GL	Description	SouthAsia	NorthAsia	Europe	Location	SouthAsia
2	Jan18	1111	Sales	2000	3000	4000
3	Jan18	1112	Exp	1000	1500	2000	GL	Description	Jan18	Feb18
4	Jan18	1113	Net Sales	1000	1500	2000	1111	Sales	2000	3000
5	Feb18	1111	Sales	3000	4000	5000	1112	Exp	1000	1500
6	Feb18	1112	Exp	1500	2000	3500	1113	Net Sales	1000	1500
7	Feb18	1113	Net Sales	1500	2000	1500
Sheet1

In K4 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$7,SMALL(IF(FREQUENCY(IF(ISNUMBER($B$2:$B$7),MATCH($B$2:$B$7,$B$2:$B$7,0)),ROW($A$2:$F$7)-ROW(INDEX($A$2:$F$7,1,1))+1),ROW($A$2:$F$7)-ROW(INDEX($A$2:$F$7,1,1))+1),ROWS($K$4:K4))),"")

In L4 just enter and copy down:

=IF($K4="","",VLOOKUP($K4,$B$2:$C$7,2,0))

In M4 control+shift+enter, not just enter, copy across to N4, and down:

=IF($K4="","",INDEX($A$2:$F$7,SMALL(IF($A$2:$A$7=M$3,ROW($A$2:$F$7)-ROW(INDEX($A$2:$F$7,1,1))+1),ROWS(M$4:M4)),MATCH($L$1,$A$1:$F$1,0)))

 

[jyroxxor]

Thanks Aladin, but dont quite get the formula. However, can you help me solve the following, similar to above but much more complicated

 

[jyroxxor]

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Raw Data								Report Wanted
2
3	Date	GL	Description	SouthAsia	NorthAsia	Europe				SouthAsia	Jan18	Feb18	Mar18
4	Jan18	1111	Sales	2000	3000	4000				Sales	2000	3000	3500
5	Jan18	1112	S&D	800	1500	2200			GL1112 + GL1113	Total Exp	1000	1250	1400
6	Jan18	1113	G&A	200	300	500				Net Profit	1000	1750	2100
7	Jan18	1114	Net Profit	1000	1200	1300
8	Feb18	1111	Sales	3000	4000	5000
9	Feb18	1112	S&D	1000	2300	2500
10	Feb18	1113	G&A	250	500	800
11	Feb18	1114	Net Profit	1750	1200	1700
12	Mar18	1111	Sales	3500	4500	5500
13	Mar18	1112	S&D	1200	1500	1800
14	Mar18	1113	G&A	200	300	300
15	Mar18	1114	Net Profit	2100	2700	3400
16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula K6 =K4-K5 L6 =L4-L5 M6 =M4-M5 D7 =D4-D5-D6 E7 =E4-E5-E6 F7 =F4-F5-F6 D11 =D8-D9-D10 E11 =E8-E9-E10 F11 =F8-F9-F10 D15 =D12-D13-D14 E15 =E12-E13-E14 F15 =F12-F13-F14 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

May I know what is the formula in K4 and K5?

 

[Aladin Akyurek]

Thanks Aladin, but dont quite get the formula. However, can you help me solve the following, similar to above but much more complicated

See the workbook which implements the set up: https://www.dropbox.com/s/xvaqvh6am3ycblh/jyroxxor re-representation.xlsx?dl=0