How to extract information from 3 Excel documents? (Bank Conciliation)

Help
,
1

Hi everyone!

I have to do an activity with 3 excel documents. I show u the structure:

First Excel Sheet:

image
image761×183 3.59 KB

Second Excel Sheet:

image
image887×207 6.13 KB

Third Excel Sheet:
image

And i want to create an output document like this, where i get ID, Account Number, Description, Area Code, B.Balance, B.Due, Payment, Total (from secund sheet) and Value (from first sheet) and i search negative or positive differencies (With the subtraction of “Value” and “Total”).

image
image1068×186 5.94 KB

I was thinking in use Invoke Code after of extract data from excel with read range but i dont know how to iterate through files and columns and get that data.

Bless!

2

Firstly, you can read all of the information into 3 separate datatables and use merge datatable activity as you have common columns across each table.

Are the sheets within the same workbook or different workbooks?

If they are in the same, you can access each worksheet by increment the worksheet index and read the range into a datatable etc.

If they are different, you would simply assign them to a list and iterate through each one to add to a datatable.

Then action as mentioned at the start to merge the datatables.

2 Likes
3

I have a question with that activitie. If a sheet contains text in other rows and the table is located in other position, the activitie can do the merge?

The sheets are in differents workbooks, i will try to test. Bless!

1 Like
4

If a sheet contains text in other rows and the table is located in other position, the activitie can do the merge?

you can firstly find the cell which contains the header of the 1st column, and after read range
For example:
for each file

  1. LookUp “id” returns “B3”
  2. Read Range from “B3” returns the next datatable, you need
    and then merge datatables by conditions
5

I believe than is better option clean the file and set an order inside for than merge can be executed without errors

6

Hi @inf_L

Use read range sheet1

Use Invoke Method based on merge for dt1 and dt2 and dt3

Thanks
Ashwin S

7

Yes,

I have a question with that activitie. If a sheet contains text in other rows and the table is located in other position, the activitie can do the merge?

If there are blank columns and rows etc it may effect the merge

Cleaning the data first is a great idea. wheather you do get that done manually or if you know the input has the same ‘bad’ format every time you can automate the cleaning of the file etc.

8

I have the solution, i a few of moments i will share it

1 Like
9

Glad to hear you have a solution :slight_smile: