Need help with formula!

jyroxxor

New Member
Joined
Feb 26, 2018
Messages
11
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]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Book1
ABCDEFKLMN
1DateGLDescriptionSouthAsiaNorthAsiaEuropeLocationSouthAsia
2Jan181111Sales200030004000
3Jan181112Exp100015002000GLDescriptionJan18Feb18
4Jan181113Net Sales1000150020001111Sales20003000
5Feb181111Sales3000400050001112Exp10001500
6Feb181112Exp1500200035001113Net Sales10001500
7Feb181113Net Sales150020001500
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)))
 
Upvote 0
Thanks Aladin, but dont quite get the formula. However, can you help me solve the following, similar to above but much more complicated :(
 
Upvote 0
Excel 2013/2016
ABCDEFGHIJKLM
Raw DataReport Wanted
Jan18SalesSales
Jan18S&DTotal Exp
Jan18G&ANet Profit
Jan18Net Profit
Feb18Sales
Feb18S&D
Feb18G&A
Feb18Net Profit
Mar18Sales
Mar18S&D
Mar18G&A
Mar18Net 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?
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top