4/2/2023 0 Comments Find duplicate excel formulaThere’s no one feature or technique that will find duplicates in every case. Duplicates can occur within a single column, across multiple columns or complete records. That’s because a duplicate is subjective to the context of its related data. In the duplicate world, definition means everything. For more info, visit our Terms of Use page. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. Try changing the name entered in E3 and the relevant sales values will appear as below.You'll need more than one trick up your sleeve to find duplicates in Microsoft Excel. The formula needs to be entered as an array formula before it can be copied down. If the count of the number of rows exceeds the number of times Ben’s name appears the IF returns an empty text string. The IF function’s logical test compares an incremented value (a count of the number rows the formula has been copied to) with a count of the number of times Ben’s name appears in the Name column. The final step is to avoid the #NUM! error which occurs when too many sales results are returned for the specified salesperson. STEP 5 – Return the Correct Number of Sales Values =INDEX(Sales,SMALL(IF(Sales=E$3,ROW(Sales)-3),ROWS(D4:D$4)))Ĭopying the formula down as an array formula your table should now look like this. We need the INDEX function to return values from the Sales Column in the same vertical position as each occurrence of Ben’s name in the Name column. STEP 4 – Return the Sales Values for the Specified Sales Person As an array formula brace brackets will appear around it. NB: The formula only works as an array formula so you need to use CTRL SHIFT ENTER to confirm the formula and then copy it down. If you copy the formula down the table you will see it returns the correct row numbers for Ben. So the formula at this stage will look like this: The data set or array is returned by our current formula, the k-th smallest value can be specified with a simple incrementer – ROWS(D4:D$4) which when copied down will return 1, 2, 3 etc. SMALL returns the k-th smallest value in a data set. We can use the SMALL function to achieve this. Now we know which rows Ben appears in we need each row number to be returned independently as we copy the formula down the second table. STEP 3 – Return Each Row Number As the Formula is Copied Down So the IF function is only performing the ROW calciulation where the logical test is met – as in the name is equal to Ben. The result is again 1 but using our F9 trick we can see what the formula is actually returning: The name Ben is in cell E3 in our example. The second step is to get Excel to only return row numbers where the specified sales person’s name appears. STEP 2 – Only Return the Row Numbers for the Sales Person Specified You will need to undo this (CTRL Z) to get back to the original formula. At the moment the row numbers are hard-coded into your formula as a result of using the F9 trick. The formula is in fact returning each row number. The F9 trick requires you to select the text of the formula and then press the F9 function key on your keyboard. However, as we have asked the ROW function to return the row number of all the cells in the Name column we have to use the F9 trick to see the true result. The -3 has been included at the end of the formula as the table starts in row 4 of the worksheet. Normally ROW returns the row number for a single cell: we have specified the entire Name column. We can use the ROW function to achieve this. The first step is to return each row number within the sales table. STEP 1 – Return Each Row Number in our Sales Table The steps involved in putting this formula together may not seem to make sense initially but stick with it and you will see how everything fits together. To name a table, click on the DESIGN tab on Ribbon: the Table Name option is on the far left of the Ribbon as shown below. You can do this fairly simply by clicking anywhere in it and then using the shortcut key CTRL T. The first table has been converted into an Excel Table. The formula returns 5 results for Ben, for Fred it would return 2 and Greg, 1. When we change that name at the top of the second table the formula will return the sales values for that person. In our example we have a sales table and a second table listing sales for a specific salesperson. Click here to Enrol on the Ultimate Excel VBA CourseĪll purchases help to support this blog - thanks!
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |