![]() ![]() And then click OK, another prompt box will appear to let you choose a place to put the result, see screenshot:ĥ. Then press F5 to run this code, and a prompt box will pop out to remind you select the data range that you want to use. Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8) Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8) Set dt = CreateObject("Scripting.Dictionary") VBA: Extract unique values from multiple columns Sub Uniquedata() Click Insert > Module, and paste the following code in the Module Window. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.Ģ. With the following VBA code, you can also extract the unique values from multiple columns.ġ. Then check the field Value or drag the Value to the Rows label, now you will get the unique values from the multiple columns as follows:Įxtract unique values from multiple columns with VBA code At last, click Finish to complete the wizard, and a pivot table has been created in current worksheet, then uncheck all the fields from the Choose fields to add to report section, see screenshot:ħ. After selecting the data range, continue click Next, in the wizard step 3, choose where you want to put the PivotTable report as you like.Ħ. Go on clicking Next button, click to select the data range which including the left new column of cells, then click Add button to add the data range to the All ranges list box, see screenshot:ĥ. Then click Next button, check Create a single page field for me option in wizard step2, see screenshot:Ĥ. Click one cell in your data, and press Alt+D keys, then press P key immediately to open the PivotTable and PivotChart Wizard, choose Multiple consolidation ranges in the wizard step1, see screenshot:ģ. At first, please insert one new blank column at the left of your data, in this example, I will insert column A beside the original data.Ģ. If you are familiar with the pivot table, you can easily extract the unique values form multiple columns with following steps:ġ. See screenshot:Įxtract unique values from multiple columns with Pivot Table Then press Shift + Ctrl + Enter keys together, and then drag the fill handle to extract the unique values until blank cells appear. Note: In the above formula, A2:C9 indicates the range of cells which you want to extract the unique values, E1:E1 is the first cell of the column you want to place the result, $2:$9 stands fro the rows contain the cells you want to use, and $A:$C indicates the columns contain the cells you want to use.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |