We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copying rows from one sheet to another sheet using excel Formula

Aspose.Cells Product Family
#1

Hi,

I am having following Requirement.On sheet 1, I am having data as follows :

Product Country Price
ABC Ind 10
CDE SLN 20
EFG BAN 30
ABC SLN 40

I want to show all the records on sheet 2 for Product ABC using any EXCEL FORMULA.

I tried VLOOKUP and MATCH , but I am not getting the solution.

Is there any Function with which we can accomplish the above mentioned.

If yes,Please provide sample example.

Thanks,

#2

Hi Jack,


Thank you for contacting Aspose support.

You can use the Excel’s VLOOKUP formula to pick individual cell values and show them on another worksheet as a reference. Please check the following code snippet that uses the same approach to show all values from Sheet1 on Sheet2. You can surely amend the code to achieve your custom requirement.

C#

Workbook workbook = new Workbook(myDir + “Book2.xlsx”);
Worksheet worksheet = workbook.Worksheets[1];
for (int i = 0; i <= 3; i++)
{
for (int j = 0; j <= 2; j++)
{
worksheet.Cells[i, j].Formula = “=VLOOKUP(Sheet1!A” + (i + 1) + “,data,” + (j + 1) + “,FALSE)”;
}
}
workbook.CalculateFormula();
workbook.Save(myDir + “VLOOKUP.xlsx”, SaveFormat.Xlsx);

You can also use the Aspose.Cells' Find functionality to seek for a particular text in a given spreadsheet, and copy the entire row to another worksheet. Please check the following code snippet and attached resultant spreadsheet for your reference.

C#
Workbook workbook = new Workbook(myDir + "Book2.xlsx"); Worksheet sourceWorksheet = workbook.Worksheets[0]; Cells sourceCells = sourceWorksheet.Cells;
FindOptions opts = new FindOptions(); opts.LookInType = Aspose.Cells.LookInType.Values; opts.LookAtType = Aspose.Cells.LookAtType.EntireContent;
Cell cell = sourceCells.Find("ABC", null, opts); System.Console.WriteLine("Name of the cell containing required text: " + cell.Name);
int row = 0; Worksheet destinationWorksheet = workbook.Worksheets[1]; Cells destinationCells = destinationWorksheet.Cells; destinationCells.CopyRow(sourceCells, cell.Row, row++ );

workbook.Save(myDir + "Find.xlsx", SaveFormat.Xlsx);

Please feel free to write back in case you need our further assistance with Aspose APIs.
#3

Hi Babar,

Thanks for your response.

I have one question. The above code, we will use while creating the workbook. I am in need of functionality after we create the workbook.

Suppose, In my workbook, on One sheet I am having data and one Combo box/Drop down.

If I select any product, then corresponding row has to show on other sheet.

In other sense, i am looking for event handling kind of stuff in excel through Aspose.

Is this possible using some Excel formulas.

If yes, please provide sample example/sheet.

Thanks,

#4

Hi Jack,


I think your requirement can be achieved using macros, however, Aspose.Cells APIs do not support creation, manipulation or triggering of macros in a spreadsheet. The API can only preserve or remove them while processing a spreadsheet containing macros.
#5

Hi,

While surfing the net, i got the following formula for returning multiple values.

=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))

On following link :

http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

Could you please suggest any other related formula/suggestion to accomplish my requirement, as this formula looks complex and i need to copy in every cell.

Sorry for any inconvenience caused my post.

Thanks

#6

Hi Jack,


Thank you for writing back,

Unfortunately, I couldn’t think of a simple formula that could enable you to copy an entire row from one worksheet to another, this is because; more the cells are involved more the formula will become complex. Anyway, you should be looking for a VBA based solution for your scenario. I think if you search the internet for VBA, you will find pretty simple modules to achieve your requirements.
#7

Hi Jack,

Thanks for using Aspose.Cells.

For your requirement, here is a one solution you may try.

Java


Workbook wb = new Workbook(“VLOOKUP.xlsx”);

Cells cellsData = wb.Worksheets[0].Cells;

int maxDataCol = cellsData.MaxDataColumn + 1;

int maxDataRow = cellsData.MaxDataRow + 1;

Cell cell = cellsData[0, maxDataCol];

cell.SetSharedFormula("=ROW()", maxDataRow, 1);

Worksheet sheetView = wb.Worksheets[1];

Cells cellsView = sheetView.Cells;

cellsView.Columns[0].IsHidden = true;

cell = cellsView[2, 0];

maxDataCol++;

cell.SetSharedFormula("=VLOOKUP(INDIRECT(“Sheet1!A”&$A$1),INDIRECT(“Sheet1!A”&(A2+1)&":"&""

+ CellsHelper.CellIndexToName(maxDataRow, maxDataCol) + “”)," + maxDataCol + “,FALSE)”,

maxDataRow, 1);

cell = cellsView[2, 1];

cell.SetSharedFormula("=IFERROR(OFFSET(INDIRECT(“Sheet1!A”&$A3),0,COLUMN()-2),"")", maxDataRow, maxDataCol - 1);

ShapeCollection shapes = sheetView.Shapes;

Shape s = shapes.AddShape(MsoDrawingType.ComboBox, 0, 0, 1, 0, 30, 100);

ComboBox cb = (ComboBox)s;

cb.InputRange= “Sheet1!A1:A” + maxDataRow;

cb.LinkedCell = “A1”;

wb.Save(“res.xlsx”);