Need Help in a Data Table

[Santosh080]

hello Friends
i have a excel sheet like this.

MY MF.xlsx
	A	B	C	D	E
1	Dates	Debit	Credit	Interest	Balance
2	29-09-2011				100000.00
3	30-09-2011	5000	0	0	105000.00
4	01-10-2011	0	6000	0	99000.00
5	02-10-2011	0	0	0	99000.00
6	03-10-2011	0	0	0	99000.00
7	04-10-2011	0	0	0	99000.00
8	05-10-2011	0	0	0	99000.00
9	06-10-2011	0	0	0	99000.00
10	07-10-2011	0	0	0	99000.00
11	08-10-2011	0	0	0	99000.00
12	09-10-2011	0	0	0	99000.00
13	10-10-2011	0	0	1169	100169.00
14	11-10-2011	0	0	0	100169.00
15	12-10-2011	0	0	0	100169.00
16	13-10-2011	0	0	0	100169.00
17	14-10-2011	15000	0	0	115169.00
18	15-10-2011	0	0	0	115169.00
19	16-10-2011	0	0	0	115169.00
20	17-10-2011	0	0	0	115169.00
21	18-10-2011	0	3000	0	112169.00
22	19-10-2011	0	0	0	112169.00
23	20-10-2011	0	0	0	112169.00
24	21-10-2011	0	0	1547	113716.00
25	22-10-2011	0	0	0	113716.00
26	23-10-2011	0	0	0	113716.00
27	24-10-2011	0	0	0	113716.00
Sheet2

Cell Formulas
Range	Formula
E3:E27	E3	=E2+B3+D3-C3

I want to extract data like below table. Here i want ignore data where Debit,Credit,Interest,Balance all are "0" and extract data where data avaible. Please help me. Thanks

MY MF.xlsx
	I	J	K	L	M
1	Debit	Credit	Interest	Balance	Dates
2	0	0	0	100000.00	29-09-2011
3	0	6000	0	99000.00	01-10-2011
4	0	0	1169	100169.00	10-10-2011
5	15000	0	0	115169.00	14-10-2011
6	0	3000	0	112169.00	18-10-2011
7	0	0	1547	113716.00	21-10-2011
Sheet2

 

[SanjayGMusafir]

Santosh the data you have provided seems to be like some bank statement kind where you are getting a running total.

With Excel 2016 there are some limitations to work with but there are always workarounds.

1. Best is to convert your data into structured table - This way you can filter data manually the way you want to.
2. In case you want it on a click - Macro is a solution
   1. The easiest way to start with a macro is start recording a macro
   2. then perform the actions you need macro to perform
   3. Stop recording
   4. And use the macro over and over again.
3. Once you get that macro and you have any challenge, feel free to post your macro in the forum along with the problem you are facing. Some expert shall help you.

Hope this information helps you in some useful way.

 

[Santosh080]

thanks for reply.
If i install new version of office then is it possible? I really have no idea where to start a macro. Is there any other method?(Any method formula, vba, macro)

 

[SanjayGMusafir]

thanks for reply.
If i install new version of office then is it possible? I really have no idea where to start a macro. Is there any other method?(Any method formula, vba, macro)

With Excel 2021 and 365 it can be done with a simple formula that works dynamically.

Also, once you update your Excel, remember to update same in your profile here. Experts will know what version you are working on and will deliver solution accordingly.

 

[Santosh080]

I have installed office 2021. Now please tell how can i do this. Accutaly i have installed office 2021 in my home pc but at office it was MS Office 2016.

 

[SanjayGMusafir]

I have installed office 2021. Now please tell how can i do this. Accutaly i have installed office 2021 in my home pc but at office it was MS Office 2016.

I just respond back. Wait

thanks for your patience

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Dates	Debit	Credit	Interest	Balance				Dates	Debit	Credit	Interest	Balance
2	29/09/2011				100000				30/09/2011	5000	0	0	105000
3	30/09/2011	5000	0	0	105000				01/10/2011	0	6000	0	99000
4	01/10/2011	0	6000	0	99000				10/10/2011	0	0	1169	100169
5	02/10/2011	0	0	0	99000				14/10/2011	15000	0	0	115169
6	03/10/2011	0	0	0	99000				18/10/2011	0	3000	0	112169
7	04/10/2011	0	0	0	99000				21/10/2011	0	0	1547	113716
8	05/10/2011	0	0	0	99000
9	06/10/2011	0	0	0	99000
10	07/10/2011	0	0	0	99000
11	08/10/2011	0	0	0	99000
12	09/10/2011	0	0	0	99000
13	10/10/2011	0	0	1169	100169
14	11/10/2011	0	0	0	100169
15	12/10/2011	0	0	0	100169
16	13/10/2011	0	0	0	100169
17	14/10/2011	15000	0	0	115169
18	15/10/2011	0	0	0	115169
19	16/10/2011	0	0	0	115169
20	17/10/2011	0	0	0	115169
21	18/10/2011	0	3000	0	112169
22	19/10/2011	0	0	0	112169
23	20/10/2011	0	0	0	112169
24	21/10/2011	0	0	1547	113716
25	22/10/2011	0	0	0	113716
26	23/10/2011	0	0	0	113716
27	24/10/2011	0	0	0	113716
Certivin

