Excel COUNT IF Formula

Help Studio
, ,
1

Hello,
I have one excel of 2 columns.
Column A: Cust ID
Column B: Acc No

Now i want to apply COUNT IF for Column A. It will give value 1, 2 based on Duplicates if that value is greater than 1 then i want to write DUPLICATE FOUND before that element.
Then i want to apply COUNT IF for Column B. It will give value 1, 2 based on Duplicates if that value is greater than 1 then i want to write DUPLICATE FOUND before that element.

Plz help me with this

2

@Omkar_Shete2

Add two columns for each to write the data in excel

And then use write cell activity and give the formula as "=If(COUNTIF(A:A,A2)>1,""Duplicate Found"","""")"

Then use auto fill range to fill the formula to the bottom

Repeat same for the second columns as well

Hope this helps

Cheers

1 Like
3

Hi @Omkar_Shete2

  1. Use the “Read Range WorkBook” activity to read the Excel file and store it in a DataTable variabledtInput.
  2. Create a new DataTable to store the results say dtResults. Add the necessary columns (“Cust ID”, “Acc No”, “Duplicate Found A”, “Duplicate Found B”).
  3. Use an Assign activity to calculate the count of duplicates for Column A and Column B and add the “Duplicate Found” label:
Assign: dtInput.DefaultView.ToTable(True, "Cust ID", "Acc No", "Duplicate Found A", "Duplicate Found B")
  1. Use a For Each Row activity to loop through the dtInput DataTable.
  2. Inside the For Each Row loop, use the following activities to count duplicates for Column A and B and add the necessary information to the result DataTable:
  • Use an Assign activity to get the count of duplicates for Column A:
Assign: duplicateCountA = dtInput.AsEnumerable().Count(Function(row) row("Cust ID").ToString() = currentRow("Cust ID").ToString())
  • Use a similar Assign activity to get the count of duplicates for Column B.
Assign: duplicateCountB = dtInput.AsEnumerable().Count(Function(row) row("Acc No").ToString() = currentRow("Acc No").ToString())
  • Use an Assign activity to add the “Duplicate Found” label for Column A:
Assign: duplicateFoundA = If(duplicateCountA > 1, "DUPLICATE FOUND", "")
  • Use a similar Assign activity to add the “Duplicate Found” label for Column B.
Assign: duplicateFoundB = If(duplicateCountB > 1, "DUPLICATE FOUND", "")
  • Use an Add Data Row activity to add the row to the result DataTable:
ArrayRow: {currentRow("Cust ID"), currentRow("Acc No"), duplicateFoundA, duplicateFoundB}
  1. Use the “Write Range” activity to write the result DataTable i.e dtResults to a new Excel file.

Hope it helps!

4

Thank you. It works. Thank you so much for your quick reply

5

@Omkar_Shete2
If you find solution for your query please mark it as solution to close the loop.

Happy Automation
Regards

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.