Compare two columns with Go To Special

In the figure below, say that you want to find any changes between column A and column D.

There are lists of products in A and D. Select A2:A9. Ctrl+Select D2:D9. Open Home, Find & Select, Go To Special. Choose Row Differences. OK.

Select the data in A2:A9 and then hold down the Ctrl key while you select the data in D2:D9.

Select, Home, Find & Select, Go To Special. Then, in the Go To Special dialog, choose Row Differences. Click OK.

Only the items in column A that do not match the items in column D are selected. Use a red font to mark these items, as shown below.

After choosing Row Differences, any items in column A that do not match column D are highlighted. Apply a red font so you can identify which have changed.

Caution

This technique works only for lists that are mostly identical. If you insert one new row near the top of the second list, causing all future rows to be offset by one row, each of those rows is marked as a row difference

Comments

Year Over Year calculation using Pivot Table

Instead of creating a formula outside of the pivot table, you can do this inside the pivot table. Start from the image with column D empty. Drag Revenue a second time to the Values area. Look in the Columns section of the Pivot Table Fields panel. You will see a tile called Values that appears below Date. Drag that tile so it is below the Date field. Your pivot table should look like this: Double-click the Sum of Revenue2 heading in D4 to display the Value Field Settings dialog. Click on the tab for Show Values As. Change the drop-down menu to % Difference From. Change the Base Field to Date. Change the Base Item to (Previous Item). Type a better name than Sum of Revenue2 - perhaps % Change. Click OK. You will have a mostly blank column D (because the pivot table can't calculate a percentage change for the first year. Right-click the D and choose Hide.

Power Point - Slide Master a very important tool

When you want all your slides to contain the same fonts and images (such as logos), you can make those changes in one place—the Slide Master, and they'll be applied to all your slides. To open Slide Master view, on the View tab, select Slide Master : The master slide is the top slide in the thumbnail pane on the left side of the window. The related layout masters appear just below the slide master (as in this picture from PowerPoint for macOS): 1 Slide master 2 Layout masters When you edit the slide master, all slides that are based on that master will contain those changes. However, the majority of changes that you make will most likely be to the layout masters related to the master. When you make changes to layout masters and the slide master in Slide Master view, other people working in your presentation (in Normal view) can’t accidentally delete or edit what you’ve done. Conversely, if you're working in Normal view and find that you'...

Split Column in Power Query

Split Column In Power Query, a column of text can be split into multiple columns and in a number of different ways to achieve the results you want. By default, the name of the new columns contains the same name as the original column with a suffix of a period (.) and a number that represents each split section from the original column. You can then rename the column. Split Column Types - Click on the link below for details Split a column by delimiter Split a column by number of characters Split a column by positions Split a column by letter case combinations Split a column by digit and non-digit combinations

Solutions for Data Science

Search This Blog