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