Cell Formulas
Range	Formula
I2:M7	I2	=FILTER(A2:E100,MMULT(--(B2:D100<>0),SEQUENCE(COLUMNS(B2:D2),,,0)))
E3:E27	E3	=E2+B3+D3-C3
Dynamic array formulas.

 

[SanjayGMusafir]

I have installed office 2021. Now please tell how can i do this. Accutaly i have installed office 2021 in my home pc but at office it was MS Office 2016.

Check this -

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Dates	Debit	Credit	Interest	Balance		Debit	Credit	Interest	Balance
2	40815				1,00,000		5,000	0	0	1,05,000
3	40816	5,000	0	0	1,05,000		0	6,000	0	99,000
4	40817	0	6,000	0	99,000		0	0	1,169	1,00,169
5	40818	0	0	0	99,000		15,000	0	0	1,15,169
6	40819	0	0	0	99,000		0	3,000	0	1,12,169
7	40820	0	0	0	99,000		0	0	1,547	1,13,716
8	40821	0	0	0	99,000
9	40822	0	0	0	99,000
10	40823	0	0	0	99,000
11	40824	0	0	0	99,000
12	40825	0	0	0	99,000
13	40826	0	0	1,169	1,00,169
14	40827	0	0	0	1,00,169
15	40828	0	0	0	1,00,169
16	40829	0	0	0	1,00,169
17	40830	15,000	0	0	1,15,169
18	40831	0	0	0	1,15,169
19	40832	0	0	0	1,15,169
20	40833	0	0	0	1,15,169
21	40834	0	3,000	0	1,12,169
22	40835	0	0	0	1,12,169
23	40836	0	0	0	1,12,169
24	40837	0	0	1,547	1,13,716
25	40838	0	0	0	1,13,716
26	40839	0	0	0	1,13,716
27	40840	0	0	0	1,13,716
Sheet2

Cell Formulas
Range	Formula
G1:J1	G1	=B1:E1
G2:J7	G2	=FILTER(B2:E27,(B2:B27<>0)+(C2:C27<>0)+(D2:D27<>0))
E3:E27	E3	=E2+B3+D3-C3
Dynamic array formulas.

 

[Santosh080]

thanks for help. It is working. Is it possible with out FILTER formula i mean can it possible in office 2016 (in my office).

 

[Santosh080]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Dates	Debit	Credit	Interest	Balance				Dates	Debit	Credit	Interest	Balance
2	29/09/2011				100000				30/09/2011	5000	0	0	105000
3	30/09/2011	5000	0	0	105000				01/10/2011	0	6000	0	99000
4	01/10/2011	0	6000	0	99000				10/10/2011	0	0	1169	100169
5	02/10/2011	0	0	0	99000				14/10/2011	15000	0	0	115169
6	03/10/2011	0	0	0	99000				18/10/2011	0	3000	0	112169
7	04/10/2011	0	0	0	99000				21/10/2011	0	0	1547	113716
8	05/10/2011	0	0	0	99000
9	06/10/2011	0	0	0	99000
10	07/10/2011	0	0	0	99000
11	08/10/2011	0	0	0	99000
12	09/10/2011	0	0	0	99000
13	10/10/2011	0	0	1169	100169
14	11/10/2011	0	0	0	100169
15	12/10/2011	0	0	0	100169
16	13/10/2011	0	0	0	100169
17	14/10/2011	15000	0	0	115169
18	15/10/2011	0	0	0	115169
19	16/10/2011	0	0	0	115169
20	17/10/2011	0	0	0	115169
21	18/10/2011	0	3000	0	112169
22	19/10/2011	0	0	0	112169
23	20/10/2011	0	0	0	112169
24	21/10/2011	0	0	1547	113716
25	22/10/2011	0	0	0	113716
26	23/10/2011	0	0	0	113716
27	24/10/2011	0	0	0	113716
Certivin

Cell Formulas
Range	Formula
I2:M7	I2	=FILTER(A2:E100,MMULT(--(B2:D100<>0),SEQUENCE(COLUMNS(B2:D2),,,0)))
E3:E27	E3	=E2+B3+D3-C3
Dynamic array formulas.

this gives rersult but not like yours, it shows all data mean it does not ignore data where Debit,Credit,Interest,Balance all are "0". I just put same formula but why mine is not working?