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!

[TABLE="width: 1068"]
<colgroup><col><col span="2"><col><col span="3"><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]GL[/TD]
[TD]Description[/TD]
[TD]SouthAsia[/TD]
[TD]NorthAsia[/TD]
[TD]Europe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Location[/TD]
[TD]SouthAsia[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jan18[/TD]
[TD]1111[/TD]
[TD]Sales[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD]4000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jan18[/TD]
[TD]1112[/TD]
[TD]Exp[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]to[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]GL[/TD]
[TD]Description[/TD]
[TD]Jan18[/TD]
[TD]Feb18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jan18[/TD]
[TD]1113[/TD]
[TD]Net Sales[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]1111[/TD]
[TD]Sales[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Feb18[/TD]
[TD]1111[/TD]
[TD]Sales[/TD]
[TD]3000[/TD]
[TD]4000[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]1112[/TD]
[TD]Exp[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Feb18[/TD]
[TD]1112[/TD]
[TD]Exp[/TD]
[TD]1500[/TD]
[TD]2000[/TD]
[TD]3500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]1113[/TD]
[TD]Net Sales[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Feb18[/TD]
[TD]1113[/TD]
[TD]Net Sales[/TD]
[TD]1500[/TD]
[TD]2000[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Raw Data	Report Wanted
Jan18	Sales	Sales
Jan18	S&D	Total Exp
Jan18	G&A	Net Profit
Jan18	Net Profit
Feb18	Sales
Feb18	S&D
Feb18	G&A
Feb18	Net Profit
Mar18	Sales
Mar18	S&D
Mar18	G&A
Mar18	Net Profit

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]GL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]SouthAsia[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]NorthAsia[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]Europe[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]SouthAsia[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]Jan18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]Feb18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]Mar18[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1111[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFCC]#FFFFCC[/URL] , align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFCC]#FFFFCC[/URL] , align: right"]2000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3000[/TD]
[TD="align: right"]3500[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1112[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]800[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]GL1112 + GL1113[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]1250[/TD]
[TD="align: right"]1400[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1113[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1750[/TD]
[TD="align: right"]2100[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1114[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1111[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1112[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]1000[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1113[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]250[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1114[/TD]

[TD="align: right"]1750[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1111[/TD]

[TD="align: right"]3500[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]1112[/TD]

[TD="align: right"]1200[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]1113[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1114[/TD]

[TD="align: right"]2100[/TD]
[TD="align: right"]2700[/TD]
[TD="align: right"]3400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K6[/TH]
[TD="align: left"]=K4-K5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L6[/TH]
[TD="align: left"]=L4-L5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M6[/TH]
[TD="align: left"]=M4-M5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=D4-D5-D6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=E4-E5-E6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=F4-F5-F6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=D8-D9-D10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E11[/TH]
[TD="align: left"]=E8-E9-E10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F11[/TH]
[TD="align: left"]=F8-F9-F10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]=D12-D13-D14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E15[/TH]
[TD="align: left"]=E12-E13-E14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F15[/TH]
[TD="align: left"]=F12-F13-F14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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