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.
@aamir, Lets make few assumption before we proceed,
Read and take SHEET 1 - 5 Columns as dt1
Read and take SHEET 2 - 6 Columns as dt2
Comparison is based only on first cell value of Debit column of datatable dt1. (Correct me if am wrong)
If output has single count, will copy that row to dtSingle Initialise dtSingle and assign this to dt1.Clone
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
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.
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.
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)
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
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
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()
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)
ashokkarale
Akash_Javalekar1
KezayAlperen
Yoichi
naveen.s