Free Support Forum - aspose.com

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.

Please also see this articles:

Data Filtering
Data Validation

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.

The code uses your source input file. Please see its output.

C#


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

Hashtable hashTable = new Hashtable();


//Open the source file

string fpath = @“F:\Downloads\inputExcel.xls”;

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++)

{

//Read the cell value

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…!!!