Get unique values from excel column

Aspose.Cells Product Family
1

Does aspose have any function to get only the unique column values and filter out the rows that have repeating vale. We are currently using CountIf function however it is taking lots of memory. Adding just 3 lakh record to the column bumps up the memory to 10.5GB

2

@shahsidd1
We can obtain the unique value of each column by using advanced filters. Please refer to the attachment (12.6 KB).

The sample code as follows:

Workbook workbook = new Workbook(filePath + "a.xlsx");

Worksheet worksheet = workbook.Worksheets[0];
//Apply advanced filter on range A1:A12 and criteria range is B1:B12            
worksheet.AdvancedFilter(true, "A1:A12", "B1:B12", "", true);

workbook.Save(filePath + "out_filter.xlsx");

For advanced filter, you can refer to the following documents.

3

@shahsidd1,

Are you looking for the function such as UNIQUE? Aspose.Cells has supported this function and you may try it.

4

Hi,

I tried the unique formula but it did not work as expected. Attached is the code and the sample file.

3914.zip (8.1 KB)

Thanks
Siddharth Shah

5

@shahsidd1
UNIQUE is a dynamic array formula. Please call the Workbook.refreshDynamicArrayFormulas method before calculating the formula.

The sample code as follows:

Workbook wb = new Workbook(filePath + "blank.xlsx");
Worksheet sheet = wb.getWorksheets().get("Sheet1");
int start = 0;
Cells cells = sheet.getCells();

int startRowOrColumn= 0;
for (int i = 0; i < 5; i++) {
	Cell cell = cells.get(startRowOrColumn + i, start);
	
	cell.putValue(5);
	
}
for (int i = 5; i < 7; i++) {
	Cell cell = cells.get(startRowOrColumn + i, start);
	
	cell.putValue(2);
	
}

for (int i = 7; i < 10; i++) {
	Cell cell = cells.get(startRowOrColumn + i, start);
	
	cell.putValue(6);
	
}
//refresh dynamic array formulas
wb.refreshDynamicArrayFormulas(true);
wb.calculateFormula(true);
// Saving the Excel file
wb.save(filePath + "out_java.xlsx");
wb.dispose();

Hope helps a bit.

6

Thanks John. The code is working as expected. Do we have a list of Dynamic formulas supported by excel. Also I tried using the same function for array formula but unfortunately it did not work. The formula was

{IFERROR(FILTER(Mapping, Mapping,),"")}

Can you please help check if this works at your end?

7

@shahsidd1,

We do not have a specific list of dynamic array formulas but you may check other online documents for your reference.
e.g.,
https://www.simonsezit.com/article/dynamic-arrays-in-excel/

Could you please share your template Excel file and sample code to evaluate your issue? We will check your issue soon.

8

@shahsidd1,

Almost all kind of formulas can be taken as dynamic array formulas in newer version of ms excel. For example, when you input formula like “=A1:A3” for B1 cell, the formula will be spilled into B1:B3 automatically. So, there is no special list of dynamic array formulas.

If you use the legacy array formula like your “{IFERROR(FILTER(Mapping, Mapping,),”“)}” instead, you need to know the dimension of this formula’s calculated result and use the dimension to set array formula by methods such as setArrayFormula(String arrayFormula, int rowNumber, int columnNumber). For above example “=A1:A3”, it is easy for you to know its dimension(3x1) so you can set the array formula as

cell.setArrayFormula("=A1:A3", 3, 1)

But for some functions such as UNIQUE, FILTER, the dimension of calculated result depends on the used data set. Generally it is impossible for you to get it without calculation(it is just the main job for refreshing dynamic array formulas). If you set it as legacy array formula with incorrect rowNumber/columnNumber, then it is very possible that you cannot get the correct result.