filter VBA to show only from the list

auzanoo · Nov 6, 2021

dear experts,

could i get a vba code for this problem?

i have a list of movie titles:

Movie Summary.xlsx

A

1

TITLE

2

YOU

3

5 MAN: RISE OF THE ZOMBIES

4

DO NOT ENTER

5

HOTLINE 666

6

HATTRICK

List

and i have raw data:

Movie Summary.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Market

Target

Non

2

All

Total

Non

3

Counter

Description (grouped)

Year

Channel

Day of week

Date

Time

A

B

C

D

E

F

G

4

1

6V YOU

2015

CSTV

Sunday

29/11/2015

25:42:38 - 25:59:59

186

0,4

12,2

0,4

309

10

60,1

5

2

Summary 29/11/2015

186

0,4

12,2

0,4

309

10

60,1

6

3

2018

CSTV

Saturday

06/01/2018

12:33:53 - 13:58:32

647

1,3

9,7

1,9

3.256

17

19,9

7

4

14:02:09 - 15:06:51

632

1,3

9,8

1,9

2.449

17

25,8

8

5

Summary 06/01/2018

640

1,3

9,7

1,9

2.907

17

22,4

9

6

6K HOTLINE 666

2015

WTV

Thursday

01/01/2015

09:56:12 - 11:05:04

986

1,9

12,8

3,1

4.532

15

21,8

10

7

11:05:05 - 11:10:01

867

1,6

14,7

2,2

3.148

19

27,6

11

7

11:10:01 - 11:56:15

1.191

2,3

15,4

3,2

3.621

15

32,9

12

8

Summary 01/01/2015

1.068

2,1

13,9

3,2

4.166

15

26,2

13

9

6V WHY ALWAYS ME

2017

BTV

Friday

06/10/2017

12:48:47 - 14:43:41

806

1,5

11,6

2,4

3.910

24

20,6

14

10

Summary 06/10/2017

806

1,5

11,6

2,4

3.910

24

20,6

15

11

6I HATTRICK

2019

NTV

Tuesday

07/05/2019

02:00:00 - 02:12:59

726

1,5

10,3

2,1

1.873

13

38,8

16

12

25:42:38 - 25:59:59

751

1,5

10,9

2,1

2.212

19

33,9

17

13

Summary 07/05/2019

742

1,5

10,7

2,1

2.087

17

35,7

Raw

and i want to be like this:

Movie Summary.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2

MOVIE SUMMARY

3

All Demographics

4

5

No.

Title

Tahun Rilis

Chn

Day

Date

Time

A

B

C

D

E

F

G

6

1

6V YOU

2015

CSTV

Sunday

29/11/2015

25:42 - 25:59

186

0,4

12,2

0,4

309

10

60,1

7

6V YOU

2018

CSTV

Saturday

06/01/2018

12:33 - 15:06

640

1,3

9,7

1,9

2.907

17

22,4

8

2

6V HOTLINE 666

2015

WTV

Thursday

01/01/2015

09:56 - 11:56

1.068

2,1

13,9

3,2

4.166

15

26,2

9

3

6I HATTRICK

2019

NTV

Tuesday

07/05/2019

02:00 - 02:12

726

1,5

10,3

2,1

1.873

13

38,8

10

6I HATTRICK

2019

NTV

Tuesday

07/05/2019

25:42 - 25:59

751

1,5

10,9

2,1

2.212

19

33,9

11

Summary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G10	Cell Value	duplicates	text	NO
G8	Cell Value	duplicates	text	NO
G7	Cell Value	duplicates	text	NO
G7	Cell Value	duplicates	text	NO
G6	Cell Value	duplicates	text	NO
G11:G1048576,G5:G6	Cell Value	duplicates	text	NO
G9	Cell Value	duplicates	text	NO

notes:
1. i just want to show the data from the movie list
2. the raw data movie title has a code
3. if the time is split, must be merged and use summary date (the blue one) ex: hotline 666
4. but if they split too long (more than one hour), keep them separated and ignore summary date ex: hattrick
5. format time from hh:mm:ss to hh:mm

big thanks if you could help this out. cause the real data has so many lists?

RasGhul · Nov 8, 2021

Hi,

Transforming your data might be easier in Power Query

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Counter

Description (grouped)

Year

Channel

Day of week

Date

Time

A

B

C

D

E

F

G

2

1

6V YOU

2015

CSTV

Sunday

29/11/2015

25:42 - 25:59

186

0.4

12.2

0.4

309

10

60.1

3

6V YOU

2018

CSTV

Saturday

6/01/2018

12:33 - 13:58

647

1.3

9.7

1.9

3,256

17

19.9

4

6V YOU

2018

CSTV

Saturday

6/01/2018

14:02 - 15:06

632

1.3

9.8

1.9

2,449

17

25.8

5

6

6K HOTLINE 666

2015

WTV

Thursday

