Comparing two columns in Excel sheet but not rowwise

Hello aamir
is your question is this
(suppose dt_sheet1 has table one and dt_sheet2 has table two
then for every value of debit in table 1 we are checking credit matches in table 2
if the count of credit values of table 2 existing with debit value of table 1 is
one then extract that row from table1(dt_sheet1) and place in new sheet output_sheet1 or if count is zero or greater than one then put that row of table1(dt_sheet1) into output_sheet2)

Yes Sravya

hello aamir
I took table1 as dt1 and table 2 as dt2
using build data table created two empty tables for (count=1 let outtable1) and other (count is zero or more than one data let outtable2) like table1 structure
next in for each row of table1 kept the assign activity like dominic said and if else block with count=1 condition if yes added add data row to outtable1 as input if no then add data row to outtable2.
Finally under for each row kept the write range activities for the two sheets

Hi,

Thanks sravya. one more help if this line has som eerror
“=COUNTIF(Sheet2!G2:G+LedgerFinalCount.ToString+,Sheet1!F+DebitRowInitial.ToString)”
Here LedgerFinalCount is the variable where i am calculation the range of G column and DebitRowInitial is also a variable it keeps on incrementing so how to use these variables inside the string under countif

Hi Sravya,

i understood your logic and dominic also just a small help. using build datatable activity in my uipath should i feed the name of the column manually or can you please show how to fill the column name in build datatable automatically and the logic of counting rest i understood how to add datatable.

@aamir, Sorry. Was quite busy .Follow this steps as I couldn’t upload xaml here.

dtSheet1 - your sheet1
dtSheet2 - your sheet2

In Assign

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()

Regards,
Dominic :slight_smile:

@Dominic , @aamir, @Sravya

I have tried to put the dominic code into UiPath statements.
But Could not succeed completely.
PFA the zip.
with demo excel sheet.AamirQuery.zip (14.5 KB)

In build data table I do it manually.I don’t know if any methods exist.

@aamir, @priyanka.yadav18 ,

Hope my last post is a concise solution. Please revert if you have any queries.

Regards,
Dominic :slight_smile:

@Dominic
Yes. It will definetly help.

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.

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.

Hi Priyanka,

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

@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:

@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:

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

@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:

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

@aamir, Exactly Right !

Me too. No worries !

Regards,
Dominic :slight_smile:

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