Reverse-Filter-List-of-Items searching with a string of itens

rodrigobertollo · May 28, 2021

Hi guys,

Recently I've seen a workaround in this forum that made me think if this is also possible.

I have a file with expected gene coverage and I have genetic exams with a string of genes.
I have to check if the each gene is covered in each exam. However, for each search I need to transform the string of genes to a lis of itens. I want to transfer this task and reduce the complexity with a search similar of the image above:

Can this example be doable?

By the way, I also suffer with the partial match, since some genes names overlap (like NF2 and F2), I also need the search to be full match, my workaround for full match was to add spacial characters before and after the gene name so it would be unic.

Solving this also reduce the complexity of the task.

Thank you
Rodrigo

Fluff · May 28, 2021

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

rodrigobertollo · May 28, 2021

Fluff said:
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi

I've download this table from the internet, did not chance the code imbued on the file

CSTMV2 expected Coverage.xlsx

A

B

C

D

E

F

G

H

I

J

K

1

Gene

Transcript

Chr

Start

End

50x

60x

70x

Partial Match Filter

Filter List

2

|PIK3CD/

NM_005026.5

chr1

9710450

9727051

100.00%

|ATM/

3

|KIF1B/

NM_015074.3

chr1

10232323

10376592

100.00%

|BLM/

4

|TARDBP/

NM_007375.4

chr1

11013722

11022659

100.00%

|BRCA1/

5

|MTHFR/

NM_005957.5

chr1

11790674

11803121

38.07%

37.05%

35.72%

|BRCA2/

6

|PLOD1/

NM_000302.4

chr1

11934774

11974813

100.00%

|CBL/

7

|CTRC/

NM_007272.3

chr1

15438459

15446594

100.00%

|CEBPA/

8

|SDHB/

NM_003000.3

chr1

17018875

17054024

100.00%

|CHEK2/

9

|ALPL/

NM_000478.6

chr1

21554076

21577653

100.00%

|DDX41/

10

|MLH1/

NM_000249.4

chr3

36993542

37050658

|ETV6/

11

|GALE/

NM_000403.4

chr1

23795943

23799012

100.00%

|GATA2/

12

|LDLRAP1/

NM_015627.3

chr1

25543693

25566997

100.00%

|IKZF1/

13

|P3H1/

NM_022356.4

chr1

42746691

42766976

100.00%

|MLH1/

14

|SLC2A1/

NM_006516.4

chr1

42927035

42958656

100.00%

|MSH2/

15

|BRIP1/

NM_032043.3

chr17

61683290

61861544

99.99%

99.98%

99.95%

|MSH6/

16

|PTCH2/

NM_003738.5

chr1

44822409

44842937

100.00%

|NBN/

17

|MUTYH/

NM_001128425.2

chr1

45329300

45340259

100.00%

18

|FOXE3/

NM_012186.3

chr1

47416310

47417280

100.00%

19

|PCSK9/

NM_174936.4

chr1

55039832

55063589

100.00%

20

|ACADM/

NM_000016.6

chr1

75724782

75762768

100.00%

21

|MSH6/

NM_000179.3

chr2

47783228

47806865

100.00%

99.98%

|MSH6/

22

|DPYD/

NM_000110.4

chr1

97078970

97920927

95.61%

95.49%

95.34%

23

|AGL/

NM_000028.2

chr1

99851037

99921656

100.00%

Custom Média

Cell Formulas
Range	Formula
F2:H9,F11:H14,F16:H20,F22:H23	F2	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J2)
I2:I23	I2	=IFERROR(LOOKUP(1E+100,SEARCH(tblFilterList5[Filter List],[@Gene]),tblFilterList5[Filter List]),"")
F10:H10	F10	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J102)
F15:H15	F15	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J422)
F21:H21	F21	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J62)

This is the example of the file
thnkz for the suggestion Fluff

Fluff · May 28, 2021

Sorry but I don't understand, doesn't the formula in col I already do what you want?

rodrigobertollo · May 28, 2021

Sorry, I posted what I have been using.

You mentioned what I needed to be done?

CSTMV2 expected Coverage.xlsx

A

B

C

D

E

F

G

H

I

J

K

1

Gene

Transcript

Chr

Start

End

50x

60x

70x

Partial Match Filter

Filter List

2

|PIK3CD/

NM_005026.5

chr1

9710450

9727051

100.00%

ATM/BLM/BRCA1/BRCA2/CBL/CEBPA/CHEK2/DDX41/ETV6/GATA2/IKZF1/MLH1/MSH2/MSH6/NBN/

3

|KIF1B/

NM_015074.3

chr1

10232323

10376592

100.00%

4

|TARDBP/

NM_007375.4

chr1

11013722

11022659

100.00%

5

|MTHFR/

NM_005957.5

chr1

11790674

11803121

38.07%

37.05%

35.72%

6

|PLOD1/

NM_000302.4

chr1

11934774

11974813

100.00%

7

|CTRC/

NM_007272.3

chr1

15438459

15446594

100.00%

8

|SDHB/

NM_003000.3

