Excel

[Idea]How to compare values of 2 columns?

• [Solutions]Use formula to return match/ un-match results in a separate column
  • [Techniques]Comparison

    [js]=IF(A2=B2), "Match", "Unmatch")[/js]

  • [Techniques]Case sensitive comparison

    [js]=IF(EXACT(A2,B2), "Match", "Unmatch")[/js]

• [Solutions]Highlight match/ un-match cells

• Compare value with multiple conditions
  [sql]
  IF(AND(A2=”aaaa”, B2=”bbbb”, C2 =”cccc”, “MATCHED”, “NOT MATCHED”)
  [/sql]

How to find if cell exists in a range of data or not

[sql]
VLOOKUP([cell], [range of data], [column number in range of data])
[/sql]
First column is always search data range

How to force Excel to open a UTF-8 csv file?

• Open Microsoft Excel 2007.
• Click on the Data menu bar option.
• Click on the From Text icon.
• Navigate to the location of the file that you want to import. Click on the filename and then click on the Import button. The Text Import Wizard – Step 1 or 3 window will now appear on the screen.
• Choose the file type that best describes your data – Delimited or Fixed Width.
• Choose 65001: Unicode (UTF-8) from the drop-down list that appears next to File origin.
• Click on the Next button to display the Text Import Wizard – Step 2 or 3 window.

How to check if value of this cell exists on other column or not

[sql]
=MATCH([this cell], [other column array], 0)>0
[/sql]

Paint color

• Select “Home” > “Conditional Formatting” > “New Rule” > “Use Formula to determine…”
• Input formula
• Select “Format”

How to rotate data from rows to columns or vice versa

Ctrl-C to Copy data

Right click > Paste Options: Transpose

How to filter for unique values or remove duplicate values

• Select the range of cells, or make sure the active cell is in a table.
• On the Data tab, in the Sort & Filter group, click Advanced.
• To filter the range of cells or table in place, click Filter the list, in-place.
• Select the Unique records only check box, and click OK.