You are currently viewing How to match and extract different columns in excel

How to match and extract different columns in excel

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.

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.

7. Within the formula field, enter this 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 looks 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.

Simplified steps.

1. Press CTRL -M and select columns to be extracted in the data range.

2. Fill input and output and output range in the dialog box that will pop up.

3. Click OK.(ignore the code degree and code type)

4. Highlight the 1st three columns,→ hold down the shift key after clicking the 1st column and then click the     3rd column. After that, click adds column button.

5. You can add the other columns while holding down the CTRL key and clicking the columns you want.

6. Click done and close the dialog box.

That is how we match and extract columns. You can add them to the other spreadsheet. The same can happen with ROWS. But with Rows extraction, a lot of people prefer VLOOKUPits simplified, and it's easy. VLOOKUPS is can also be used to extract columns and make comparisons.

While doing any of the steps, you need to look at the Excel workbook. To check, click the Data "button" it will be on the lower right-hand side of the dialog box. Do not change anything in the workbook.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.