It is easy to extract and match multiple columns in excel, and we will talk about how we can do this swiftly and effortlessly. To fetch data from given cells in a column, we will need a combination of INDEX function, SMALL function, COLUMNS function, IF function, Row function to create an excel formula. There are many formulas people use nowadays, like VLOOKUP, and it helps a lot when doing it for row data.
How to match columns in excel
1. Select the whole data set. Select Ctrl+A to highlight.
2. And then click the Home tab.
3. Go to styles group and select conditional formatting.
4. Levitate the cursor on the highlighted cell rules option.
5. Select duplicate values.
6. Within the duplicate values, the dialog box ensures the duplicate is selected.
Simple Way to match between two columns using Formula:
7. Enter the formula: $A1=$B1.
8. Click the Format button and ensure the format you want to apply is specified to the matching cells.
10. Then hit OK.
This will highlight all cells with the same names in each row.
Extracting Columns
Extraction of columns is mostly used in real statistic data analysis tools. It's useful when data for the random variable are used in a particular analysis. Let's look at how to extract poverty, infant mort, university, crime, doctors, and unemployment from data range A1:G10 in the image.
Press ctrl-m and then select the extract column from the data range option. Fill in the dialog box that will appear with the input range and output range. And then hit OK. For now, Ignore the code degree and type. The dialog box will have changes. Highlight the 1st three columns by clicking on the state column and the hold down the shift key, and click the infant mort. After that, click adds column button. The 1st three columns will be copied to the output. You can copy other columns by holding down CTRL and clicking the columns. Then click done and close the dialog box.
Extracting Columns to A new Sheet using VBA code
If you are working with hundreds of columns you might need to extract only a few of them to another sheet. There are two ways that you can do this ie manually or using VBA macros.
Let us use the following random data as an example. I will extract the first 4 columns using VBA
Copy and paste the following macro code by pressing Alt+F11
Public Sub extractCol()
Set range1 = Range("A:D, BI:BI, BQ:BQ,CL:CL,CM:CN,CT:CT,DB:DB")
range1.Copy
Set newbook = Workbooks.Add
ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub
This code selects the columns that you want to extract and copies it. Now you need to select a destination that you want to extract the data to.
Pick your destination and press Enter. Your columns are now extracted successfully. You can modify the VBA code to extract data from your preferred range of columns
