Hi All Could you please assist, Project I need to reconcile a statement using multiple criteria I have attached a file which contains a sample source and the finished result I want Task 1 - Need to extract the first column (A Number) based on first 4 digits - Completed Task 2 - Need to extract the column (A Account) based on last 4 digits - Completed For eg Now the main task A NumberVoucherDateA Account Amount JALA1000-035324ABJ00000462330/06/20221026150-2235-AB0751-8340- 1,500,000.00100083401000-035324ABJ00000462330/06/20223025200-2235-AB0751-8340 1,500,000.00100083401000-035324ABJ00000462330/06/20221026150-2235-AB0751-8234- 25,000.00100082341000-035324ABJ00000462330/06/20223025200-2235-AB0751-8234 25,000.00100082341000-006139ABJ00000233930/06/20213025200-2235-AB0751-8245- 45,000.00100082458245-006787ABJ00000239830/06/20211026150-2235-AB0751-1000 45,000.00824510008245-006787ABJ00000239830/06/20211026150-2235-AB0751-1000 1,500.0082451000 If data extracted from task 1 and task 2 matches with date column (Date) and amount column (Amount) then give me a Match else give No Match Points 1. The data in JA and LA can come in any order like the ones I have highlighted in bold 2. All the matching criteria could be anywhere in the source. I was thinking of adding a helper column where I add column JA and LA and use the helper as a lookup to match amount and date Please help Query.xlsx

Formula or logic needed to match various criteria

cterrence

Hi All

Could you please assist,

Project

I need to reconcile a statement using multiple criteria

I have attached a file which contains a sample source and the finished result I want

Task 1 - Need to extract the first column (A Number) based on first 4 digits - Completed

Task 2 - Need to extract the column (A Account) based on last 4 digits - Completed

For eg

Now the main task

A Number	Voucher	Date	A Account	Amount	JA	LA
1000-035324	ABJ000004623	30/06/2022	1026150-2235-AB0751-8340	- 1,500,000.00	1000	8340
1000-035324	ABJ000004623	30/06/2022	3025200-2235-AB0751-8340	1,500,000.00	1000	8340
1000-035324	ABJ000004623	30/06/2022	1026150-2235-AB0751-8234	- 25,000.00	1000	8234
1000-035324	ABJ000004623	30/06/2022	3025200-2235-AB0751-8234	25,000.00	1000	8234
1000-006139	ABJ000002339	30/06/2021	3025200-2235-AB0751-8245	- 45,000.00	1000	8245
8245-006787	ABJ000002398	30/06/2021	1026150-2235-AB0751-1000	45,000.00	8245	1000
8245-006787	ABJ000002398	30/06/2021	1026150-2235-AB0751-1000	1,500.00	8245	1000

If data extracted from task 1 and task 2 matches with date column (Date) and amount column (Amount) then give me a Match else give No Match

Points
1. The data in JA and LA can come in any order like the ones I have highlighted in bold
2. All the matching criteria could be anywhere in the source.

I was thinking of adding a helper column where I add column JA and LA and use the helper as a lookup to match amount and date

Please help

Query.xlsx

Workflow

Output

Macros

Accepted answers

All comments

caltang

Based on your logic @cterrence - the last row should be "No", no?

For cterrence.yxmd

caltang

What is your defined logic in joining the fields? Can you lay it out clearly in bullet points so we all understand what you're trying to do?

cterrence

Thank you for the time and reply @caltang

JA and LA which are extracted columns are one of the main column for us to reconcile. The data to check can exist anywhere in the data source in any order like in the example below. Please see - 1000 and 8245. It can be in reverse order or same order

Eg 1

JA	LA
1000	8245
1000	5245
1000	5820
1000	6578
8245	1000

Eg 2

JA	LA
1000	8245
1000	5245
1000	5820
1000	6578
1000	8245

Please note that we are clearing out all the transactions that meet the conditions. There are conditions that it has to satisfy

Based on JA/LA column...we have to match the date and amount column.
1. If the data in JA/LA is same and if the corresponding data in date and amount is the same, then give "Match" else give me "Does not Match". Please see example

Date	Amount	JA	LA	Status
30/06/2023	-5000	1000	8245	Match
30/06/2023	500	1000	5245	Does not Match
30/06/2023	250	1000	5820	Does not Match
30/06/2023	1000	1000	6578	Does not Match
30/06/2023	5000	8245	1000	Match
1/07/2023	1000	8245	1000	Does not Match

I have attached a sample data. Please check column F (manual entry) for the logic.

Thank you

Query.xlsx

caltang

Using your new data, shouldn't it be:

