# Excel COUNT IF Formula

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

@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
1. Use the â€śRead Range WorkBookâ€ť activity to read the Excel file and store it in a DataTable variable`dtInput`.
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!

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

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

Happy Automation
Regards

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