create date wise & ledger wise report by vba in excel

S

SACHIN_P

New Member
Joined
Aug 30, 2016
Messages
5
***

<colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody>
[TD="class: xl63, width: 64"]date[/TD]
[TD="class: xl63, width: 64"]doc no[/TD]
[TD="class: xl63, width: 64"]ledger[/TD]
[TD="class: xl63, width: 64"]narr[/TD]
[TD="class: xl63, width: 64"]amt[/TD]
[TD="class: xl63, width: 64"]balcne[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A001[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]XYZ[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl63, align: right"]1000[/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A002[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]RTY[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]3000[/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A003[/TD]
[TD="class: xl63"]CDE[/TD]
[TD="class: xl63"]EER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]3500[/TD]

[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]A004[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]EWR[/TD]
[TD="class: xl63, align: right"]210[/TD]
[TD="class: xl63, align: right"]3710[/TD]

[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]A005[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]WER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]4210[/TD]

[TD="class: xl64, align: right"]03-04-16[/TD]
[TD="class: xl63"]A006[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]HFT[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]4710[/TD]

[TD="class: xl64, align: right"]03-04-16[/TD]
[TD="class: xl63"]A007[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]ERW[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl63, align: right"]5710[/TD]

[TD="colspan: 4"]OPENING BALANCE OF ABC : 1000[/TD]

[TD="colspan: 4"]OPENING BALANCE OF BCD :1500[/TD]

[TD="colspan: 4"]OPENING BALANCE OF CDE :100[/TD]

[TD="colspan: 4"]OPENING BALANCE OF DEF :NILL[/TD]

[TD="colspan: 8"]1) I WANT DATE-WISE (FROM DATE TO DATE )(TILL DATE BALANCE MUST) REPORT AS IN FORMAT AS BELOW[/TD]

[TD="colspan: 7"] DATE DOC NO. LEDGER NARRATION AMOUNT BALANCE[/TD]

[TD="colspan: 7"]2) I WANT LEDGER-WISE DATE-WISE REPORT AS IN FORMAT AS BELOW[/TD]

[TD="colspan: 3"] LEDGER NAME (ABC)[/TD]

[TD="colspan: 6"] DATE DOC NO. NARRATION AMOUNT BALANCE[/TD]

[TD="colspan: 5"] OPENING BALNCE 1000 [/TD]

[TD="colspan: 5"]3) I WANT DOC NO-WISE REPORT AS BELOW[/TD]

[TD="colspan: 6"] DOC NO. DATE LEDGER AMOUNT BALANCE[/TD]

[TD="colspan: 8"]IF REPORT WANT FROM 03-04-2016 THAN BALANCE TILL DATE MUST SHOW (FROM 01-04 TO 02-04 LAST ENTRY TOTAL)[/TD]

</tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
[TABLE="class: cms_table"]
<tbody>[TR]
[/TR]
[TR]
[TD="class: cms_table_xl63"]date[/TD]
[TD="class: cms_table_xl63, width: 64"]doc no[/TD]
[TD="class: cms_table_xl63, width: 64"]ledger[/TD]
[TD="class: cms_table_xl63, width: 64"]narr[/TD]
[TD="class: cms_table_xl63, width: 64"]amt[/TD]
[TD="class: cms_table_xl63, width: 64"]balcne[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]01-04-16[/TD]
[TD="class: cms_table_xl63"]A001[/TD]
[TD="class: cms_table_xl63"]ABC[/TD]
[TD="class: cms_table_xl63"]XYZ[/TD]
[TD="class: cms_table_xl63, align: right"]1000[/TD]
[TD="class: cms_table_xl63, align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]01-04-16[/TD]
[TD="class: cms_table_xl63"]A002[/TD]
[TD="class: cms_table_xl63"]BCD[/TD]
[TD="class: cms_table_xl63"]RTY[/TD]
[TD="class: cms_table_xl63, align: right"]2000[/TD]
[TD="class: cms_table_xl63, align: right"]3000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]01-04-16[/TD]
[TD="class: cms_table_xl63"]A003[/TD]
[TD="class: cms_table_xl63"]CDE[/TD]
[TD="class: cms_table_xl63"]EER[/TD]
[TD="class: cms_table_xl63, align: right"]500[/TD]
[TD="class: cms_table_xl63, align: right"]3500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]02-04-16[/TD]
[TD="class: cms_table_xl63"]A004[/TD]
[TD="class: cms_table_xl63"]DEF[/TD]
[TD="class: cms_table_xl63"]EWR[/TD]
[TD="class: cms_table_xl63, align: right"]210[/TD]
[TD="class: cms_table_xl63, align: right"]3710[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]02-04-16[/TD]
[TD="class: cms_table_xl63"]A005[/TD]
[TD="class: cms_table_xl63"]BCD[/TD]
[TD="class: cms_table_xl63"]WER[/TD]
[TD="class: cms_table_xl63, align: right"]500[/TD]
[TD="class: cms_table_xl63, align: right"]4210[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]03-04-16[/TD]
[TD="class: cms_table_xl63"]A006[/TD]
[TD="class: cms_table_xl63"]ABC[/TD]
[TD="class: cms_table_xl63"]HFT[/TD]
[TD="class: cms_table_xl63, align: right"]500[/TD]
[TD="class: cms_table_xl63, align: right"]4710[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]03-04-16[/TD]
[TD="class: cms_table_xl63"]A007[/TD]
[TD="class: cms_table_xl63"]DEF[/TD]
[TD="class: cms_table_xl63"]ERW[/TD]
[TD="class: cms_table_xl63, align: right"]1000[/TD]
[TD="class: cms_table_xl63, align: right"]5710[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD="colspan: 4"]OPENING BALANCE OF ABC : 1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]OPENING BALANCE OF BCD :1500 please explain how this is so[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]OPENING BALANCE OF CDE :100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]OPENING BALANCE OF DEF :NILL[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
this data in sheet1 in excel

it is the opening balance of ledger BCD. if i want report of ledger BCD it will be shown as :

Ledger :BCD
Date Doc. no narration amount balance
-------------------------------------------------------------------------------
opening balance 1500
01/04/2016 A002 RTY 2000 3500
02/04/2016 A005 WER 520 4000
-------------------------------------------------------------------------------
Total balance is : 4000
 
Upvote 0
***

<tbody>
[TD="class: xl63"]date
[/TD]
[TD="class: xl63, width: 64"]doc no[/TD]
[TD="class: xl63, width: 64"]ledger[/TD]
[TD="class: xl63, width: 90"]narr[/TD]
[TD="class: xl63, width: 64"]amt[/TD]
[TD="class: xl63, width: 64"]balcne[/TD]
[TD="width: 64"][/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A001[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]XYZ[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl63, align: right"]1000[/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A002[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]RTY[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]3000[/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A003[/TD]
[TD="class: xl63"]CDE[/TD]
[TD="class: xl63"]EER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]3500[/TD]

[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]A004[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]EWR[/TD]
[TD="class: xl63, align: right"]210[/TD]
[TD="class: xl63, align: right"]3710[/TD]

[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]A005[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]WER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]4210[/TD]

[TD="class: xl64, align: right"]03-04-16[/TD]
[TD="class: xl63"]A006[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]HFT[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]4710[/TD]

[TD="class: xl64, align: right"]03-04-16[/TD]
[TD="class: xl63"]A007[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]ERW[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl63, align: right"]5710[/TD]

[TD="colspan: 4"]OPENING BALANCE OF ABC : 1000[/TD]

[TD="colspan: 4"]OPENING BALANCE OF BCD :1500[/TD]

[TD="colspan: 4"]OPENING BALANCE OF CDE :100[/TD]

[TD="colspan: 4"]OPENING BALANCE OF DEF :NILL[/TD]

[TD="colspan: 7"]1) I WANT DATE-WISE (FROM DATE TO DATE )(TILL DATE BALANCE MUST) REPORT AS IN FORMAT AS BELOW[/TD]

[TD="class: xl63"] date [/TD]
[TD="class: xl63"]doc no[/TD]
[TD="class: xl63"]ledger[/TD]
[TD="class: xl63"]narr[/TD]
[TD="class: xl63"]amt[/TD]
[TD="class: xl63"]balcne[/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A001[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]XYZ[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl65, align: right"]1000[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A002[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]RTY[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl65, align: right"]3000[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A003[/TD]
[TD="class: xl63"]CDE[/TD]
[TD="class: xl63"]EER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl65, align: right"]3500[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]A004[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]EWR[/TD]
[TD="class: xl63, align: right"]210[/TD]
[TD="class: xl65, align: right"]3710[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]A005[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]WER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl65, align: right"]4210[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64, align: right"]03-04-16[/TD]
[TD="class: xl63"]A006[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]HFT[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl65, align: right"]4710[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64, align: right"]03-04-16[/TD]
[TD="class: xl63"]A007[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]ERW[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl63, align: right"]5710[/TD]

[TD="colspan: 7"]2) I WANT LEDGER-WISE DATE-WISE REPORT AS IN FORMAT AS BELOW[/TD]

[TD="class: xl73, colspan: 5"]LEDGER NAME (ABC)[/TD]

[TD="class: xl63"]DATE[/TD]
[TD="class: xl63"]DOC NO[/TD]
[TD="class: xl63"]NARR[/TD]
[TD="class: xl63"]AMOUNT[/TD]
[TD="class: xl65"]BALANCE[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl63"] OPENING BALNCE[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65, align: right"]1000[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]A001[/TD]
[TD="class: xl63"]XYZ[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl65, align: right"]2000[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl67, align: right"]03-04-16[/TD]
[TD="class: xl68"]A006[/TD]
[TD="class: xl68"]HFT[/TD]
[TD="class: xl68, align: right"]500[/TD]
[TD="class: xl69, align: right"]2500[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl70, colspan: 3"]CLOASING BALANCE[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]2500[/TD]

[TD="colspan: 7"]3) I WANT DOC NO-WISE REPORT FROM A002 TO A006 THAN AS BELOW[/TD]

[TD="class: xl63"]DOC NO[/TD]
[TD="class: xl63"]DATE[/TD]
[TD="class: xl63"]LEDGER[/TD]
[TD="class: xl63"]NARRATION[/TD]
[TD="class: xl63"]AMOUNT[/TD]
[TD="class: xl63"]BALANCE[/TD]

[TD="class: xl63"]A002[/TD]
[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]RTY[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]2000[/TD]

[TD="class: xl63"]A003[/TD]
[TD="class: xl64, align: right"]01-04-16[/TD]
[TD="class: xl63"]CDE[/TD]
[TD="class: xl63"]EER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]2500[/TD]

[TD="class: xl63"]A004[/TD]
[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]EWR[/TD]
[TD="class: xl63, align: right"]210[/TD]
[TD="class: xl63, align: right"]2710[/TD]

[TD="class: xl63"]A005[/TD]
[TD="class: xl64, align: right"]02-04-16[/TD]
[TD="class: xl63"]BCD[/TD]
[TD="class: xl63"]WER[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]3210[/TD]

[TD="class: xl63"]A006[/TD]
[TD="class: xl64, align: right"]03-04-16[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]HFT[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63, align: right"]4210[/TD]

[TD="colspan: 7"]IF REPORT WANT FROM 03-04-2016 THAN BALANCE TILL DATE MUST SHOW (FROM 01-04 TO 02-04 LAST ENTRY TOTAL)[/TD]

</tbody>
 
Upvote 0
ahhhhhhhhhhhhhhhhhhhhhhhh....the last column is a running total....

I suggest you tackle it one question at a time

[TABLE="width: 855"]
<colgroup><col><col><col span="5"><col><col><col></colgroup><tbody>[TR]
[TD]1) I WANT DATE-WISE (FROM DATE TO DATE )(TILL DATE BALANCE MUST) REPORT AS IN FORMAT AS BELOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]date[/TD]
[TD]doc no[/TD]
[TD]ledger[/TD]
[TD]narr[/TD]
[TD]amt[/TD]
[TD]balcne[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/04/2016[/TD]
[TD]A001[/TD]
[TD]ABC[/TD]
[TD]XYZ[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/04/2016[/TD]
[TD]A002[/TD]
[TD]BCD[/TD]
[TD]RTY[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/04/2016[/TD]
[TD]A003[/TD]
[TD]CDE[/TD]
[TD]EER[/TD]
[TD]500[/TD]
[TD]3500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/04/2016[/TD]
[TD]A004[/TD]
[TD]DEF[/TD]
[TD]EWR[/TD]
[TD]210[/TD]
[TD]3710[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/04/2016[/TD]
[TD]A005[/TD]
[TD]BCD[/TD]
[TD]WER[/TD]
[TD]500[/TD]
[TD]4210[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/04/2016[/TD]
[TD]A006[/TD]
[TD]ABC[/TD]
[TD]HFT[/TD]
[TD]500[/TD]
[TD]4710[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/04/2016[/TD]
[TD]A007[/TD]
[TD]DEF[/TD]
[TD]ERW[/TD]
[TD]1000[/TD]
[TD]5710[/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]
[/TR]
[TR]
[TD]2) I WANT LEDGER-WISE DATE-WISE REPORT AS IN FORMAT AS BELOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]a pivot table gives you the row doc numbers[/TD]
[/TR]
[TR]
[TD]LEDGER NAME (ABC)[/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]ledger[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]DOC NO[/TD]
[TD]NARR[/TD]
[TD]AMOUNT[/TD]
[TD]BALANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OPENING BALNCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of doc no[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/04/2016[/TD]
[TD]A001[/TD]
[TD]XYZ[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD]doc no[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/04/2016[/TD]
[TD]A006[/TD]
[TD]HFT[/TD]
[TD]500[/TD]
[TD]2500[/TD]
[TD][/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CLOASING BALANCE[/TD]
[TD][/TD]
[TD]2500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A006[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]2[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]DOC NO[/TD]
[TD]NARR[/TD]
[TD]AMOUNT[/TD]
[TD]BALANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OPENING BALNCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A001[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A006[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]I obtained the 1000 by[/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]
[/TR]
[TR]
[TD][/TD]
[TD]=OFFSET($B$2,MATCH(B24,$B$3:$B$9,0),3)[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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="colspan: 7"]you can use the same principle to get the other amount , the narratives and the balances[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
sorry should have been clearer - the offset match formula means start from here,go down until you obtain a match, then go 3 columns to the right
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Delete customer if contains the same amount otherwise just subtract between two sheets
2
Replies
15
Views
387
igold
igold
M
  • Question Question
link the Date to the advanced editor of Excel Power Query
Replies
3
Views
633
JEC
JEC
ExcelNewbie2020
  • Solved
Allocation of Amount, based on the Monthly amount and dates
Replies
4
Views
411
ExcelNewbie2020
ExcelNewbie2020
T
  • Question Question
Retrieve info with different date formats
2
Replies
11
Views
239
tigerzen
T
A
  • Question Question
sorting duplicates Customers names by add two formulas in last column
Replies
3
Views
362
Kerryx
K

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

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