RecordID	A Number	Voucher	Date	A Account	Amount	Result
1	1000-035324	ABJ000004623	30/6/2022	1026150-2235-AB0751-8340	-1,500,000.00	Yes
2	1000-035324	ABJ000004623	30/6/2022	3025200-2235-AB0751-8340	1,500,000.00	Yes
3	1000-035324	ABJ000004623	30/6/2022	1026150-2235-AB0751-8234	-25,000.00	Yes
4	1000-035324	ABJ000004623	30/6/2022	3025200-2235-AB0751-8234	25,000.00	Yes
5	1000-006132	ABJ000002332	30/6/2021	3025200-2235-AB0751-8234	-25,000.00	No
6	1000-035324	ABJ000004623	30/6/2022	1026150-2235-AB0751-8245	-125,000.00	Yes
6	1000-035324	ABJ000004623	30/6/2022	1026150-2235-AB0751-8245	-125,000.00	Yes
7	1000-035324	ABJ000004623	30/6/2022	3025200-2235-AB0751-8245	125,000.00	Yes
8	1000-006139	ABJ000002339	30/6/2021	3025200-2235-AB0751-8245	-45,000.00	No
9	8245-006787	ABJ000002398	30/6/2021	1026150-2235-AB0751-1000	45,000.00	No
10	8245-006787	ABJ000002395	30/6/2021	3025200-2235-AB0751-8340	-1500	No
11	8245-006787	ABJ000002395	30/6/2021	3025200-2235-AB0751-8340	-1500	No
12	1000-035324	ABJ000004623	30/6/2022	3025200-2235-AB0751-8245	125000	Yes
13	1000-006139	ABJ000002339	30/6/2021	3025200-2235-AB0751-8245	-45000	No

cterrence

Hi @caltang

Thanks you for your reply

I have updated my comments here. I have added extra columns for your reference. I have highlighted the items in bold for your review

dID	A Number	Voucher	Date	A Account	Amount	JA	LA	Result	St
1	1000-035324	ABJ000004623	30/06/2022	1026150-2235-AB0751-8340	-1,500,000.00	1000	8340	Yes	Correct
2	1000-035324	ABJ000004623	30/06/2022	3025200-2235-AB0751-8340	1,500,000.00	1000	8340	Yes	Correct
3	1000-035324	ABJ000004623	30/06/2022	1026150-2235-AB0751-8234	-25,000.00	1000	8234	Yes	Correct
4	1000-035324	ABJ000004623	30/06/2022	3025200-2235-AB0751-8234	25,000.00	1000	8234	Yes	Correct
5	1000-006132	ABJ000002332	30/06/2021	3025200-2235-AB0751-8234	-25,000.00	1000	8234	No	Correct
6	1000-035324	ABJ000004623	30/06/2022	1026150-2235-AB0751-8245	-125,000.00	1000	8245	Yes	Correct
6	1000-035324	ABJ000004623	30/06/2022	1026150-2235-AB0751-8245	-125,000.00	1000	8245	Yes	Correct
7	1000-035324	ABJ000004623	30/06/2022	3025200-2235-AB0751-8245	125,000.00	1000	8245	Yes	Correct
8	1000-006139	ABJ000002339	30/06/2021	3025200-2235-AB0751-8245	-45,000.00	1000	8245	No	Incorrect. This transactions matches. Dates, amount, JA and LA match. Do note that JA and LA is interchanged.
9	8245-006787	ABJ000002398	30/06/2021	1026150-2235-AB0751-1000	45,000.00	8245	1000	No	Incorrect. Same as above. Above transaction is related
10	8245-006787	ABJ000002395	30/06/2021	3025200-2235-AB0751-8340	-1500	8245	8340	No	Correct
11	8245-006787	ABJ000002395	30/06/2021	3025200-2235-AB0751-8340	-1500	8245	8340	No	Correct
12	1000-035324	ABJ000004623	30/06/2022	3025200-2235-AB0751-8245	125000	1000	8245	Yes	Correct
13	1000-006139	ABJ000002339	30/06/2021	3025200-2235-AB0751-8245	-45000	1000	8245	No	Correct

caltang

Okay, the LA / JA switch may cause some issues. Try this workflow and see.

For cterrence.yxzp

cterrence

Hi @caltang

It seems that the query worked successfully for most transactions, but for some transactions, even though they were only one transaction, the result erroneously displayed as "Yes"

RecordID	A Number	Date	A Account	Amount	Result
28	1000-006898	2022-03-16	1026150-2235-5240	22500000	Yes
33	1000-014065	2022-05-11	1026150-2235-5240	20300000	Yes
48	1000-022757	2022-11-22	1026150-2235-5520	15000000	Yes
1	5240-035324	2022-06-30	1026150-2235-5340	-244287842.27	Yes
2	5240-035324	2022-06-30	1026150-2235-5340	244287842.27	Yes
3	8195-035324	2022-06-30	1026150-2235-8234	-137671151.58	Yes
4	8195-035324	2022-06-30	3025200-2235-8234	137671151.58	Yes
6	8195-035324	2022-06-30	1026150-2235-8245	-83265564.78	Yes
7	8195-035324	2022-06-30	3025200-2235-8245	83265564.78	Yes