Reconciling debits and credits

[RG93]

Hi, I’m trying to do a reconciliation and see what has been offset. I have a column of debits and credits and I have a difference of £68k between the 2 columns. There’s about 1000 lines in total but I’ve uploaded a small sample. I’ve tried matching but quite a few of the figures appear more than once. Is there a formula I can use to see which transactions haven’t netted off please? Thanks

 

[alansidman]

Cannot manipulate data in a picture. Please resubmit using XL2BB function. Also, will all debits match credits one for one or will there be multiple entries to offset a single entry.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RG93]

Cannot manipulate data in a picture. Please resubmit using XL2BB function. Also, will all debits match credits one for one or will there be multiple entries to offset a single entry.

I'm struggling to get the add in to work. Is there another way i can post my data? I'm not entirely sure if all debits will match credits. I haven't been given much guidance unfortunately

 

[abdelfattah]

@RG93
but why you can't use XL2BB tool?!
just click icon XL2BB and will move link to show you how adds in and how use it.
or shows picture before and expected result after.
good luck.

 

[RG93]

@RG93
but why you can't use XL2BB tool?!
just click icon XL2BB and will move link to show you how adds in and how use it.
or shows picture before and expected result after.
good luck.

I think maybe because I was using my work computer. I might be restricted in what I can add in. I will do it on my personal laptop later and hopefully that works. I’m off now but will definitely be back and hopefully someone can help. Thanks!

 

[RG93]

I've managed to download the add in. Hopefully it's attached correctly. Here is my spreadsheet with sensitive info removed. Some vendors appear twice but its not clear who is who. The debits are meant to be offset by the credits meaning it should total to zero but the variance is 58k. I'm trying to see if there is a faster way to see which transactions match. It's also possible that a number of transactions need to be matched to be linked to a larger number, waiting for guidance on that. Any help that can be provided would be greatly appreciated

Sample 3.xlsx
	A	B	C	D	E	F	G
1	Type	Date	Document Number	Name	Debit	Credit	Balance
2							-£269,840.38
3	Bill	07/02/2022	203032884	VENDOR 1	£454.58		-£269,385.79
4	Bill	07/03/2022	203032885	VENDOR 2	£520.08		-£268,865.71
5	Bill	07/04/2022	203032886	VENDOR 3	£520.08		-£268,345.62
6	Bill	25/02/2023	203032887	VENDOR 4	£115.86		-£268,229.77
7	Bill	25/03/2023	203032888	VENDOR 5	£115.86		-£268,113.91
8	Journal	01/05/2023	203032889	VENDOR 6	£9,744.89		-£258,369.02
9	Bill	10/06/2023	203032890	VENDOR 7	£14.98		-£258,354.04
10	Bill	22/06/2023	203032891	VENDOR 8	£14.98		-£258,339.07
11	Bill	01/07/2023	203032892	VENDOR 9	£98.08		-£258,240.99
12	Bill	01/08/2023	203032893	VENDOR 10	£98.08		-£258,142.91
13	Bill	25/09/2023	203032894	VENDOR 11	£14.34		-£258,128.56
14	Bill	30/09/2023	203032895	VENDOR 12	£20.26		-£258,108.30
15	Journal	01/10/2023	203032896	VENDOR 13	£3,091.13		-£255,017.17
16	Journal	01/10/2023	203032897	VENDOR 14		£154,225.06	-£409,242.23
17	Bill	02/10/2023	203032898	VENDOR 15	£14.52		-£409,227.71
18	Bill	02/10/2023	203032899	VENDOR 16	£17.13		-£409,210.59
19	Bill	08/10/2023	203032900	VENDOR 17	£41,952.00		-£367,258.59
20	Bill	09/10/2023	203032901	VENDOR 18	£20.44		-£367,238.15
21	Bill	09/10/2023	203032902	VENDOR 19	£89.16		-£367,148.98
22	Bill	11/10/2023	203032903	VENDOR 20	£179.22		-£366,969.76
23	Bill	13/10/2023	203032904	VENDOR 21	£111.37		-£366,858.39
24	Bill	16/10/2023	203032905	VENDOR 22	£21.64		-£366,836.75
25	Item Receipt	16/10/2023	203032906	VENDOR 23	£278.38		-£366,558.37
26	Invoice	17/10/2023	203032907	VENDOR 24		£6,432.89	-£372,991.26
27	Invoice	17/10/2023	203032908	VENDOR 25		£280.75	-£373,272.01
28	Invoice	17/10/2023	203032909	VENDOR 26		£6,939.36	-£380,211.37
29	Invoice	17/10/2023	203032910	VENDOR 27	£833.33		-£379,378.04
30	Invoice	17/10/2023	203032911	VENDOR 28		£520.00	-£379,898.04
31	Invoice	17/10/2023	203032912	VENDOR 29		£333.33	-£380,231.37
32	Invoice	17/10/2023	203032913	VENDOR 30		£51,810.00	-£432,041.37
33	Invoice	17/10/2023	203032914	VENDOR 31		£8,309.85	-£440,351.22
34	Item Receipt	17/10/2023	203032915	VENDOR 32	£85.82		-£440,265.40
35	Invoice	17/10/2023	203032916	VENDOR 33		£163,760.17	-£604,025.57
36	Credit Memo	17/10/2023	203032917	VENDOR 34	£154,225.06		-£449,800.51
37	Bill	18/10/2023	203032918	VENDOR 35	£167.02		-£449,633.49
38	Bill	19/10/2023	203032919	VENDOR 36	£111.30		-£449,522.19
39	Bill	20/10/2023	203032920	VENDOR 37	£46.80		-£449,475.39
40	Journal	23/10/2023	203032921	VENDOR 38		£8,995.00	-£458,470.39
41	Bill	24/10/2023	203032922	VENDOR 39	£37.10		-£458,433.29
42	Item Receipt	24/10/2023	203032923	VENDOR 40	£62.21		-£458,371.08
43	Bill	27/10/2023	203032924	VENDOR 41	£167.02		-£458,204.06
44	Bill	30/10/2023	203032925	VENDOR 42	£2,147.69		-£456,056.37
45	Item Receipt	30/10/2023	203032926	VENDOR 43	£51,810.00		-£404,246.37
46	Journal	31/10/2023	203032927	VENDOR 44		£15,000.00	-£419,246.37
47	Journal	31/10/2023	203032928	VENDOR 45		£15,000.00	-£434,246.37
48	Journal	31/10/2023	203032929	VENDOR 46		£15,000.00	-£449,246.37
49	Journal	31/10/2023	203032930	VENDOR 47	£27,192.07		-£422,054.30
50	Journal	31/10/2023	203032931	VENDOR 48		£278.38	-£422,332.68
51	Journal	31/10/2023	203032932	VENDOR 49	£145,000.00		-£277,332.68
52	Journal	31/10/2023	203032933	VENDOR 50	£15,000.00		-£262,332.68
53	Journal	31/10/2023	203032934	VENDOR 51	£15,000.00		-£247,332.68
54	Journal	31/10/2023	203032935	VENDOR 52	£15,000.00		-£232,332.68
55	Item Receipt	31/10/2023	203032936	VENDOR 53	£117.80		-£232,214.88
56	Journal	01/11/2023	203032937	VENDOR 54		£15,000.00	-£247,214.88
57	Journal	01/11/2023	203032938	VENDOR 55		£4,774.25	-£251,989.13
58	Journal	01/11/2023	203032939	VENDOR 56	£2,570.75		-£249,418.38
59	Journal	01/11/2023	203032940	VENDOR 57		£514.15	-£249,932.53
60	Journal	01/11/2023	203032941	VENDOR 58	£514.15		-£249,418.38
61	Journal	01/11/2023	203032942	VENDOR 59		£145,000.00	-£394,418.38
62	Journal	01/11/2023	203032943	VENDOR 60	£15,000.00		-£379,418.38
63	Journal	01/11/2023	203032944	VENDOR 61	£4,774.25		-£374,644.13
64	Journal	01/11/2023	203032945	VENDOR 62	£367.25		-£374,276.88
65	Journal	01/11/2023	203032946	VENDOR 63	£15,000.00		-£359,276.88
66	Bill	02/11/2023	203032947	VENDOR 64	£105.33		-£359,171.55
67	Bill	02/11/2023	203032948	VENDOR 65	£63.60		-£359,107.95
68	Item Receipt	08/11/2023	203032949	VENDOR 66	£373.67		-£358,734.28
69	Item Receipt	14/11/2023	203032950	VENDOR 67	£33.16		-£358,701.12
70	Item Receipt	15/11/2023	203032951	VENDOR 68	£39,271.00		-£319,430.12
71	Invoice	17/11/2023	203032952	VENDOR 69		£333.33	-£319,763.45
72	Invoice	17/11/2023	203032953	VENDOR 70		£39,271.00	-£359,034.45
73	Invoice	17/11/2023	203032954	VENDOR 71		£24,400.00	-£383,434.45
74	Invoice	17/11/2023	203032955	VENDOR 72		£6,888.08	-£390,322.53
75	Invoice	17/11/2023	203032956	VENDOR 73		£15,000.00	-£405,322.53
76	Invoice	17/11/2023	203032957	VENDOR 74		£157,951.88	-£563,274.41
77	Invoice	17/11/2023	203032958	VENDOR 75		£2,667.69	-£565,942.10
78	Invoice	17/11/2023	203032959	VENDOR 76		£280.75	-£566,222.85
79	Journal	17/11/2023	203032960	VENDOR 77	£157,951.88		-£408,270.97
80	Invoice	17/11/2023	203032961	VENDOR 78		£8,309.85	-£416,580.82
81	Bill	20/11/2023	203032962	VENDOR 79	£42.89		-£416,537.93
82	Bill	28/11/2023	203032963	VENDOR 80	£1,413.00		-£415,124.93
83	Journal	30/11/2023	203032964	VENDOR 81	£18,804.56		-£396,320.37
84	Journal	30/11/2023	203032965	VENDOR 82	£145,000.00		-£251,320.37
85	Bill	30/11/2023	203032966	VENDOR 83	£2,025.00		-£249,295.37
86	Journal	01/12/2023	203032967	VENDOR 84		£145,000.00	-£394,295.37
87	Journal	01/12/2023	203032968	VENDOR 85	£440.70		-£393,854.67
88	Journal	01/12/2023	203032969	VENDOR 86	£220.35		-£393,634.32
89	Item Receipt	04/12/2023	203032970	VENDOR 87	£188.75		-£393,445.57
90	Bill	04/12/2023	203032971	VENDOR 88	£19.48		-£393,426.09
91	Item Receipt	07/12/2023	203032972	VENDOR 89	£157.53		-£393,268.56
92	Item Receipt	08/12/2023	203032973	VENDOR 90	£23,762.00		-£369,506.56
93	Bill	11/12/2023	203032974	VENDOR 91	£11.76		-£369,494.80
94	Invoice	15/12/2023	203032975	VENDOR 92		£6,992.87	-£376,487.67
95	Invoice	15/12/2023	203032976	VENDOR 93		£23,762.00	-£400,249.67
96	Invoice	15/12/2023	203032977	VENDOR 94		£520.00	-£400,769.67
97	Invoice	15/12/2023	203032978	VENDOR 95		£11,400.00	-£412,169.67
98	Invoice	15/12/2023	203032979	VENDOR 96		£13,464.23	-£425,633.90
99	Invoice	15/12/2023	203032980	VENDOR 97		£8,309.85	-£433,943.75
100	Invoice	15/12/2023	203032981	VENDOR 98		£280.75	-£434,224.50
101	Invoice	15/12/2023	203032982	VENDOR 99		£333.33	-£434,557.83
102	Item Receipt	18/12/2023	203032983	VENDOR 100	£24.99		-£434,532.84
103	Bill	19/12/2023	203032984	VENDOR 101	£5,752.80		-£428,780.04
104	Item Receipt	19/12/2023	203032985	VENDOR 102	£74.98		-£428,705.06
105	Journal	19/12/2023	203032986	VENDOR 103	£158,464.23		-£270,240.83
106	Bill	21/12/2023	203032987	VENDOR 104	£18.54		-£270,222.29
107	Journal	22/12/2023	203032988	VENDOR 105	£1,949.62		-£268,272.67
108	Bill	29/12/2023	203032989	VENDOR 106	£1,914.94		-£266,357.73
109	Bill	29/12/2023	203032990	VENDOR 107	£2,193.75		-£264,163.98
110	Journal	31/12/2023	203032991	VENDOR 108	£25,774.95		-£238,389.03
111	Item Receipt	08/01/2024	203032992	VENDOR 109	£14,610.00		-£223,779.03
112	Journal	12/01/2024	203032993	VENDOR 110		£440.70	-£224,219.73
113	Invoice	15/01/2024	203032994	VENDOR 111	£1,922.64		-£222,297.09
114	Invoice	15/01/2024	203032995	VENDOR 112		£14,610.00	-£236,907.09
115	Invoice	15/01/2024	203032996	VENDOR 113		£7,730.86	-£244,637.95
116	Invoice	15/01/2024	203032997	VENDOR 114		£8,970.49	-£253,608.44
117	Invoice	15/01/2024	203032998	VENDOR 115	£333.33		-£253,275.11
118	Invoice	15/01/2024	203032999	VENDOR 116		£280.75	-£253,555.86
119	Invoice	15/01/2024	203033000	VENDOR 117		£19,929.82	-£273,485.68
120	Item Receipt	18/01/2024	203033001	VENDOR 118	£145.82		-£273,339.86
121	Bill	20/01/2024	203033002	VENDOR 119	£2,088.00		-£271,251.86
122	Bill	22/01/2024	203033003	VENDOR 120	£16.71		-£271,235.14
123	Bill	28/01/2024	203033004	VENDOR 121	£255.36		-£270,979.78
124	Bill	28/01/2024	203033005	VENDOR 122	£255.36		-£270,724.42
125	Bill	28/01/2024	203033006	VENDOR 123	£151.98		-£270,572.44
126	Bill	28/01/2024	203033007	VENDOR 124	£127.68		-£270,444.76
127	Bill	28/01/2024	203033008	VENDOR 125	£255.36		-£270,189.40
128	Bill	28/01/2024	203033009	VENDOR 126	£255.36		-£269,934.04
129	Bill	29/01/2024	203033010	VENDOR 127	£200.00		-£269,734.04
130	Bill	29/01/2024	203033011	VENDOR 128	£500.00		-£269,234.04
131	Bill	29/01/2024	203033012	VENDOR 129	£27,840.00		-£241,394.04
132	Bill	29/01/2024	203033013	VENDOR 130	£19,124.00		-£222,270.04
133	Item Receipt	31/01/2024	203033014	VENDOR 131	£374.15		-£221,895.89
134	Bill	31/01/2024	203033015	VENDOR 132	£3,881.25		-£218,014.64
135	Journal	31/01/2024	203033016	VENDOR 133	£11,741.55		-£206,273.09
136	Bill	01/02/2024	203033017	VENDOR 134	£63.25		-£206,209.84
137	Bill	02/02/2024	203033018	VENDOR 135	£129.21		-£206,080.63
138	Bill	02/02/2024	203033019	VENDOR 136	£382.42		-£205,698.21
139	Bill	07/02/2024	203033020	VENDOR 137	£40.08		-£205,658.13
140	Item Receipt	12/02/2024	203033021	VENDOR 138	£7,066.00		-£198,592.13
141	Invoice	15/02/2024	203033022	VENDOR 139		£14,800.00	-£213,392.13
142	Invoice	15/02/2024	203033023	VENDOR 140		£3,808.34	-£217,200.47
143	Invoice	15/02/2024	203033024	VENDOR 141		£659.35	-£217,859.82
144	Bill	15/02/2024	203033025	VENDOR 142	£882.77		-£216,977.05
145	Invoice	15/02/2024	203033026	VENDOR 143	£2,252.50		-£214,724.55
146	Invoice	15/02/2024	203033027	VENDOR 144		£7,066.00	-£221,790.55
147	Bill	21/02/2024	203033028	VENDOR 145	£120.83		-£221,669.72
148	Journal	21/02/2024	203033029	VENDOR 146		£2.94	-£221,672.67
149	Bill	22/02/2024	203033030	VENDOR 147	£23.32		-£221,649.35
150	Bill	26/02/2024	203033031	VENDOR 148	£148.33		-£221,501.02
151	Item Receipt	28/02/2024	203033032	VENDOR 149	£18,750.00		-£202,751.02
152	Journal	29/02/2024	203033033	VENDOR 150	£2.94		-£202,748.07
153	Bill	29/02/2024	203033034	VENDOR 151	£116.65		-£202,631.42
154	Journal	29/02/2024	203033035	VENDOR 152		£2.94	-£202,634.37
155	Bill	29/02/2024	203033036	VENDOR 153	£7.48		-£202,626.89
156	Journal	29/02/2024	203033037	VENDOR 154	£11,772.40		-£190,854.49
157	Bill	02/03/2024	203033038	VENDOR 155	£128.32		-£190,726.17
158	Bill	04/03/2024	203033039	VENDOR 156	£66.66		-£190,659.51
159	Bill	05/03/2024	203033040	VENDOR 157	£1,195.05		-£189,464.46
160	Bill	05/03/2024	203033041	VENDOR 158	£6,795.00		-£182,669.46
161	Bill	06/03/2024	203033042	VENDOR 159	£19.16		-£182,650.30
162	Bill	08/03/2024	203033043	VENDOR 160	£141.65		-£182,508.65
163	Bill	11/03/2024	203033044	VENDOR 161	£141.65		-£182,367.00
164	Bill	14/03/2024	203033045	VENDOR 162	£173.04		-£182,193.96
165	Invoice	15/03/2024	203033046	VENDOR 163		£3,496.00	-£185,689.96
166	Invoice	15/03/2024	203033047	VENDOR 164		£6,795.00	-£192,484.96
167	Invoice	15/03/2024	203033048	VENDOR 165		£659.35	-£193,144.31
168	Invoice	15/03/2024	203033049	VENDOR 166		£14,800.00	-£207,944.31
169	Invoice	15/03/2024	203033050	VENDOR 167		£3,808.34	-£211,752.65
170	Bill	18/03/2024	203033051	VENDOR 168	£31.32		-£211,721.32
171	Item Receipt	22/03/2024	203033052	VENDOR 169	£1,608.54		-£210,112.78
172	Bill Credit	25/03/2024	203033053	VENDOR 170		£31.39	-£210,144.17
173	Bill	25/03/2024	203033054	VENDOR 171	£24.99		-£210,119.18
174	Bill	28/03/2024	203033055	VENDOR 172	£30.67		-£210,088.52
175	Bill	31/03/2024	203033056	VENDOR 173	£15.21		-£210,073.30
176	Journal	31/03/2024	203033057	VENDOR 174	£11,245.26		-£198,828.04
177	Bill	02/04/2024	203033058	VENDOR 175	£129.06		-£198,698.98
178	Bill	05/04/2024	203033059	VENDOR 176	£4,507.00		-£194,191.98
179	Journal	15/04/2024	203033060	VENDOR 177		£447.20	-£194,639.18
180	Invoice	15/04/2024	203033061	VENDOR 178		£14,800.00	-£209,439.18
181	Invoice	15/04/2024	203033062	VENDOR 179		£4,507.00	-£213,946.18
182	Invoice	15/04/2024	203033063	VENDOR 180		£659.35	-£214,605.53
183	Invoice	15/04/2024	203033064	VENDOR 181		£280.75	-£214,886.28
184	Invoice	15/04/2024	203033065	VENDOR 182		£3,496.00	-£218,382.28
185	Invoice	15/04/2024	203033066	VENDOR 183		£3,808.34	-£222,190.62
186	Bill	17/04/2024	203033067	VENDOR 184	£97.59		-£222,093.03
187	Bill	22/04/2024	203033068	VENDOR 185	£15.21		-£222,077.82
188	Invoice	23/04/2024	203033069	VENDOR 186		£91.27	-£222,169.08
189	Invoice	23/04/2024	203033070	VENDOR 187		£109.52	-£222,278.60
190	Credit Memo	23/04/2024	203033071	VENDOR 188	£109.52		-£222,169.08
191	Journal	23/04/2024	203033072	VENDOR 189	£91.27		-£222,077.82
192	Journal	29/04/2024	203033073	VENDOR 190		£15.21	-£222,093.03
193	Journal	29/04/2024	203033074	VENDOR 191		£15.21	-£222,108.25
194	Journal	30/04/2024	203033075	VENDOR 192	£11,245.56		-£210,862.69
195					£1,272,438.62	£1,213,460.93	-£210,862.69
GeneralLedger

Cell Formulas
Range	Formula
G3:G194	G3	=G2+E3-F3
E195:F195	E195	=SUM(E2:E194)
G195	G195	=G194

 

[abdelfattah]

I suggest for you show some result what you want expected result until can members help you.

 

[RG93]

I want to see which transactions match and if there is a combination of some debits that may add up to a corresponding value in the credit column and vice versa. I’m mainly trying to see what is causing the variance between the two columns

 

[RG93]

Had some progress with a match formula but still can’t see what’s driving the variance unfortunately