Adding data from a filtered column

[ClaytonM]

There are few ways to do this.

One way is to manipulate the data table, however, you may find that after removing some rows with this method, that writing it back to the file, the existing data in the file has more rows therefore leaving the excess rows at the bottom of the spreadsheet.

dt1.AsEnumerable.Where(Function(r) r("CEDULA").ToString.Trim<>"90850158" ).ToArray

or if you have the ids in an array/list, then the condition would look like this:

dt1.AsEnumerable.Where(Function(r) Not idsToRemove.Contains(r("CEDULA").ToString.Trim) ).ToArray

or if you already have an array of rows, then change the dt1.AsEmuerable to the array:

matchedRows.Where(Function(r) Not idsToRemove.Contains(r("CEDULA").ToString.Trim) ).ToArray

Then, process those rows as you would as previously presented.

However, like I mentioned, when you try to write this new data set back to the existing file, if the data in the existing file has more rows than what you are writing, it will keep those excess rows. This means you would need to clear the file prior to writing it back to the file.

There is another way though, which removes the row directly in the file, but slightly slower:

The ‘Insert/Delete Rows’ activity lets you perform the action directly in the file. This also means you need to run loops over all the ids you want to remove.

For this, you still need to find the row index because it is required by the activity. You can use .net for this (but if you already are using a ForEach of the row, then use the output property for the index value.)

First, you need to run a loop over all the IDs you want to remove. Then, inside the loop, assign the index using the cedula value from the loop. After that, just place the index into the ‘Insert/Delete Rows’ activity.

It will also be important assign the rows to delete to an array of DataRows, so you can remove all rows matching to the ID and it won’t throw any errors:

ForEach id In idsArray //typeargument: String
    Assign: idRowsToDelete = dt1.AsEnumerable.Where(Function(r) r("CEDULA").ToString.Trim = id ).ToArray

    IF idRowsToDelete.Count > 0
        ForEach idRow In idRowsToDelete //typeargument: DataRow
            idRowIndex = dt1.Rows.IndexOf(idRow)
            Insert/Delete Row activity using idRowIndex with Delete option.

I hope that makes sense. If you have trouble, I might be able when I have time later to provide the .xaml with this working.

Note: if you have the Excel AutoSave option off, the execution of the delete will be faster. Then, use the ‘Save Workbook’ activity when all rows are done deleting.

Regards.

[Beatriz_Eugenia_Duqu (Beatriz)]

@ClaytonM

or if you have the ids in an array/list, then the condition would look like this:

Thank you very much

[Beatriz_Eugenia_Duqu (Beatriz)]

But I need to eliminate the whole user from this filter, and then generate another filter to take only this user, since I must get a different percentage from the “IBCS”.

I haven’t been able to do it yet.

Regards

[ClaytonM]

Here is sample xaml working off of a previous xaml i sent you: Totales(filterpart2).xaml (22.4 KB)

I used a spreadsheet with the cedulas you want to remove, then stored that to an array variable, which was used to filter the data set between the filtered data without the removed cedulas and the data set of the removed cedulas.

Then, using those filtered arrays, calculated the sum of each.

Lastly, wrote both those data sets to new sheets. I also used a Insert/Delete activity to remove all existing rows already in those sheets before outputting, to avoid keeping excess rows - although, you may not need that part.

I also added the Close Workbook activity in a few places which is really needed anytime you use an Excel scope to avoid leaving the file open in the background.

Additionally, as I mentioned in prior post, you can run a loop on the Removed data set to remove the row directly within the main sheet, however, I chose not to include that as it may be a good idea to keep the original data to audit the results if needed.

I hope this helps solve your issues. Regards.

EDIT: I stored the file path to the cedulas you want to remove in the cedulaRemoveFilePath variable.

[jerry_maguire]

Try this:

1. Copy the formula or value to the clipboard
2. Select the filtered column
3. Hit F5 or Ctrl+G to open the Go To dialog
4. Click Special
5. Click “Visible cells only” and OK
6. Hit Ctrl+V to paste.

Let me know if this works.

Regards,
Jerry M.

[Beatriz_Eugenia_Duqu (Beatriz)]

I didn’t quite understand the process

[Beatriz_Eugenia_Duqu (Beatriz)]

@Beatriz_Eugenia_Duqu

“CEDULA=‘9999999’”

You can’t put a condition inside an Assign that I can put to delete when “CEDULA=‘9999999’”, the way you helped me with the .XAML I don’t see how I can delete a specific user unless I delete it directly from the file, that is to say I must put a position and if you remember the file when a “CEDULA” user has a new one it will modify the position of all the others, so you can’t condition it to a cell number but to a specific “CEDULA”

[ClaytonM]

The solution I posted a few days ago deletes the row by the user (or array of users) from the data, then outputs the new data to a new spreadsheet.

However, if you would like to not output it to a new spreadsheet, I can make a slight adjustment to the .xaml and post it again. But, essentially, there are two approaches… 1) you can clear the existing spreadsheet (as shown in the previous xaml) and output the data in the same file - in this case, you don’t actually need to change much in the xaml: just the file or sheet name … or 2) use the Insert/Delete Row activity using the cell number, which is doable if you read the existing data each time before calculating the row number.

Let me know if you would like an adjusted xaml and which approach you would prefer.

Regards.

[Beatriz_Eugenia_Duqu (Beatriz)]

@ClaytonM

The solution I posted a few days ago deletes the row by the user (or array of users) from the data, then outputs the new data to a new spreadsheet.

However, if you would like to not output it to a new spreadsheet, I can make a slight adjustment to the .xaml and post it again. But, essentially, there are two approaches… 1) you can clear the existing spreadsheet (as shown in the previous xaml) and output the data in the same file - in this case, you don’t actually need to change much in the xaml: just the file or sheet name … or 2) use the Insert/Delete Row activity using the cell number, which is doable if you read the existing data each time before calculating the row number.

Let me know if you would like an adjusted xaml and which approach you would prefer.

f you can help me with the .xaml file I would appreciate it very much, it still creates confusion

Regards

[ClaytonM]

The previous .xaml I posted writes the filtered and removed users to different sheets, but if you want to remove the users from the original sheet, then here is a version of .xaml that does that:
Totales(filterpart3).xaml (23.0 KB)

In this xaml, I removed the Filter activity, and used the conditions directly in the Assign activities. Although, that was only because I was planning to use the original data, until I realized I couldn’t anyway. Regardless, the Assign .Where() filter approach works just fine.

In order to remove the users from the original sheet, I used a Loop on the filtered removed data variable, then read in the existing data to calculate the row number. I used another .Where() in this calculation so I could find the user that was being removed in the existing data.

I added a Log Message to show what was removed to help you.

Let me know if you need further help. This xaml can easily be adjusted for your requirements.

Regards.

[Beatriz_Eugenia_Duqu (Beatriz)]

I am trying to subtract the value of the “CEDULA” I need from the sum already made, but it doesn’t work for me