chr1

17018875

17054024

100.00%

9

|ALPL/

NM_000478.6

chr1

21554076

21577653

100.00%

10

|MLH1/

NM_000249.4

chr3

36993542

37050658

99.35%

99.16%

98.96%

11

|GALE/

NM_000403.4

chr1

23795943

23799012

100.00%

12

|LDLRAP1/

NM_015627.3

chr1

25543693

25566997

100.00%

13

|P3H1/

NM_022356.4

chr1

42746691

42766976

100.00%

14

|SLC2A1/

NM_006516.4

chr1

42927035

42958656

100.00%

15

|BRIP1/

NM_032043.3

chr17

61683290

61861544

99.99%

99.98%

99.95%

16

|PTCH2/

NM_003738.5

chr1

44822409

44842937

100.00%

17

|MUTYH/

NM_001128425.2

chr1

45329300

45340259

100.00%

18

|FOXE3/

NM_012186.3

chr1

47416310

47417280

100.00%

19

|PCSK9/

NM_174936.4

chr1

55039832

55063589

100.00%

20

|ACADM/

NM_000016.6

chr1

75724782

75762768

100.00%

21

|MSH6/

NM_000179.3

chr2

47783228

47806865

100.00%

99.98%

22

|DPYD/

NM_000110.4

chr1

97078970

97920927

95.61%

95.49%

95.34%

23

|AGL/

NM_000028.2

chr1

99851037

99921656

100.00%

Custom Média

Cell Formulas
Range	Formula
F2:H9,F11:H14,F16:H20,F22:H23	F2	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J2)
I2:I23	I2	=IFERROR(LOOKUP(1E+100,SEARCH(tblFilterList5[Filter List],[@Gene]),tblFilterList5[Filter List]),"")
F10:H10	F10	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J102)
F15:H15	F15	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J422)
F21:H21	F21	=AVERAGE('D:\Coisas trabalho\CSTM V2\Validação\[Validação round 1.xlsx]002:016'!J62)

I needed the result from the post above with the string search from this example.

Fluff · May 28, 2021

Ok, thanks for that, how about

+Fluff 1.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

Gene

Transcript

Chr

Start

End

50x

60x

70x

Partial Match Filter

Filter List

2

PIK3CD

NM_005026.5

chr1

9710450

9727051

100.00%

ATM/BLM/BRCA1/BRCA2/CBL/CEBPA/CHEK2/DDX41/ETV6/GATA2/IKZF1/MLH1/MSH2/MSH6/NBN/

3

KIF1B

NM_015074.3

chr1

10232323

10376592

100.00%

4

TARDBP

NM_007375.4

chr1

11013722

11022659

100.00%

5

MTHFR

NM_005957.5

chr1

11790674

11803121

38.07%

37.05%

35.72%

6

PLOD1

NM_000302.4

chr1

11934774

11974813

100.00%

7

CTRC

NM_007272.3

chr1

15438459

15446594

100.00%

8

SDHB

NM_003000.3

chr1

17018875

17054024

100.00%

9

ALPL

NM_000478.6

chr1

21554076

21577653

100.00%

10

MLH1

NM_000249.4

chr3

36993542

37050658

99.35%

99.16%

98.96%

MLH1

11

GALE

NM_000403.4

chr1

23795943

23799012

100.00%

12

LDLRAP1

NM_015627.3

chr1

25543693

25566997

100.00%

13

P3H1

NM_022356.4

chr1

42746691

42766976

100.00%

14

SLC2A1

NM_006516.4

chr1

42927035

42958656

100.00%

15

BRIP1

NM_032043.3

chr17

61683290

61861544

99.99%

99.98%

99.95%

16

PTCH2

NM_003738.5

chr1

44822409

44842937

100.00%

17

MUTYH

NM_001128425.2

chr1

45329300

45340259

100.00%

18

FOXE3

NM_012186.3

chr1

47416310

47417280

100.00%

19

PCSK9

NM_174936.4

chr1

55039832

55063589

100.00%

20

ACADM

NM_000016.6

chr1

75724782

75762768

100.00%

21

MSH6

NM_000179.3

chr2

47783228

47806865

100.00%

99.98%

MSH6

22

DPYD

NM_000110.4

chr1

97078970

97920927

95.61%

95.49%

95.34%

23

AGL

NM_000028.2

chr1

99851037

99921656

100.00%

24

Lists

Cell Formulas
Range	Formula
I2:I23	I2	=IF(ISNA(MATCH(A2,FILTERXML("<k><m>"&SUBSTITUTE($K$2,"/","</m><m>")&"</m></k>","//m"),0)),"",A2)

Reverse-Filter-List-of-Items searching with a string of itens

rodrigobertollo

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

rodrigobertollo

New Member

Fluff

MrExcel MVP, Moderator

rodrigobertollo

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Reverse-Filter-List-of-Items searching with a string of itens

rodrigobertollo

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

rodrigobertollo

New Member

Fluff

MrExcel MVP, Moderator

rodrigobertollo

New Member

Fluff

MrExcel MVP, Moderator

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