# Data Filter Count

Hi,

I have an excel file with lots of data. In a particular column I have multiple data values. Now, is possible to get the names of all the distinct values in that column along with their count of occurance with the help of Aspose. Thanks in advance.

Regards,

Neel

Hi,

Please provide your source input xls/xlsx file which you can create manually in MS-Excel and also some screenshots will be helpful to better know your requirement.

I have attached the input Excel. In this excel I want the count of the distinct values in the column D (Assay Type). I have to create a new csv file with the names of the distinct data in that column along with their counts. Please let me know if the requirement is not clear enough

Hi,

Please see the following code written by me to find all the unique values and their count. The code basically uses Hash Table a sort of data structure best fit for your needs.

C#

//Create a hash table it will have all the unique values and their counts

Hashtable hashTable = new Hashtable();

//Open the source file

Workbook workbook = new Workbook(fpath);

//Access the first sheet

Worksheet worksheet = workbook.Worksheets[0];

//Colum D index

int colIdx = 3;

//Find unique values

for (int rowIdx = 1; rowIdx < worksheet.Cells.Rows.Count; rowIdx++)

{

string cellValue = worksheet.Cells[rowIdx, colIdx].StringValue;

//If hashtable does not contain this cell value before than create

//a hash key and place 1 inside it

//Otherwise increase the old value

if (hashTable[cellValue] == null)

{

hashTable[cellValue] = 1;

}

else

{

int increment = Convert.ToInt32(hashTable[cellValue]) + 1;

hashTable[cellValue] = increment;

}//if

}//for

//Finally print the unique values and their counts

foreach (DictionaryEntry hashValue in hashTable)

{

Debug.WriteLine(hashValue.Key.ToString() + ": " + hashValue.Value.ToString());

}

Output:

ALT: 239
CRP: 5
FT4: 14
A1C: 32
CEA: 1
Mg: 53
hsCRP: 2
Fol: 7
PCP: 19
LAC: 10
CRBM: 2
URIC: 6
VANC: 18
CHOL: 131
ACET: 4
FT3: 2
CREA: 479
K: 493
BARB: 19
LH: 2
CL: 466
GGT: 14
TP: 275
ALKP: 228
BENZ: 19
HBsAg: 10
BUN: 465
COCM: 19
Glu: 490
TBIL: 237
B-hCG: 11
OP: 19
Prol: 3
HBc M: 6
HAVT: 6
aHBs: 3
AMPH: 19
AMYL: 42
dLDL: 17
tropi: 108
mALB: 10
NTBNP: 40
dHDL: 127
Ca: 466
PSA: 18
aHCV: 8
NA: 471
Li: 1
AST: 230
DGXN: 7
THC: 19
FE: 9
TSH: 42
Cort: 2
PALB: 7
CK: 2
VALP: 4
AMON: 5
B12: 11
dBili: 23
FSH: 3
ALB: 255
TT4: 5
CKMB: 87
SALI: 4
Prog: 3
CO2: 466
HPT: 1
TT3: 45
TRIG: 126
LDH: 5

thanks Shakeel…it worked…
you are the BOSS…!!!