powerquery
Excel: How to gather unique values in one column that are associated with duplicates in another column?
Let me add two additional methods to the answer by @Harun24HR. Both options assume you don’t have headers as per your sample data. Option 1) : Dynamic Array Functions When one has access to dynamic array functions you may use the following: In C1: =UNIQUE(A1:A17) This UNIQUE function will spill an array of unique values … Read more
How can I reference a cell’s value in PowerQuery
This can be achieved using a named range and a custom function in PowerQuery: Name the cell you need to refer (type in a name into the file left of the formula bar) – e.g. SourceFile Insert a new blank PowerQuery query (PowerQuery ribbon -> From other sources) In the PowerQuery editor, go to View … Read more
PowerQuery: How can I concatenate grouped values?
You can use the GUI to do it this way: Select your LetterColumn and then Transform / GroupBy: Select Add Column / Custom Column: Click opposing arrows at top right of new Custom column to Extract Values from new Custom column: Remove AllData column.
Excel UNIQUE Across Columns
There may be a better approach, but here is one using TEXTJOIN and FILTERXML to create an array that you can call UNIQUE on: =SORT(UNIQUE(FILTERXML(“<t><s>”&TEXTJOIN(“</s><s>”,TRUE,A1:C6)&”</s></t>”,”//s”)))
Convert matrix to 3-column table (‘reverse pivot’, ‘unpivot’, ‘flatten’, ‘normalize’)
To “reverse pivot”, “unpivot” or “flatten”: For Excel 2003: Activate any cell in your summary table and choose Data – PivotTable and PivotChart Report: For later versions access the Wizard with Alt+D, P. For Excel for Mac 2011, it’s ⌘+Alt+P (See here). Select Multiple consolidation ranges and click Next. In “Step 2a of 3”, choose … Read more