Comparing two columns in Excel sheet but not rowwise

Help
21

@Dominic
Yes. It will definetly help.

22

Hi,

can anyone explain me the meaning of
(From rowsheet2 in DTSheet2.Select() Where rowsheet2(“Credit”).ToString.Equals(DTSheet1(index)(“Debit”).ToString) Select rowsheet2).Count()

and i can see that there is a datarow named Matched Row its in assign activity but in write range i cannot see where its getting used or where this matched Row is getting used.
Rest everything i understood.

23

Hi Dominic,

Priyanka has created a workflow but there is a small issue could you please check and verify the same so that i can implement that logic.

24

Hi Priyanka,

Thanks a lot for your help.
Thank you so much.

25

@aamir, Sure I will. In the mean time requesting you to follow my latest post which is simpler than former one.

Regards,
Dominic :slight_smile:

26

@aamir, Yes, MatchedRow hasn’t been used there.

Follow this → Comparing two columns in Excel sheet but not rowwise - #17 by Dominic

Regards,
Dominic :slight_smile:

27

Hi Dominic,

i understood your code but a small doubt
Datatable dtSingle = (From sheet1 In dtSheet1.Select
Where (From sheet2 In dtSheet2.Select
Where sheet1(“Debit”).ToString.Equals(sheet2(“Credit”).ToString) Select sheet2).Count =1
Select sheet1).CopyToDatatable()

Datatable dtZeroOrMultiple = (From sheet1 In dtSheet1.Select
Where (From sheet2 In dtSheet2.Select
Where sheet1(“Debit”).ToString.Equals(sheet2(“Credit”).ToString) Select sheet2).Count <> 1
Select sheet1).CopyToDatatable()
how can i use sheet1 name that is the name of my excel sheet i.e sheet1 and sheet2.
and i think you have used read range to read sheet1 and sheet2 and saved in two different datatable dtsheet1 and dtsheet2.
But i find in your query its written sheet1. whats that.
Plaese explain

28

@aamir,

You are exactly right !

Its just an object instance to refer each row of that datatable. You can name it as per your wish.

Regards,
Dominic :slight_smile:

29

Ok you you mean to say that instead of sheet1 i can write anything it doesnt have any link with my sheet1 and sheet2 in my excel file?
Right? Please correct me if i am wrong since i am not that techie

30

@aamir, Exactly Right !

Me too. No worries !

Regards,
Dominic :slight_smile:

31

Thanks Dominic,

last thing is this correct way to write this
“=COUNTIF(Sheet2!G2:”+LedgerFinalCount.ToString+“,Sheet1!F”+DebitRowInitial.ToString+“)”
because i am getting an error.
while if i am putting the value manually then its working

32

am not sure about this

Regards,
Dominic :slight_smile:

33

Hi Dominic,

The query which you gave me it ran thanks a lot for this but one query didnt ran as i expected i am pasting you that query
Datatable dtSingle = (From sheet1 In dtSheet1.Select
Where (From sheet2 In dtSheet2.Select
Where sheet1(“Debit”).ToString.Equals(sheet2(“Credit”).ToString) Select sheet2).Count =1
Select sheet1).CopyToDatatable()
I have total of 481 rows in sheet1 that is 481 rows in debit column and around 10995 rows under credit in sheet2 but after running the query and doing write range of dtsingle its pasting around 10995 rows but i hvae 481 debit rows.

34

Hi Dominic,

Its Working thanks a lot. I re-ran the query it worked nicely but truely speaking you are a genius.
Can you please let me know from where i can understand this query because although it worked but i was not able to understand the above query please suggest some site where i can go and understand this query.
Thanks for everything.

35

Hi Priyanka,

Thanks a lot for your help, you are always very hardworking and helpful.
and last thing priyanka can you please suggest me from where should i study to understand that query which Dominic has written. Any site somewhere if i study then i should understand that query because this query can help me in future for many things.
Thanks again in Advance.

36

I will search and will let you know.
Thanks for the humble words aamir :slight_smile:

37

https://forum.uipath.com/t/clone-the-data-table-structure/20206/2?u=sravya

38

Hi Dominic,

Just a small help in the previous scenario where from sheet1 debit column we were checking credit column if that credit contains only one exact match then we are copying all the rows of all the column from sheet1 and sending it at one place. i also want sheet2 matching rows all column what will be the query and also those rows which is not matching or matching is more than one count. i will send these two in different sheets as Ledger Reconcile and Ledger Unreconsile.
Thank you

39

@aamir, Made a small change (Bold Letters)

dtSheet1 - your sheet1
dtSheet2 - your sheet2

In Assign

Datatable dtLedgerSingle = (From sheet2 In dtSheet2.Select
Where (From sheet1 In dtSheet1.Select
Where sheet2(“Credit”).ToString.Equals(sheet1(“Debit”).ToString) Select sheet1).Count =1
Select sheet2).CopyToDatatable()

Datatable dtLedgerZeroOrMultiple = (From sheet2 In dtSheet2.Select
Where (From sheet1 In dtSheet1.Select
Where sheet2(“Credit”).ToString.Equals(sheet1(“Debit”).ToString) Select sheet1).Count <>1
Select sheet2).CopyToDatatable()

Regards,
Dominic :slight_smile:

1 Like
40

Thank you so much Dominic for your help. is this query called linq query??
Because i searched a lot for this query in google exactly what it is and till now what i thin k it is a linq query. In the meanwhile let me adjust this query in my code and will let you know. Thanks once again for helping me.