Microsoft Office 2007 applications shown on Windows 10 clockwise from top left Excel, Word, OneNote, PowerPoint these four programs make up the Home and Student. I-AYYY9zsoE/hqdefault.jpg' alt='Formula Of Ms Excel 2007 With Example Pdf' title='Formula Of Ms Excel 2007 With Example Pdf' />A reader writes From reading the comments on AAM, it seems many of your readers are Excel gurus. I use Excel a lot at work but feel like Im only scratchi. Extract a list of duplicates from a column. The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed in column C. Update 2. 01. 7 0. New regular formula in cell C2 LOOKUP2, 1COUNTIFE1 E1, A2 A2. COUNTIFA2 A2. A2 A2. A2 A2. This video explains how to use the formula and how it works. If you want a list sorted from A to Z, read this article This post explains how to extract duplicate values using a condition You can also use more than one condition, this article shows you how Filter a unique distinct list Array formula in C2 INDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. How to enter an array formula. Copy Ctrl c above formula. Double click on cell C2. Anubis 2 Pc Download. Paste Ctrl v to cell C2. Press and hold CTRL SHIFT simulatneously. Press Enter once. Release all keys. Your formula now looks like this arrayformulaDont enter the curly brackets, they appear automatically. How to copy formula. Copy cell C2 and paste it to cells below as far as needed. Remove num errors IFERRORINDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. Copy cell C2 and paste it down to D2. Learn more IFERROR function. The IFERROR function was introduced in excel 2. In previous excel versions you could check for errors with the ISERROR Comments0. Filed in category Excel. Earlier Excel versions, array formula in C2 IFISERRORINDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. INDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. How this array formula works. Opera Mail Linux Download'>Opera Mail Linux Download. Step 1 Show a duplicate value only onceINDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. COUNTIFC1 C1, A2 A2. C1 C1 to a range. When you copy a cell reference like this the cell reference expands. COUNTIFC1 C1, A2 A2. C2 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0Step 2 Filter values in A2 A2. INDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. COUNTIFA2 A2. A2 A2. The returning array is 2, 1, 1, 2, 1, 1, 2, 2, 1, 2, 2, 1, 2, 1, 1, 2, 1, 1, 1, 1. COUNTIFA2 A2. A2 A2. TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE. IFCOUNTIFA2 A2. A2 A2. Step 3 Calculate arrays combinedINDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. Step 4 Identify duplicatesINDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. MATCH0, 1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0 is 2. Match returns the relative position of an item in an array that matches a specified value. Step 5 Return duplicatesINDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. INDEXA2 A2. Federer, Roger. Index returns a value or reference of the cell at the intersection of a particular row and column, in a given range. Final thoughts. When formula in c. Example. The formula in c. INDEXA2 A2. MATCH0, COUNTIFC1 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. Then copy the formula to C3. The formula references changes INDEXA2 A2. MATCH0, COUNTIFC2 C1, A2 A2. IFCOUNTIFA2 A2. A2 A2. Read more about absolute and relative cell references. This makes it possible to avoid previous cell values C2 and only calculate remaining values. Download excel sample file for this tutorial. Excel 2. 00. 7 Workbook.