Monday, 16 July 2018

Excel Column Name or Index For Given Number

Links to this post
I came across this problem to be solved on an online test which will be useful for many others I am posting here.

Problem Statement:

Excel Problem Hacker Earth
Excel Problem
We need to find the column names based on the given number. Assume that your excel sheet cells are given numbers in above order and your columns spread from A to ZZ as shown in the image.

Sample case:

string[] columnNames = new string[702];
int count = 26;
for (int i = 0; i < alphabets.Length; i++) {
columnNames[i] = alphabets[i].ToString ();
for (int i = 0; i < 26; i++) {
for (int j = 0; j < 26; j++) {
columnNames[count++] = alphabets[i].ToString () + alphabets[j].ToString ();

Console.WriteLine ("Enter Number");
var columnValue = int.Parse (Console.ReadLine ());
int factor = columnValue / 702;
int rem = (columnValue % 702);
if (rem == 0) {
rem = 702;
factor -= 1;
Console.WriteLine ((factor + 1) + columnNames[rem - 1]);

Input: 1
Cell Name: 1A

Input: 691
Cell Name: 1ZO

Input : 2801
Cell Name: 4ZS


 Solution is simple Each row has 702 columns and column names start from A to ZZ. So its easy to solve by just keeping all the column names in an array in same order as show in excel.

Once array is ready we need to calculate two values one is factor and other is reminder for given number after dividing by 702 (max number of columns).

factor will be our row number and reminder will be out column name index.

Working C# Sample below: Sample Output:
Enter Number
Output: 4ZZ

This gives your answer