Posts Tagged ‘microsoft’

Utility Tool – ColumnArranger v1.0

 

Hello Readers

Today I have got another interesting utility – ColumnArranger. It is used to automatically re-arrange the order of columns in MS Excel to a predefined format.

For example, you receive a report for Accounts-Opportunity in MS Excel.
Provided sequence of columns – OPTY_NUM, ACCOUNT_ID, CITY, POSTAL_CODE, COUNTRY, STATE, STATUS.
Expected sequence – ACCOUNT, COUNTRY, STATE, STATUS, CURRENCY, POSTAL_CODE, OPTY_NUM.

In this scenario, ColumnArranger utility comes handy. Instead of re-arranging columns manually, only specify the column header in desired sequence and run an embedded macro.

 

Origin Of The Tool

During EIM Load, we used to receive data extracts from Data Conversion Team. They designed their own SQL and used to send the data in MS Excel sheet. And in parallel, my team was working on the design of EIM Loads and developed the CTL files with data order not in sync with select part of SQL statements. Therefore, data in received sheets could not be fed to the EIM loads directly.

At this point, I developed ColumnArranger tool. Instead of manually ordering the columns in Excel, I utilized it eliminating the manual efforts.

 

Presentation And User Guide

For additional details and user guide, please refer this document >> ColumnArranger – Presentation

 

Tool Setup

To download this tool, click here >> ColumnArranger – Setup

 

MS Excel – Finding Repeated Values

 

Scenario

Sometimes it is required to verify a particular column for repeated values in MS Excel sheet. It would be similar to the use of distinct clause in SQL statement.

 

Solution

We can use CountIF function to determine repeated values as shown below:

 

The syntax is: =CountIF (Range, Value to search for) > 1

where Range >> Specified as $A$2:$A$20. It means all cells from Row: 2 to 20 and Column: A.

Value to search for >> Specified as A2. It means value in the cell – A2 should be looked for in above range.

 

Press Enter. Then drag the value of cell B2 to all other cells below it. System will automatically update the formula and value of the cell. All cells with value = TRUE signifies that corresponding column A values has repetitions in the list.