1/01/2015

09:56 - 11:05

986

1.9

12.8

3.1

4,532

15

21.8

6

7

6K HOTLINE 666

2015

WTV

Thursday

1/01/2015

11:05 - 11:10

867

1.6

14.7

2.2

3,148

19

27.6

7

6K HOTLINE 666

2015

WTV

Thursday

1/01/2015

11:10 - 11:56

1,191

2.3

15.4

3.2

3,621

15

32.9

8

9

6V WHY ALWAYS ME

2017

BTV

Friday

6/10/2017

12:48 - 14:43

806

1.5

11.6

2.4

3,910

24

20.6

9

11

6I HATTRICK

2019

NTV

Tuesday

7/05/2019

02:00 - 02:12

726

1.5

10.3

2.1

1,873

13

38.8

10

12

6I HATTRICK

2019

NTV

Tuesday

7/05/2019

25:42 - 25:59

751

1.5

10.9

2.1

2,212

19

33.9

Sheet2

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [Time] <> null and [Time] <> ""),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Description (grouped)", "Year", "Channel", "Day of week", "Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"A", type number}, {"B", type number}, {"C", type number}, {"D", type number}, {"E", type number}, {"F", type number}, {"G", type number}, {"Date", type date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Time", Splitter.SplitTextByEachDelimiter({" - "}, QuoteStyle.Csv, false), {"Time.1", "Time.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time.1", type text}, {"Time.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "Time.1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time.1.1", "Time.1.2", "Time.1.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Time.1.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Time.1.1", "Time.1.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Merged Columns", "Time.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time.2.1", "Time.2.2", "Time.2.3"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Time.2.3"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns1",{"Time.2.1", "Time.2.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged.2"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns2",{{"Merged.2", "Time"}})
in
    #"Renamed Columns"

auzanoo · Nov 8, 2021

RasGhul said:

Hi,

Transforming your data might be easier in Power Query

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Counter

Description (grouped)

Year

Channel

Day of week

Date

Time

A

B

C

D

E

F

G

2

1

6V YOU

2015

CSTV

Sunday

29/11/2015

25:42 - 25:59

186

0.4

12.2

0.4

309

10

60.1

3

6V YOU

2018

CSTV

Saturday

6/01/2018

12:33 - 13:58

647

1.3

9.7

1.9

3,256

17

19.9

4

6V YOU

2018

CSTV

Saturday

6/01/2018

14:02 - 15:06

632

1.3

9.8

1.9

2,449

17

25.8

5

6

6K HOTLINE 666

2015

WTV

Thursday

1/01/2015

09:56 - 11:05

986

1.9

12.8

3.1

4,532

15

21.8

6

7

6K HOTLINE 666

2015

WTV

Thursday

1/01/2015

11:05 - 11:10

867

1.6

14.7

2.2

3,148

19

27.6

7

6K HOTLINE 666

2015

WTV

Thursday

1/01/2015

11:10 - 11:56

1,191

2.3

15.4

3.2

3,621

15

32.9

8

9

6V WHY ALWAYS ME

2017

BTV

Friday

6/10/2017

12:48 - 14:43

806

1.5

11.6

2.4

3,910

24

20.6

9

11

6I HATTRICK

2019

NTV

Tuesday

7/05/2019

02:00 - 02:12

726

1.5

10.3

2.1

1,873

13

38.8

10

12

6I HATTRICK

2019

NTV

Tuesday

7/05/2019

25:42 - 25:59

751

1.5

10.9

2.1

2,212

19

33.9

Sheet2

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [Time] <> null and [Time] <> ""),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Description (grouped)", "Year", "Channel", "Day of week", "Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"A", type number}, {"B", type number}, {"C", type number}, {"D", type number}, {"E", type number}, {"F", type number}, {"G", type number}, {"Date", type date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Time", Splitter.SplitTextByEachDelimiter({" - "}, QuoteStyle.Csv, false), {"Time.1", "Time.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time.1", type text}, {"Time.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "Time.1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time.1.1", "Time.1.2", "Time.1.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Time.1.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Time.1.1", "Time.1.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Merged Columns", "Time.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time.2.1", "Time.2.2", "Time.2.3"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Time.2.3"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns1",{"Time.2.1", "Time.2.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged.2"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns2",{{"Merged.2", "Time"}})
in
    #"Renamed Columns"

i don't know this thing is existing. thank you so much, Mr. RasGhul!

RasGhul · Nov 9, 2021

Glad we could help

filter VBA to show only from the list

auzanoo

Board Regular

Excel Facts

RasGhul

Well-known Member

auzanoo

Board Regular

RasGhul

Well-known Member

Similar threads

Forum statistics

Share this page

filter VBA to show only from the list

auzanoo

Board Regular

Excel Facts

RasGhul

Well-known Member

auzanoo

Board Regular

RasGhul

Well-known Member

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