create date wise & ledger wise report by vba in excel

[SACHIN_P]

date	doc no	ledger	narr	amt	balcne
01-04-16	A001	ABC	XYZ	1000	1000
01-04-16	A002	BCD	RTY	2000	3000
01-04-16	A003	CDE	EER	500	3500
02-04-16	A004	DEF	EWR	210	3710
02-04-16	A005	BCD	WER	500	4210
03-04-16	A006	ABC	HFT	500	4710
03-04-16	A007	DEF	ERW	1000	5710
OPENING BALANCE OF ABC : 1000
OPENING BALANCE OF BCD :1500
OPENING BALANCE OF CDE :100
OPENING BALANCE OF DEF :NILL
1) I WANT DATE-WISE (FROM DATE TO DATE )(TILL DATE BALANCE MUST) REPORT AS IN FORMAT AS BELOW
DATE DOC NO. LEDGER NARRATION AMOUNT BALANCE
2) I WANT LEDGER-WISE DATE-WISE REPORT AS IN FORMAT AS BELOW
LEDGER NAME (ABC)
DATE DOC NO. NARRATION AMOUNT BALANCE
OPENING BALNCE 1000
3) I WANT DOC NO-WISE REPORT AS BELOW
DOC NO. DATE LEDGER AMOUNT BALANCE
***
IF REPORT WANT FROM 03-04-2016 THAN BALANCE TILL DATE MUST SHOW (FROM 01-04 TO 02-04 LAST ENTRY TOTAL)

<colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

date	doc no	ledger	narr	amt	balcne
01-04-16	A001	ABC	XYZ	1000	1000
01-04-16	A002	BCD	RTY	2000	3000
01-04-16	A003	CDE	EER	500	3500
02-04-16	A004	DEF	EWR	210	3710
02-04-16	A005	BCD	WER	500	4210
03-04-16	A006	ABC	HFT	500	4710
03-04-16	A007	DEF	ERW	1000	5710
OPENING BALANCE OF ABC : 1000
OPENING BALANCE OF BCD :1500 please explain how this is so
OPENING BALANCE OF CDE :100
OPENING BALANCE OF DEF :NILL

<tbody>
</tbody>

 

[SACHIN_P]

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

 

[SACHIN_P]

date	doc no	ledger	narr	amt	balcne
01-04-16	A001	ABC	XYZ	1000	1000
01-04-16	A002	BCD	RTY	2000	3000
01-04-16	A003	CDE	EER	500	3500
02-04-16	A004	DEF	EWR	210	3710
02-04-16	A005	BCD	WER	500	4210
03-04-16	A006	ABC	HFT	500	4710
03-04-16	A007	DEF	ERW	1000	5710
OPENING BALANCE OF ABC : 1000
OPENING BALANCE OF BCD :1500
OPENING BALANCE OF CDE :100
OPENING BALANCE OF DEF :NILL
1) I WANT DATE-WISE (FROM DATE TO DATE )(TILL DATE BALANCE MUST) REPORT AS IN FORMAT AS BELOW
date	doc no	ledger	narr	amt	balcne
01-04-16	A001	ABC	XYZ	1000	1000
01-04-16	A002	BCD	RTY	2000	3000
01-04-16	A003	CDE	EER	500	3500
02-04-16	A004	DEF	EWR	210	3710
02-04-16	A005	BCD	WER	500	4210
03-04-16	A006	ABC	HFT	500	4710
03-04-16	A007	DEF	ERW	1000	5710
2) I WANT LEDGER-WISE DATE-WISE REPORT AS IN FORMAT AS BELOW
LEDGER NAME (ABC)
DATE	DOC NO	NARR	AMOUNT	BALANCE
OPENING BALNCE				1000
01-04-16	A001	XYZ	1000	2000
03-04-16	A006	HFT	500	2500
CLOASING BALANCE		2500
3) I WANT DOC NO-WISE REPORT FROM A002 TO A006 THAN AS BELOW
DOC NO	DATE	LEDGER	NARRATION	AMOUNT	BALANCE
A002	01-04-16	BCD	RTY	2000	2000
A003	01-04-16	CDE	EER	500	2500
A004	02-04-16	DEF	EWR	210	2710
A005	02-04-16	BCD	WER	500	3210
A006	03-04-16	ABC	HFT	500	4210
***
IF REPORT WANT FROM 03-04-2016 THAN BALANCE TILL DATE MUST SHOW (FROM 01-04 TO 02-04 LAST ENTRY TOTAL)

<tbody>
</tbody>

 

[oldbrewer]

ahhhhhhhhhhhhhhhhhhhhhhhh....the last column is a running total....

I suggest you tackle it one question at a time

1) I WANT DATE-WISE (FROM DATE TO DATE )(TILL DATE BALANCE MUST) REPORT AS IN FORMAT AS BELOW
date	doc no	ledger	narr	amt	balcne
01/04/2016	A001	ABC	XYZ	1000	1000
01/04/2016	A002	BCD	RTY	2000	3000
01/04/2016	A003	CDE	EER	500	3500
02/04/2016	A004	DEF	EWR	210	3710
02/04/2016	A005	BCD	WER	500	4210
03/04/2016	A006	ABC	HFT	500	4710
03/04/2016	A007	DEF	ERW	1000	5710
2) I WANT LEDGER-WISE DATE-WISE REPORT AS IN FORMAT AS BELOW						a pivot table gives you the row doc numbers
LEDGER NAME (ABC)
							ledger	ABC
DATE	DOC NO	NARR	AMOUNT	BALANCE
OPENING BALNCE				1000			Count of doc no
01/04/2016	A001	XYZ	1000	2000			doc no	Total
03/04/2016	A006	HFT	500	2500			A001	1
CLOASING BALANCE		2500					A006	1
							Grand Total	2
DATE	DOC NO	NARR	AMOUNT	BALANCE
OPENING BALNCE				1000
	A001		1000
	A006
	I obtained the 1000 by
	=OFFSET($B$2,MATCH(B24,$B$3:$B$9,0),3)
	you can use the same principle to get the other amount , the narratives and the balances

<colgroup><col><col><col span="5"><col><col><col></colgroup><tbody>
</tbody>

 

[SACHIN_P]

Isn't it done by vba...?

 

[oldbrewer]

no

 

[SACHIN_P]

Ok....!

 

[oldbrewer]

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