How to use macro filter excel?

Help Activities
, ,
1

I want to filter date in excel in sheet CaseCA and sheet CaseCH.
input.xlsx (11.1 KB)
output.xlsx (10.8 KB)

I have variable for dateCasaCA as string = 01/01/2021
and variable for dateCasaCH as string = 02/05/2021

Condition : I want filter date in sheet CasaCA by date < dateCasaCA (01/01/2021)
and filter date in sheet CasaCH by date < dateCasaCH (02/05/2021) and write it in same file.

Please guide me about it.

2

@fairymemay - Are you specifically looking for macro ?? we can easily use LINQ query to filter the result and write to the output sheet.

3

@prasath17 I’m ok for use LINQ query.
But I want to write in same file and same sheet.

Please guide me.

4

@fairymemay - may I ask why? Since you have 5 rows in the input sheet and 2 in the output for CaseCA if you write the output to the same sheet your other rows will still be there I am afraid. So I won’t write it to the same sheet.

5

@prasath17 If clear data from sheet CaseCA and CashCH before write.
Is it possible or not?

6

@fairymemay - That is equivalent to writing the result to new sheet or the new workbook right???

7

@prasath17 Ok , If can’t write in same sheet.
Can write in new sheet but same name (delete original sheet) ?

8

You can’t do that…May be first we can delete the sheet and create a new sheet with same name…

9

@prasath17 Delete the sheet and create a new sheet with same name // okay

Please solution for filter excel to me.

10

@fairymemay - Here you go
DateFilter_Noona.zip (69.2 KB)

Please remove the “input_Org.xlsx” from the folder and then run the workflow you will see the output gets created.

date in sheet CasaCH by date < dateCasaCH (02/05/2021) → For this case, only one date qualified because your date format in the sheet is dd/MM/yyyy.

Hope this helps…

1 Like
11

@fairymemay - Did you get a chance to try this? If yes and solved your query , please mark my post as solution.

1 Like
12

@prasath17 If I use my excel , it show error as below.

image
image1020Ă—164 9.82 KB

File excel : input.xlsx (15.5 KB) ( I delete sensitive date in my file)

I edit filter as below.

image

Please guide me more.

13

Whatever the sheet you initially shared, i have provided the fully working code…

Recently shraed one is not in proper date format. Check the cell type on top it is “General”

image
image635Ă—700 19.8 KB

This is our original format

image
image544Ă—562 12.5 KB

14

@prasath17 If​ I​ want​ use​ format​ General.
How to solve. XAML?

15

I can help. But May I know why your original requirement has changed?

1 Like
17

@prasath17 Now, I​ download​ file​ from​ internal​ system.​----->due​ date​ format​ type​ General.
But original​ file​ that​ example​ data

18

@prasath17 If you have free time Please advise for​ solve this case.

Thank​ you.

19

@fairymemay - please let me know what is the exact filtering requirement?

1 Like
21

@prasath17 I have variable date1 as sting = 20/05/2021

I want filter sheet caseCA column Due Date (format type General) < date1 and write in same sheet.
but if column Due Date not have data < date1 , I want to show header in sheet only.

input.xlsx (20.9 KB)

Please suggest​ solution​ for​ this​ case.

1 Like
22

@fairymemay - I will work on this today and let you know.

1 Like