Excel-Buzz

This post is in continuation to First one i.e. Restricting User to enter Duplicate Values using Data Validation.  As the previous Duplicate Restrictor has some limitations. So I came up with a rather new way ( Conditional Formatting ) to achieve it. This time, it will highlight the duplicate cell, that is too, except First Occurrence. Here is the Conditional Formatting formula:- =COUNTIF($C$1:$C1,$C1)>1  Here are the steps:- 1. Select the Target Column say C ( in the attached example ) 2. Go to Home Tab--> Conditional Formatting ---> New Rule --> Formula 3. Type the above formula there. 4. Select the formatting for highlighting the duplicates found like filing colour in cell or colouring Font.     ( I have used Green Colour in the attached example ) 5.  Click OK. Explanation:- The Target column which has been selected, is the Range on which Conditional formatting applies. Excel interprates above formula in context of Each c...

Hello Everyone, Some times we come across situations where we want to restrict duplicate values to be entered. For example in the attached file, we want to avoid entering duplicate invoice numbers. It can be possible with DATA VALIDATION Custom Formula:- =COUNTIF($C:$C,$C1)=1  Here are the steps:- 1. Select the target column fully say Col C. 2. Click Data tab in the Ribbon, select Data validation. 3. In the Drop down, select Custom. 4. Enter the Formula and click Ok. It will give an error message while entering first duplicate. Explanation:- The Target column which has been selected, is the Range on which Data validation applies. Excel interprates above formula in context of Each cell in the Target Range and decides whether that cell has Unique or Duplicate entry. The $C1 parameter of Countif formula is a Row relative range. So when excel moves to next cell downwards in the Target Range, $C1 changes to $C2 and so on, for each next cell. When user puts ...

Hi one and all, Sometimes it becomes difficult to concatenate many results in an array.  For Example, we use  =Index( Table, Small( if( Array = Criteria, Row( Array ) ), Nth Small ), Column No.)  constructions, we often want all values in the resultant array to be in one cell delimited by "," or any other delimiter. But we are unable to do this, because there is no such concatenation function provided by the Microsoft. So I have come up with a UDF that will do the needful. Here is the syntax. =JoinR( Range_OR_Array, Delimiter As String ) =JoinC( Range_OR_Array, Delimiter As String )