Hi All, I have a scenario where if two of the column having the same set of data row then Bot will look for another column(LC Tax) then if same amount of positive and negative are there then we have remove those records. I have attached file will input and output. Please help me out how to do this using data table or any other way.

US40_US00_may_2019_Monthly.xlsx (9.5 KB)

# How to remove positive and negative from a data table

Buddy @Roboskun

Its resolved buddy… @Roboskun

Here is your xaml

comparedd.zip (12.1 KB)

Kindly try this and let know buddy

Cheers @Roboskun

Hi @Palaniyappan, It’s not working other scenarios. Basically, we have to remove the positive and negative of values from the sheet. If 2 negative values (let’s say -0.42) and 3 positives are there then it should cancel it out 2 positive and 2 negatives with the outcome of 1 positive. I have attached files for reference.US40_US00_may_2019_Monthly.xlsx (10.6 KB)

Hi @Roboskun

Let me see here, are you asking for a formula where all negatives and positives of a ‘column’ should come out as result in a single row or is it something else. I see that you have only three rows in your excel sheet, could you reference that and give an example of what you need so that the requirement is understood better…

Ref:

With the info you have given what @Palaniyappan has done seems right to me

Regards

@Raghavendraprasad yes that’s correct… My requirement is that i am having 40k records in excel file and there is some validation i have to apply. The validation is that if “Business partner” and “Tax Jur.” values are same then i have to look for “LC Tax” column, if same +ve and -ve values are there then it has remove from the records. Lets say we have 3 +ve and 2 -ves of same values then it has to remove 4 rows of 2+ve and 2 -ves. Let me give another scenario if 4 -ves and 1 +ve of same values are then bot have to remove 2 rows of one +ve and one -ve and out come has to come as 3 -ve rows. Like this kind of scenario i have. Where @Palaniyappan solution is not working and its working only for 2 -ve and 1 +ve. I have attached new updated input sheet for reference.

Well @Roboskun,

Sorry i don’t see your new reference sheet. And please explain your requirement using the image I cropped form your sheet

Thanks

Well, @Roboskun

I am referring tot he excel file in your question and conditions you have said

The validation is that if “Business partner” and “Tax Jur.” values are same

From assessing the pattern both columns seem to carry different type of data (Business partner is a name/string and tax Jur. is always going to be a number {correct me if I am wrong}) so it will always be unequal.

So I needed you to be specific by referring to the document. Specify whter you want the rows to be compared, or column values and the calculation details with example that ‘cites’ this document image.

@Raghavendraprasad, see in Business Partner has the same name " CARIBBEAN NATURAL PRODUC" and Tax Jur. has 3400000000 of same records where all 5 rows has “LC Tax” column data of 2 -ve values of -0.42 and 3 positive values of 0.42. So we have to filter it out of 2 positive and 2 negative as after sum its zero and left one row of one +ve value. Same we have to check for all other rows as well. Please let me know if you have any doubt.

Ah…

Let me get this:

- The image has 4 rows with one header so basically three rows.
- The rows are to be compared with each other
- Te Columns Business partner, tax jur and LC Tax are to be compared
- If two rows with the same values in the above mentioned columns are present then they are to be deleted?
- What has it got to do with the first row that is positive 0.42 under LC Tax?

I don’t understand your statement of

5 rows has “LC Tax” column data of 2 -ve values of -0.42 and 3 positive values of 0.42. So we have to filter it out of 2 positive and 2 negative as after sum its zero and left one row of one +ve value.

I think we both are referring to two different files please upload whichever you are using to explain the logic and be more descriptive or mention the values like I did above and mention what output you need.

Regards

@Raghavendraprasad The image you are referring that’s the output of input file. If you see the file which i have attached has 2 sheets one is input and one is Output. Please look into input sheet.

Well,

@Roboskun rookie user wanted t reply to this hence his solution I am posting it.

Credits : @NishantRastogi BlankProcess (1).zip (26.0 KB)

File paths are hard-coded and use the output data table in your write range activity.

Regards

PS : If you feel like the output data table does not come as expected then provide a bigger data set to test scenarios.