filter data based on month or whole data across sheets using dropdown

Maklil · Jul 7, 2023

Hello
I need filtering data by merge duplicates items for column C,D based on ID in column B across sheets with matching part of item using helper column (G) then should show the items based on column G in output sheet .
if cell B1 is empty then should merge for each ID for whole data and insert BALANCE column(E) to subtract column C from D , if I select specific month , then just merge based on month and insert BALANCE column(E) to subtract column C from D . the result should be in OUTPUT based on cell B1 and match part of the item in column G with others sheets in column B and insert TOTAL row to sum the whole columns and subtract from each other.
every time I will add new sheets before OUTPUT sheet. so should update data in OUTPUT sheet as I need it by clear data in OUPUT sheet .

TOTAL (1).xlsm

A

B

C

D

1

DATE

BATCH

IMPORT

EXPORT

2

01/01/2023

STR ATM001 RS

2,000.00

3

02/01/2023

NO S001 STR1

2,200.00

4

03/01/2023

TRGG MMNR MSDF

2,400.00

1,200.00

5

04/01/2023

MMDT NTY AS002

2,600.00

1,300.00

6

05/01/2023

MSR

2,800.00

1,400.00

7

06/01/2023

ATM001 RS STR

1,500.00

8

07/01/2023

NO STR1 SFGG N

1,600.00

9

08/01/2023

STR

1,700.00

10

09/02/2023

NTY AS002 MMDT BN

1,800.00

11

10/02/2023

MSR NBH 1099U NMT66

3,800.00

12

11/02/2023

STR

4,000.00

2,000.00

13

12/02/2023

NO S001 CV678 STR1

4,200.00

2,100.00

14

13/02/2023

MMNR TRGG MSDF

4,400.00

15

14/02/2023

MMDT

4,400.00

16

15/02/2023

NBH 1099U MSR NMT66

4,400.00

2,200.00

CA

TOTAL (1).xlsm

A

B

C

D

1

DATE

BATCH

IMPORT

EXPORT

2

11/01/2023

ATM001 RS MN99 STR

200.00

3

12/01/2023

NO S001 NBH100 STR1

200.00

4

13/01/2023

TRGG MNJ800 MMNR MSDF

3,000.00

100.00

5

14/01/2023

MMDT NTY AS002

200.00

6

15/01/2023

MSR

1,000.00

1,400.00

7

16/01/2023

ATM001 RS BYJ STR

600.00

8

17/01/2023

NO STR1 SFGG N

1,000.00

200.00

9

18/01/2023

STR

1,000.00

100.00

10

19/02/2023

NTY AS002 MMDT BN

2,000.00

11

20/02/2023

MSR NBH 1099U NMT66

3,800.00

12

21/02/2023

STR

4,000.00

2,000.00

13

22/02/2023

NO S001 CV678 STR1

4,200.00

2,100.00

14

23/02/2023

MMNR TRGG MSDF

4,400.00

15

24/02/2023

MMDT

4,400.00

16

25/02/2023

NBH 1099U MSR NMT66

4,400.00

2,200.00

CMN

when B1 is empty

TOTAL (1).xlsm

A

B

C

D

E

F

G

1

ITEMS

2

BATCH

IMPORT

EXPORT

BALANCE

STR

3

STR1

4

MMNR

5

MMDT

6

MSR

7

8

9

10

OUTPUT

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MONTHS

result

TOTAL (1).xlsm

A

B

C

D

1

2

BATCH

IMPORT

EXPORT

BALANCE

3

STR

11,200.00

7,800.00

3,400.00

4

STR1

11,800.00

600.00

11,200.00

5

MMNR

14,200.00

1,300.00

12,900.00

6

MMDT

13,600.00

1,300.00

12,300.00

7

MSR

20,200.00

7,200.00

13,000.00

8

TOTAL

71,000.00

18,200.00

52,800.00

9

OUTPUT

Cell Formulas
Range	Formula
B8:C8	B8	=SUM(B3:B7)
D8	D8	=B8-C8

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MONTHS

IF I select month in B1 = JAN

TOTAL (1).xlsm

A

B

C

D

E

F

G

1

JAN

ITEMS

2

BATCH

IMPORT

EXPORT

BALANCE

STR

3

STR

3,200.00

3,900.00

-700.00

STR1

4

STR1

3,400.00

1,800.00

1,600.00

MMNR

5

MMNR

5,400.00

1,300.00

4,100.00

MMDT

6

MMDT

2,800.00

1,300.00

1,500.00

MSR

7

MSR

3,800.00

2,800.00

1,000.00

8

TOTAL

18,600.00

11,100.00

7,500.00

9

OUTPUT

Cell Formulas
Range	Formula
B8:C8	B8	=SUM(B3:B7)
D8	D8	=B8-C8

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MONTHS

the data could be 2500 rows for each sheet , the sheets could be 20 sheets at least
thanks .

Akuini · Jul 8, 2023

@Maklil, try this:
Note: I haven't applied the Total, just see if this works first:

VBA Code:

Sub Maklil_1()
Dim regEx As Object
Dim va, vb
Dim i As Long, j As Long, M As Long

Sheets("OUTPUT").Activate
Range("A3:D100000").Clear
va = Range("G2", Cells(Rows.Count, "G").End(xlUp)).Resize(, 4)
        Set regEx = CreateObject("VBScript.RegExp")
        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = False
        End With

    For Each wsn In Worksheets
        If wsn.Name <> "OUTPUT" Then
            For i = 1 To UBound(va, 1)
                regEx.Pattern = "\b" & va(i, 1) & "\b"
                
                If Range("B1") = "" Then
                    With wsn
                        vb = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 4)
                        For j = 1 To UBound(vb, 1)
                            If regEx.test(vb(j, 2)) Then
                                va(i, 2) = va(i, 2) + vb(j, 3)  'import
                                va(i, 3) = va(i, 3) + vb(j, 4)  'export
                                va(i, 4) = va(i, 2) - va(i, 3)  'balance
'                            If va(i, 1) = "STR" Then Debug.Print vb(j, 2) & " : " & va(i, 2) & " : " & va(i, 3)
                            End If
                            
                        Next
                    End With
                    
                Else
                    With wsn
                        vb = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 4)
                        M = Month(DateValue("1 " & Range("B1") & " 2020"))
                        For j = 1 To UBound(vb, 1)
                            If Month(vb(j, 1)) = M Then
                                If regEx.test(vb(j, 2)) Then
                                    va(i, 2) = va(i, 2) + vb(j, 3)  'import
                                    va(i, 3) = va(i, 3) + vb(j, 4)  'export
                                    va(i, 4) = va(i, 2) - va(i, 3)  'balance
                                End If
                            End If
                        Next
                    End With
               End If
            Next
        End If
    Next

Range("A3").Resize(UBound(va, 1), 4) = va
        
End Sub

But I got different result from yours when B1 is blank:

Maklil #1.xlsm

A

B

C

D

1

2

BATCH

IMPORT

EXPORT

BALANCE

3

STR

11200

7900

3300

4

STR1

11800

6000

5800

5

MMNR

14200

1300

12900

6

MMDT

13600

3100

10500

7

MSR

20200

7200

13000

8

OUTPUT

Maklil · Jul 8, 2023

But I got different result from yours when B1 is blank:

my apologies about merging errors!

your code works greatly , but I need add TOTAL row as in OP .
thank you .

Akuini · Jul 8, 2023

Maklil said:
my apologies about merging errors!
your code works greatly , but I need add TOTAL row as in OP .
thank you .

Try this:

VBA Code:

Sub Maklil_2()
Dim regEx As Object
Dim va, vb
Dim i As Long, j As Long, M As Long, n As Long
Dim Wsn As Worksheet

Sheets("OUTPUT").Activate
Range("A3:D100000").Clear
va = Range("G2", Cells(Rows.Count, "G").End(xlUp)).Resize(, 4)
        Set regEx = CreateObject("VBScript.RegExp")
        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = False
        End With

    For Each Wsn In Worksheets
        If Wsn.Name <> "OUTPUT" Then
            For i = 1 To UBound(va, 1)
                regEx.Pattern = "\b" & va(i, 1) & "\b"
                
                If Range("B1") = "" Then
                    With Wsn
                        vb = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 4)
                        For j = 1 To UBound(vb, 1)
                            If regEx.test(vb(j, 2)) Then
                                va(i, 2) = va(i, 2) + vb(j, 3)  'import
                                va(i, 3) = va(i, 3) + vb(j, 4)  'export
                                va(i, 4) = va(i, 2) - va(i, 3)  'balance
'                            If va(i, 1) = "STR" Then Debug.Print vb(j, 2) & " : " & va(i, 2) & " : " & va(i, 3)
                            End If
                            
                        Next
                    End With
                    
                Else
                    With Wsn
                        vb = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 4)
                        M = Month(DateValue("1 " & Range("B1") & " 2020"))
                        For j = 1 To UBound(vb, 1)
                            If Month(vb(j, 1)) = M Then
                                If regEx.test(vb(j, 2)) Then
                                    va(i, 2) = va(i, 2) + vb(j, 3)  'import
                                    va(i, 3) = va(i, 3) + vb(j, 4)  'export
                                    va(i, 4) = va(i, 2) - va(i, 3)  'balance
                                End If
                            End If
                        Next
                    End With
               End If
            Next
        End If
    Next

Range("A3").Resize(UBound(va, 1), 4) = va
n = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & n) = "TOTAL"
Range("B" & n) = "=SUM(B3:B" & n - 1 & ")"
Range("C" & n) = "=SUM(C3:C" & n - 1 & ")"
Range("D" & n) = "=B" & n & "-C" & n
Range("A" & n & ":D" & n).Interior.Color = 49407
Range("A" & n & ":D" & n).Font.Bold = True

End Sub

Maklil · Jul 8, 2023

it will copy data twice and will delete borders which made it manually !

TOTAL (1).xlsm

A

B

C

D

E

F

G

1

ITEMS

2

BATCH

IMPORT

EXPORT

BALANCE

STR

3

STR

11200

7900

3300

STR1

4

STR1

11800

6000

5800

MMNR

5

MMNR

14200

1300

12900

MMDT

6

MMDT

13600

3100

10500

MSR

7

MSR

20200

7200

13000

8

71000

25500

45500

9

71000

25500

45500

ITEMS

10

ITEMS

STR

11

STR

11200

7900

3300

STR1

12

STR1

11800

6000

5800

MMNR

13

MMNR

14200

1300

12900

MMDT

14

MMDT

13600

3100

10500

MSR

15

MSR

20200

7200

13000

16

TOTAL

284000

102000

182000

OUTPUT

Cell Formulas
Range	Formula
B16:C16	B16	=SUM(B3:B15)
D16	D16	=B16-C16

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MONTHS

Akuini · Jul 8, 2023

Maklil said:
it will copy data twice and will delete borders which made it manually !

I can't reproduce the error, here's what I got by running the code once or twice:

Maklil #1.xlsm

A

B

C

D

E

F

G

1

ITEMS

2

BATCH

IMPORT

EXPORT

BALANCE

STR

3

STR

11200

7900

3300

STR1

4

STR1

11800

6000

5800

MMNR

5

MMNR

14200

1300

12900

MMDT

6

MMDT

13600

3100

10500

MSR

7

MSR

20200

7200

13000

8

TOTAL

71000

25500

45500

9

OUTPUT

Cell Formulas
Range	Formula
B8:C8	B8	=SUM(B3:B7)
D8	D8	=B8-C8

In the beginning, the code will clear Range("A3:D100000") in Sheets("OUTPUT"):

VBA Code:

Sheets("OUTPUT").Activate
Range("A3:D100000").Clear

Isn't that what you want? as you said:

Maklil said:
so should update data in OUTPUT sheet as I need it by clear data in OUPUT sheet .

Maklil · Jul 8, 2023

I will check again maybe the problem is from the file .

Maklil · Jul 8, 2023

ok but how I can fix problem deletion borders and format number.

the code delete it #,##0.00 , borders !

Akuini · Jul 8, 2023

Maklil said:
ok but how I can fix problem deletion borders and format number.

the code delete it #,##0.00 , borders !

OK, I'm assuming the OUTPUT sheet already has the format. The code just keeps the format intact.
Try:

VBA Code:

Sub Maklil_3()
Dim regEx As Object
Dim va, vb
Dim i As Long, j As Long, M As Long, n As Long
Dim Wsn As Worksheet
Dim c As Range

Sheets("OUTPUT").Activate
Set c = Range("A3:D3")
Range("A4:D100000").Clear

va = Range("G2", Cells(Rows.Count, "G").End(xlUp)).Resize(, 4)
        Set regEx = CreateObject("VBScript.RegExp")
        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = False
        End With

    For Each Wsn In Worksheets
        If Wsn.Name <> "OUTPUT" Then
            
            For i = 1 To UBound(va, 1)
                regEx.Pattern = "\b" & va(i, 1) & "\b"
                
                If Range("B1") = "" Then
                    With Wsn
                        vb = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 4)
                        For j = 1 To UBound(vb, 1)
                            If regEx.test(vb(j, 2)) Then
                                va(i, 2) = va(i, 2) + vb(j, 3)  'import
                                va(i, 3) = va(i, 3) + vb(j, 4)  'export
                                va(i, 4) = va(i, 2) - va(i, 3)  'balance
'                            If va(i, 1) = "STR" Then Debug.Print vb(j, 2) & " : " & va(i, 2) & " : " & va(i, 3)
                            End If
                            
                        Next
                    End With
                    
                Else
                    With Wsn
                        vb = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 4)
                        M = Month(DateValue("1 " & Range("B1") & " 2020"))
                        For j = 1 To UBound(vb, 1)
                            If Month(vb(j, 1)) = M Then
                                If regEx.test(vb(j, 2)) Then
                                    va(i, 2) = va(i, 2) + vb(j, 3)  'import
                                    va(i, 3) = va(i, 3) + vb(j, 4)  'export
                                    va(i, 4) = va(i, 2) - va(i, 3)  'balance
                                End If
                            End If
                        Next
                    End With
               End If
            Next
        End If
    Next

Range("A3").Resize(UBound(va, 1), 4) = va
n = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & n) = "TOTAL"
Range("B" & n) = "=SUM(B3:B" & n - 1 & ")"
Range("C" & n) = "=SUM(C3:C" & n - 1 & ")"
Range("D" & n) = "=B" & n & "-C" & n

c.Copy
Range("A" & 3 & ":D" & n).PasteSpecial xlPasteFormats

Range("A" & n & ":D" & n).Interior.Color = 49407
Range("A" & n & ":D" & n).Font.Bold = True
End Sub

Maklil · Jul 8, 2023

sorry about error in OP ,no one waste time for this bad thread , but you did it.
I grateful for your assistance and spend your time to help me.
every thing is great.
thank you so much .

filter data based on month or whole data across sheets using dropdown

Maklil

Board Regular

Excel Facts

Akuini

Well-known Member

Maklil

Board Regular

Akuini

Well-known Member

Maklil

Board Regular

Akuini

Well-known Member

Maklil

Board Regular

Maklil

Board Regular

Akuini

Well-known Member

Maklil

Board Regular

Similar threads

Forum statistics

Share this page

filter data based on month or whole data across sheets using dropdown

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock