Need help writing forumla. Please see below I need help writing a formula to pul

Question

Need help writing forumla. Please see below

I need help writing a formula to pull out and delete duplicates I would like to write a formula that if the Client Name, Customer Name, DCNUM, Date of Services, Billed Charges, and Remit colums are the same: to delete that column: or highlight it red so that I can go in and delete it. I only want it to highlight red or delete if all columns are the exact same: for example, if Acct, customer name, and billed charges are all the same, but the Remit amount is different: then it's not a duplicate. Above, please find an example. We see that rows 2 and 3 are almost identical, excep that the remit amount is different. In this case, it should not show up as a duplicate. We see that rows 2 and 5 are exact duplicates: and in this case, I wan't this row to be flagged so that we can delete it.

Explanation / Answer

Step 1: Create a key from the values of column A upto column I.

in cell (j2) = a1&"_"&b1&"_"&c1&"_"&d1&"_"&e1&"_"&f1&"_"&g1&"_"&h1&"_"&i1

Step 2: calculate the frequency of each of these keys

in cell (k2) = countif(j$2:j$7,j2)

Step 3: use conditional formatting for the values which are greater than 1 in cell (k2) and mark them red

Step 4: Either delete all the row with j-column values as >1 or use red colors.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.