Comparing two columns in Excel sheet but not rowwise

excel

#1

Hi Everyone,

I am not good in excel and i have been given a task which i dont know how should i do it.
I have 5 columns in sheet 1 and 6 column in sheet 2. In sheet 1 there is one Column name Debit with amount written in that column it has around 800 rows and in sheet2 also there is a column name Credit.What i have to do is i suppose in sheet1 debit column first value i have taken and i need to search that value in sheet 2 credit column if that amount is present only once in sheet 2 credit column then i have to write all the data of sheet 1 of that row in separate sheet and if that amount has multiple count in credit column of sheet 2 or zero count in sheet2 credit column then again that row jn sheet 1 i have to copy in separate sheet.


How to write if condition for row A and row B is true
#2

@aamir, Lets make few assumption before we proceed,

  1. Read and take SHEET 1 - 5 Columns as dt1
  2. Read and take SHEET 2 - 6 Columns as dt2
  3. Comparison is based only on first cell value of Debit column of datatable dt1. (Correct me if am wrong)
  4. If output has single count, will copy that row to dtSingle
    Initialise dtSingle and assign this to dt1.Clone
  5. If output has zero or multiple count, will copy that row to dtZeroOrMultiple
    Initialise dtZeroOrMultiple and assign this to dt1.Clone

Since classifying the sheet is based on Matching Count, we shall get the count using the query mentioned below,

ASSIGN activity

int Count = (From row in dt2.Select() Where row(“Credit”).ToString.Equals(dt1(0)(“Debit”).ToString) Select row).Count()

Based on the value of intCount assign the values,

If intCount =1, Then in Add Data Row activity use dt1(0) in DataRow and dtSingle Else in Add Data Row activity use dt1(0) in DataRow and dtZeroOrMultiple

Finally write this dtsingle and dtZeroOrMultiple in different sheets

Regards,
Dominic :slight_smile:


#3

Hi Dominic,

Thanks for your reply. Should i share the file for better understand or should i share the screenshots of the file.


#4

@aamir, Yeah ! That would be fine. Was my understanding correct ?

Regards,
Dominic :slight_smile:


#5

Sheet2


#6

Hi,

i have share sheet1 and sheet 2 with you.
Whta i have to do is from sheet1 Debit column i have to extract value one by one and then check if that same value is present in sheet2 of Credit column and only one count of that value if present then i have to copy the entire column of sheet one and paste in another sheet lets say conciled. and if that value from debit column of sheet1 doesnt match or if that value from debit column has multiple counts then again that rows from sheet1 should be copied and pasted in new sheet suppose unreconcile.


#7

Hi Dominic,

It would be more helpful to me if you can create the same in a workflow that will be very easy for me for understanding.
Thanks for your help.


#8

Hi Dominic,

The first picture is of sheet1 and second picture of sheet2.
Please let me know if you want to know anything else.


#9

@aamir, Yes I got it. Thanks. You need to do the same step for all the values in debit column (not only for first column) am I Right ?

Regards,
Dominic :slight_smile:


#10

Yes Dominic i have to check each and every value that is under Debit column and have to check if that value is there in Credit column of sheet2 or not. if it has 1 count then it all the rows of that matching debit amount should go to another excel sheet if and if it has either one count or more than 1 then again it should copy that row from sheet1 and send it to another sheet like unreconcile.


#11

Hi Dominic,

Any update regarding it?


#12

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)


#13

Yes Sravya


#14

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


#15

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


#16

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.


#17

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


#18

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


#19

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


#20

@aamir, @priyanka.yadav18 ,

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

Regards,
Dominic :slight_smile: