Match cell value in different rows/columns

Question

Hi team,

The following is the sample of data I'm trying to process.

This is a balance sheet so for different reports it will have different no. of accounts hence more line items before the asset and liabilities totals. As the line numbers suggest, upto 13 lines before asset total and from 15 to 21 before liabilities total. I have color coded which cells I need to match in pairs.

What i want to achieve-

* Check Assets total from line 14 is equal to Line 22. Both beginning and ending columns. Different no. of line items between these might be present in different excel reports.
* The ones highlighted in blue should be matched against a text input with just one field with a number like 15,886,003.
* Line 21 beginning amount should match the ending amount in last column with description "Ending Capital M2".

RolandSchubert · Accepted Answer

Hi @Jaswinder ,

for your first requirement, I would suggest to filter out the lines containing "Total Assets" and "Total Liabilities and Partner Capital" - this can be done using a Filter tool with a condition like

[Schedule L Descption] CONTAINS 'Total Assets' OR [Schedule L Descption] CONTAINS 'Total Liabilities and Partner Capital'

The result should be exactly two rows. The comparison could be done using two Multi-Row Formula tools (calculating the difference between the lines for Beginning and Closing Balance) - other options would be using Transpose/Crosstab and a Formula tool to "re-organize" data and calculate the difference.

The second could be solved by filtering the data to compare (similar process as above) and using Append Fields tool to append the value from Text Input tool to all rows. The difference can be easily calculated.

The third requirement works similar to the first one, filter the respective rows and compare using a Multi-Row Formula tool.

What do you think?

Best,

Roland