Pivot Tables or alternative solutions

[Matt99]

Good day everyone!

I am working on a List or database that looks like the one below...

ID Date Debit Credit
01 1-May-2010 0 10,000
02 1-May-2010 10,000 0
03 1-May-2010 66.75 0
04 4-May-2010 2,500 0
05 4-May-2010 1,010 0
06 4-May-2010 0 2,500
07 4-May-2010 75 0
08 4-May-2010 0 1,000
09 7-May-2010 510 0
10 7-May-2010 500 0
11 7-May-2010 0 500
12 7-May-2010 0 70

I would like to use mainly Excel Pivot Tables
Or Other solutions either in Excel or Access:
if in Ms-Access - (queries or reports )


That will match Debits vs Credits for each day
then display the matched exact values (between Debit and Credit values)
Or display the values (also between Debit and Credit values) with the difference of up to $15
Table results is listed below...

ID Date Debit Credit
01 1-May-2010 0 10,000
02 1-May-2010 10,000 0
04 4-May-2010 2,500 0
05 4-May-2010 1,010 0
06 4-May-2010 0 2,500
08 4-May-2010 0 1,000
09 7-May-2010 510 0
10 7-May-2010 500 0
11 7-May-2010 0 500


and it ignore the other records...
Thanks!
Matt

 

[Gerald Higgins]

Why is value # 9 included in the results table ?

 

[Matt99]

Well, I want all values in both Debit and credit for each date that are:
Either equal in value
Or
Difference between values is $15.

So line id 9, 10, and 11 will be displayed for date: May 07
09 7-May-2010 510 0
10 7-May-2010 500 0
11 7-May-2010 0 500

 

[Gerald Higgins]

OK, I was assuming you'd want a one to one match, and not two(+) to one.

 

[Oeldere]

and filter on test

Excel Workbook
	A	B	C	D	E	F
1						15,00
2	ID	Date	Debit	Credit	result	test
3	1	1-May-2010	0,00	10,00	-10,00	ONWAAR
4	2	1-May-2010	10,00	0,00	10,00	ONWAAR
5	3	1-May-2010	66,75	0,00	66,75	WAAR
6	4	4-May-2010	2,50	0,00	2,50	ONWAAR
7	5	4-May-2010	1,01	0,00	1,01	ONWAAR
8	6	4-May-2010	0,00	2,50	-2,50	ONWAAR
9	7	4-May-2010	75,00	0,00	75,00	WAAR
10	8	4-May-2010	0,00	1,00	-1,00	ONWAAR
11	9	7-May-2010	510,00	0,00	510,00	WAAR
12	10	7-May-2010	500,00	0,00	500,00	WAAR
13	11	7-May-2010	0,00	500,00	-500,00	WAAR
14	12	7-May-2010	0,00	70,00	-70,00	WAAR
Blad1

Cell Formulas
Range	Formula
E3	=C3-D3
E4	=C4-D4
E5	=C5-D5
E6	=C6-D6
E7	=C7-D7
E8	=C8-D8
E9	=C9-D9
E10	=C10-D10
E11	=C11-D11
E12	=C12-D12
E13	=C13-D13
E14	=C14-D14
F3	=ABS(E3)>$F$1
F4	=ABS(E4)>$F$1
F5	=ABS(E5)>$F$1
F6	=ABS(E6)>$F$1
F7	=ABS(E7)>$F$1
F8	=ABS(E8)>$F$1
F9	=ABS(E9)>$F$1
F10	=ABS(E10)>$F$1
F11	=ABS(E11)>$F$1
F12	=ABS(E12)>$F$1
F13	=ABS(E13)>$F$1
F14	=ABS(E14)>$F$1