How to extract records that contains a specific text words? 🙏🙌

L

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
163
Office Version
  1. 365
Platform
  1. Windows
Dear Guys, 🙌

Accordingly with criteria (Customer Name+Month) how to extract records (Product Name+Units) that contains a specific text words abbreviation in column range C4:C7) ?
Hope someone could help.
Thank you very much!!! 🙏🙏👍👍🍻🍻

Search by Name_2.xls
ABCDEFGHIJ
1
2CRITERIADATA BASE
3Customer NameMonthProduct Name AbbreviationEXCLUSIVESCustomer NameMonthProduct NameUnits
4BOADICEAfeb. 2023Rosuva+EzetANFASBDKfeb. 2023ESPIRONOLACTONA 100 MG - 60 COMP.1
5Amlod+OlmesBDKBDKfeb. 2023SINVASTATINA 20 MG - 60 COMP.80
6Amlod+Olmes+HctzBOADICEABDKfeb. 2023AMLODIPINA 10 mg - 60 Com20
7RosuvaCREEDBDKfeb. 2023PRAVASTATINA 20 MG - 60 COMP.2
8FLAMENCBDKfeb. 2023NIFEDIPINA 20 MG - 60 COMP.5
9MARCBDKfeb. 2023SINVASTATINA 40 MG - 60 COMP.2
10MEMOIRBDKfeb. 2023SERTRALINA 50 MG - 60 COMP.24
11XERJOFFBDKfeb. 2023SERTRALINA 100 MG - 60 COMP.20
12BDKfeb. 2023RAMIPRIL 2,5 MG - 56 CAPS.2
13BDKfeb. 2023RAMIPRIL 5 MG - 56 CAPS.6
14BDKfeb. 2023RAMIPRIL 10 MG - 56 CAPS.6
15RESULTSBDKfeb. 2023RAMIPRIL + HCTZ 5 MG/25 MG - 56 COMP.1
16Product NameUnitsBDKfeb. 2023OLANZAPINA 2,5X28 MG4
17BDKfeb. 2023PANTOPRAZOL 20 MG - 56 COMP.GASTRO-RESIST.2
18BDKfeb. 2023PANTOPRAZOL 40 MG - 56 COMP.GASTRO-RESIST2
19BDKfeb. 2023EBASTINA 10MG-20 COMP6
20BDKfeb. 2023QUETIAPINA 25MG 20 COMP12
21BDKfeb. 2023MONTELUCASTE 10MG 28COMP2
22BDKfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.20
23BDKfeb. 2023MIRTAZAPINA 15 MG - 30 COMP.6
24BDKfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C50
25BDKfeb. 2023IRBESARTAN+HCTZ 150 12,5 28MG4
26BDKfeb. 2023IRBESARTAN+HCTZ 300 12,5 28MG2
27BDKfeb. 2023ACIDO IBANDRONICO 150MG 1COMP3
28BDKfeb. 2023CANDESARTAN 16MG 56COMP10
29BDKfeb. 2023CANDESARTAN 32MG 56COMP2
30BDKfeb. 2023LEVETIRACETAM 500MG 60COMP2
31BDKfeb. 2023DONEPEZILO GENERICOS 5MG 56COMP1
32BDKfeb. 2023DONEPEZILO GENERICOS 10MG 56COMP10
33BDKfeb. 2023TELMISARTAN GENERICOS 40MG 28 COMP10
34BDKfeb. 2023TELMISARTAN GENÉRICOS 80MG 28COMP8
35BDKfeb. 2023PARACETAMOL 1000MG 18C3
36BDKfeb. 2023LOSARTAN + HCTZ 50 MG/12,5 MG - 56 COMP.3
37BDKfeb. 2023DESLORATADINA OROD 5MG 203
38BDKfeb. 2023CANDESARTAN + HCTZ 32 / 25 mg 561
39BDKfeb. 2023LOSARTAN 50 MG - 56 COMP.10
40BDKfeb. 2023LOSARTAN 100 MG - 56 COMP.10
41BDKfeb. 2023PERINDOPRIL + INDAPAMIDA 8mg + 2,5mg 30C5
42BDKfeb. 2023TELMISARTAN+HCTZ 40 mg + 12,5 MG 284
43BDKfeb. 2023TELMISARTAN+HCTZ 80 mg + 12,5 MG 2812
44BDKfeb. 2023ARIPIPRAZOL 5 mg 281
45BDKfeb. 2023PREGABALINA 25 mg 561
46BDKfeb. 2023ENALAPRIL + LERCANIDIPINA 20 mg + 10mg566
47BDKfeb. 2023DULOXETINA 60 mg 284
48BDKfeb. 2023ROSUVASTATINA 20 mg 304
49BDKfeb. 2023ROSUVASTATINA 10 mg 604
50BDKfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 564
51BDKfeb. 2023OLMESARTAN+HCTZ 40 + 25 mg 566
52BDKfeb. 2023EZETIMIBA 10 mg 2810
53BDKfeb. 2023OLMESARTAN 20 mg 281
54BDKfeb. 2023OLMESARTAN 40 mg 281
55BDKfeb. 2023QUETIAPINA SR 50 mg 6010
56BDKfeb. 2023OMEPRAZOL 20 MG 5650
57BDKfeb. 2023PALIPERIDONA 9 MG 282
58BDKfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 5616
59BDKfeb. 2023OMEPRAZOL 40 MG 561
60BDKfeb. 2023SINVASTATINA 10 MG - 60 COMP.6
61BDKfeb. 2023GABAPENTINA 100 MG - 60 CAPS.1
62BDKfeb. 2023GABAPENTINA 300 MG - 60 CAPS.4
63BDKfeb. 2023MIRTAZAPINA 15 MG - 60 COMP.ORODISPERS.4
64XERJOFFfeb. 2023FLUCONAZOL 150 MG 15
65XERJOFFfeb. 2023SINVASTATINA 20 MG - 60 COMP.20
66XERJOFFfeb. 2023AMLODIPINA 10 mg - 60 Com8
67XERJOFFfeb. 2023PRAVASTATINA 20 MG - 60 COMP.4
68XERJOFFfeb. 2023AMLOD+OLME+HCTZ 5 MG +20 MG +12,5 MG 56100
69XERJOFFfeb. 2023PRAVASTATINA 40 MG - 60 COMP.2
70XERJOFFfeb. 2023SERTRALINA 50 MG - 60 COMP.10
71XERJOFFfeb. 2023SERTRALINA 100 MG - 60 COMP.12
72XERJOFFfeb. 2023RAMIPRIL 2,5 MG - 56 CAPS.6
73XERJOFFfeb. 2023RAMIPRIL 5 MG - 56 CAPS.8
74XERJOFFfeb. 2023RAMIPRIL + HCTZ 5 MG/25 MG - 56 COMP.4
75XERJOFFfeb. 2023TOPIRAMATO 25 mg 603
76XERJOFFfeb. 2023PANTOPRAZOL 20 MG - 56 COMP.GASTRO-RESIST.6
77XERJOFFfeb. 2023QUETIAPINA 25MG 20 COMP40
78XERJOFFfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.15
79XERJOFFfeb. 2023MIRTAZAPINA 15 MG - 30 COMP.6
80XERJOFFfeb. 2023MIRTAZAPINA 30 MG - 30 COMP.2
81XERJOFFfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C4
82XERJOFFfeb. 2023IRBESARTAN+HCTZ 300 12,5 28MG3
83XERJOFFfeb. 2023CANDESARTAN 8MG 28COMP5
84XERJOFFfeb. 2023CANDESARTAN 32MG 56COMP2
85XERJOFFfeb. 2023DONEPEZILO GENERICOS 5MG 56COMP5
86XERJOFFfeb. 2023DONEPEZILO GENERICOS 10MG 56COMP10
87XERJOFFfeb. 2023TELMISARTAN GENERICOS 40MG 28 COMP6
88XERJOFFfeb. 2023TELMISARTAN GENÉRICOS 80MG 28COMP2
89XERJOFFfeb. 2023CANDESARTAN + HCTZ 32 / 25 mg 566
90XERJOFFfeb. 2023LOSARTAN 50 MG - 56 COMP.8
91XERJOFFfeb. 2023TELMISARTAN+HCTZ 40 mg + 12,5 MG 286
92XERJOFFfeb. 2023TELMISARTAN+HCTZ 80 mg + 12,5 MG 286
93XERJOFFfeb. 2023PREGABALINA 25 mg 563
94XERJOFFfeb. 2023PREGABALINA 150 mg 564
95XERJOFFfeb. 2023ROSUVASTATINA 20 mg 308
96XERJOFFfeb. 2023ROSUVASTATINA 10 mg 605
97XERJOFFfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 568
98XERJOFFfeb. 2023OLMESARTAN+HCTZ 20 + 25 mg 563
99XERJOFFfeb. 2023OLMESARTAN 20 mg 284
100XERJOFFfeb. 2023QUETIAPINA SR 50 mg 6010
101XERJOFFfeb. 2023TADALAFIL 20 mg 42
102XERJOFFfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 5615
103XERJOFFfeb. 2023SITAGLIPTINA 100MG284
104XERJOFFfeb. 2023SINVASTATINA + EZETIMIBA 40 + 10 mg 282
105XERJOFFfeb. 2023DUTASTERIDA+TANSULOSINAGENERIC 0,5mg+0,4mg302
106XERJOFFfeb. 2023TADALAFIL 20 mg 122
107XERJOFFfeb. 2023VILDAGLIPTINA 50MG562
108XERJOFFfeb. 2023SINVASTATINA 10 MG - 60 COMP.12
109XERJOFFfeb. 2023METFORMINA + VILDAGLIPTINA 1000 + 50 mg 603
110XERJOFFfeb. 2023ROSUVASTATINA + EZETIMIBA 20 + 10 mg 604
111XERJOFFfeb. 2023INDAPAMIDA 1,5 MG - 30 COMP. LIB. PROLONG.15
112XERJOFFfeb. 2023MIRTAZAPINA 45 MG - 30 COMP.ORODISPERS.3
113FLAMENCfeb. 2023ATENOLOL 50 MG - 60 COMP.3
114FLAMENCfeb. 2023ESPIRONOLACTONA 100 MG - 60 COMP.2
115FLAMENCfeb. 2023SINVASTATINA 20 MG - 60 COMP.1
116FLAMENCfeb. 2023AMLODIPINA 10 mg - 60 Com15
117FLAMENCfeb. 2023SERTRALINA 50 MG - 60 COMP.16
118FLAMENCfeb. 2023SERTRALINA 100 MG - 60 COMP.6
119FLAMENCfeb. 2023VENLAFAXINA 75 MG - 30 CAPS.LIB.PROLONG.3
120FLAMENCfeb. 2023OLANZAPINA 2,5X28 MG1
121FLAMENCfeb. 2023LOSARTAN + HCTZ 100 MG/25 MG 28 COMP.5
122FLAMENCfeb. 2023QUETIAPINA 25MG 20 COMP8
123FLAMENCfeb. 2023LOSARTAN + HCTZ 100 mg/12,5 mg 28 com3
124FLAMENCfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.12
125FLAMENCfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C1
126FLAMENCfeb. 2023ACIDO IBANDRONICO 150MG 1COMP1
127FLAMENCfeb. 2023LEVETIRACETAM 500MG 60COMP3
128FLAMENCfeb. 2023OLANZAPINA ODT 10X28 MG1
129FLAMENCfeb. 2023LOSARTAN + HCTZ 50 MG/12,5 MG - 56 COMP.5
130FLAMENCfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 562
131FLAMENCfeb. 2023LOSARTAN 50 MG - 56 COMP.3
132FLAMENCfeb. 2023LOSARTAN 100 MG - 56 COMP.1
133FLAMENCfeb. 2023QUETIAPINA SR 50 mg 601
134FLAMENCfeb. 2023QUETIAPINA SR 200 mg 601
135FLAMENCfeb. 2023OMEPRAZOL 20 MG 5613
136FLAMENCfeb. 2023SINVASTATINA 10 MG - 60 COMP.3
137FLAMENCfeb. 2023MIRTAZAPINA 15 MG - 60 COMP.ORODISPERS.1
138FLAMENCfeb. 2023VIT C LARANJA 20 COM1
139BOADICEAfeb. 2023ROSUVASTATINA + EZETIMIBA 5 + 10 mg 304
140MEMOIRfeb. 2023AMLODIPINA 10 mg - 60 Com2
141MEMOIRfeb. 2023FLUOXETINA 20 MG - 56 CAPS.3
142MEMOIRfeb. 2023SINVASTATINA 40 MG - 60 COMP.3
143MEMOIRfeb. 2023RAMIPRIL 5 MG - 56 CAPS.1
144MEMOIRfeb. 2023TOPIRAMATO 50 mg 601
145MEMOIRfeb. 2023VENLAFAXINA 37,5 MG - 30 CAPS.LIB.PROLONG.2
146MEMOIRfeb. 2023OLANZAPINA 5X28 MG8
147MEMOIRfeb. 2023PANTOPRAZOL 40 MG - 56 COMP.GASTRO-RESIST1
148MEMOIRfeb. 2023EBASTINA 10MG-20 COMP1
149MEMOIRfeb. 2023MONTELUCASTE 10MG 28COMP2
150MEMOIRfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C20
151MEMOIRfeb. 2023NIMESULIDA 100 MG - 10 COMP.2
152MEMOIRfeb. 2023IRBESARTAN+HCTZ 300 12,5 28MG1
153MEMOIRfeb. 2023LERCANIDIPINA 10 mg 562
154MEMOIRfeb. 2023CANDESARTAN 8MG 28COMP4
155MEMOIRfeb. 2023CANDESARTAN 16MG 56COMP3
156MEMOIRfeb. 2023CANDESARTAN 32MG 56COMP1
157MEMOIRfeb. 2023ESOMEPRAZOL 20MG 56COMP1
158MEMOIRfeb. 2023TELMISARTAN GENÉRICOS 80MG 28COMP2
159MEMOIRfeb. 2023PARACETAMOL 1000MG 18C200
160MEMOIRfeb. 2023OLANZAPINA ODT 10X28 MG1
161MEMOIRfeb. 2023OLANZAPINA ODT 20 MG 282
162MEMOIRfeb. 2023DESLORATADINA COMPRIMIDOS 5MG 203
163MEMOIRfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 563
164MEMOIRfeb. 2023CANDESARTAN + HCTZ 32 / 25 mg 561
165MEMOIRfeb. 2023LOSARTAN 50 MG - 56 COMP.4
166MEMOIRfeb. 2023TELMISARTAN+HCTZ 80 mg + 25 MG 283
167MEMOIRfeb. 2023PREGABALINA 25 mg 561
168MEMOIRfeb. 2023PREGABALINA 50 mg 561
169MEMOIRfeb. 2023PREGABALINA 100 mg 845
170MEMOIRfeb. 2023DULOXETINA 30 mg 285
171MEMOIRfeb. 2023DULOXETINA 60 mg 283
172MEMOIRfeb. 2023ROSUVASTATINA 5 mg 602
173MEMOIRfeb. 2023ROSUVASTATINA 20 mg 306
174MEMOIRfeb. 2023ROSUVASTATINA 10 mg 604
175MEMOIRfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 562
176MEMOIRfeb. 2023OLMESARTAN 20 mg 282
177MEMOIRfeb. 2023OLMESARTAN 40 mg 281
178MEMOIRfeb. 2023QUETIAPINA SR 50 mg 602
179MEMOIRfeb. 2023QUETIAPINA SR 200 mg 601
180MEMOIRfeb. 2023QUETIAPINA SR 300 mg 601
181MEMOIRfeb. 2023ETORICOXIB 90 mg 202
182MEMOIRfeb. 2023ETORICOXIB 60 mg 201
183MEMOIRfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 561
184MEMOIRfeb. 2023ROSUVASTATINA + EZETIMIBA 10 + 10 mg 602
185MEMOIRfeb. 2023VIT C LARANJA 20 COM6
186ANFASfeb. 2023SINVASTATINA 20 MG - 60 COMP.1
187ANFASfeb. 2023AZITROMICINA 500 MG - 3 COMP.5
188ANFASfeb. 2023FLUOXETINA 20 MG - 56 CAPS.1
189ANFASfeb. 2023NIFEDIPINA 20 MG - 60 COMP.2
190ANFASfeb. 2023VENLAFAXINA 75 MG - 30 CAPS.LIB.PROLONG.2
191ANFASfeb. 2023OLANZAPINA 5X28 MG6
192ANFASfeb. 2023OLANZAPINA 10X28 MG8
193ANFASfeb. 2023PANTOPRAZOL 20 MG - 56 COMP.GASTRO-RESIST.2
194ANFASfeb. 2023QUETIAPINA 300MG 60 COMP1
195ANFASfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.6
196ANFASfeb. 2023MIRTAZAPINA 30 MG - 30 COMP.3
197ANFASfeb. 2023OLANZAPINA ODT 5X28 MG2
198ANFASfeb. 2023LOSARTAN + HCTZ 50 MG/12,5 MG - 56 COMP.4
199ANFASfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 562
200ANFASfeb. 2023LOSARTAN 100 MG - 56 COMP.1
201ANFASfeb. 2023TELMISARTAN+HCTZ 80 mg + 25 MG 282
202ANFASfeb. 2023ROSUVASTATINA 10 mg 603
203ANFASfeb. 2023QUETIAPINA SR 50 mg 601
204ANFASfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 565
205ANFASfeb. 2023SITAGLIPTINA 50MG281
206ANFASfeb. 2023PITAVASTATINA 4 MG 281
207MARCfeb. 2023ROSUVASTATINA + EZETIMIBA 20 + 10 mg 604
208CREEDfeb. 2023ESPIRONOLACTONA 100 MG - 60 COMP.2
209CREEDfeb. 2023AMLODIPINA 10 mg - 60 Com10
210CREEDfeb. 2023SERTRALINA 50 MG - 60 COMP.6
211CREEDfeb. 2023SERTRALINA 100 MG - 60 COMP.15
212CREEDfeb. 2023TOPIRAMATO 25 mg 604
213CREEDfeb. 2023VENLAFAXINA 37,5 MG - 30 CAPS.LIB.PROLONG.3
214CREEDfeb. 2023OLANZAPINA 2,5X28 MG6
215CREEDfeb. 2023OLANZAPINA 5X28 MG3
216CREEDfeb. 2023OLANZAPINA 10X28 MG10
217CREEDfeb. 2023EBASTINA 10MG-20 COMP5
218CREEDfeb. 2023QUETIAPINA 25MG 20 COMP40
219CREEDfeb. 2023LOSARTAN + HCTZ 100 mg/12,5 mg 28 com4
220CREEDfeb. 2023MONTELUCASTE 10MG 28COMP10
221CREEDfeb. 2023RISPERIDONA 1 MG - 60 COMP.3
222CREEDfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.10
223CREEDfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C15
224CREEDfeb. 2023LERCANIDIPINA 10 mg 566
225CREEDfeb. 2023LERCANIDIPINA 20 mg 284
226CREEDfeb. 2023OLANZAPINA ODT 5X28 MG6
227CREEDfeb. 2023OLANZAPINA ODT 10X28 MG15
228CREEDfeb. 2023DESLORATADINA OROD 5MG 2010
229CREEDfeb. 2023DESLORATADINA COMPRIMIDOS 5MG 2010
230CREEDfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 564
231CREEDfeb. 2023PERINDOPRIL + INDAPAMIDA 4mg + 1,25mg 30C10
232CREEDfeb. 2023GLICLAZIDA 60 mg 3010
233CREEDfeb. 2023ROSUVASTATINA 10 mg 606
234CREEDfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 564
235CREEDfeb. 2023OLMESARTAN 20 mg 282
236CREEDfeb. 2023OMEPRAZOL 20 MG 566
237CREEDfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 566
238CREEDfeb. 2023DUTASTERIDA+TANSULOSINAGENERIC 0,5mg+0,4mg3010
239CREEDfeb. 2023SINVASTATINA 10 MG - 60 COMP.4
240CREEDfeb. 2023ROSUVASTATINA + EZETIMIBA 10 + 10 mg 608
241CREEDfeb. 2023MIRTAZAPINA 15 MG - 60 COMP.ORODISPERS.6
242CREEDfeb. 2023VIT C LARANJA 20 COM4
243
Sheet1
Cell Formulas
RangeFormula
F4:F11F4=SORT(UNIQUE(G4:G242))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A4List=$F$4:$F$12
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
With the following macro you can put in the criteria: one or more customers, one or more months and, of course, one or more products, as shown in the following example:

Dante Amor
ABC
1
2CRITERIA
3Customer NameMonthProduct Name Abbreviation
4BDKfeb. 2023cande
5XERJOFFmar. 2023rosu
6apr. 2023Amlod+Olmes
7may. 2023
Hoja3


The macro requires 3 columns to carry out the process, starting in cell M3, if you do not have cell M3 available, you can change in this line of the macro the M3 for the cell you have available and 2 more columns to the right:
Rich (BB code): 
With Range("M3")

Try this:
VBA Code: 
Sub extract_records()
  Dim a As Variant, b As Variant
  Dim i&, j&, k&, m&
    
  a = Range("A4:C14").Value
  ReDim b(1 To UBound(a) * UBound(a) * UBound(a), 1 To 3)
  
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" Then
      For j = 1 To UBound(a)
        If a(j, 2) <> "" Then
          For k = 1 To UBound(a)
            If a(k, 3) <> "" Then
              m = m + 1
              b(m, 1) = a(i, 1)
              b(m, 2) = a(j, 2)
              b(m, 3) = "*" & Replace(a(k, 3), "+", "*+*") & "*"
            End If
          Next
        End If
      Next
    End If
  Next
  
  Range("A17:B" & Rows.Count).ClearContents
  With Range("M3")
    .Resize(1, 3).EntireColumn.Clear
    .Resize(1, 3).Value = Range("G3:I3").Value
    .Offset(1).Resize(m, 3).Value = b
    Range("G3:J" & Range("G" & Rows.Count).End(3).Row).AdvancedFilter _
      Action:=xlFilterCopy, CriteriaRange:=.Resize(m + 1, 3), _
      CopyToRange:=Range("A16:B16"), Unique:=False
    .Resize(1, 3).EntireColumn.Clear
  End With
End Sub
----- --
 
Last edited:
Upvote 0
Dear @DanteAmor đź‘Ť

Thank you very much for your kidness.🙏
Although I have an Excel Language Tranlsator Im afraid that some formula names are missed in the process and thats why cannot make the vba code works.
Is it possible to make it work with the standard excel formula?
Hope so.
Thank you very much again.👍🍻
 
Upvote 0
Lacan said:
Im afraid that some formula names are missed
Click to expand...
I don't understand what you mean by formula names.

I have no problems with the data sample that you put in the original post.
You just have to put your data in the CRITERIA cells. And run the macro.


HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (extract_records) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

----- --
Lacan said:
Is it possible to make it work with the standard excel formula?
Click to expand...
Hopefully someone can help you with the formulas...
 
Upvote 0
Dear @DanteAmor

Perhaps didnt explain right.
In table below will show the results thats my goal.
After that will go to next last step. Think its more easier to begin with that.
PLease let me know if its clear for you?
Thanks a lot.

Search by Name_2.xlsm
ABCDEFGHIJ
1
2CRITERIADATA BASE
3Customer NameMonthProduct Name AbbreviationEXCLUSIVESCustomer NameMonthProduct NameUnits
4BDKfeb. 2023sinvaANFASBDKfeb. 2023ESPIRONOLACTONA 100 MG - 60 COMP.1
5BDKBDKfeb. 2023SINVASTATINA 20 MG - 60 COMP.80
6BOADICEABDKfeb. 2023AMLODIPINA 10 mg - 60 Com20
7CREEDBDKfeb. 2023PRAVASTATINA 20 MG - 60 COMP.2
8FLAMENCBDKfeb. 2023NIFEDIPINA 20 MG - 60 COMP.5
9MARCBDKfeb. 2023SINVASTATINA 40 MG - 60 COMP.2
10MEMOIRBDKfeb. 2023SERTRALINA 50 MG - 60 COMP.24
11XERJOFFBDKfeb. 2023SERTRALINA 100 MG - 60 COMP.20
12BDKfeb. 2023RAMIPRIL 2,5 MG - 56 CAPS.2
13BDKfeb. 2023RAMIPRIL 5 MG - 56 CAPS.6
14BDKfeb. 2023RAMIPRIL 10 MG - 56 CAPS.6
15RESULTSBDKfeb. 2023RAMIPRIL + HCTZ 5 MG/25 MG - 56 COMP.1
16Product NameUnitsBDKfeb. 2023OLANZAPINA 2,5X28 MG4
17SINVASTATINA 10 MG - 60 COMP.6BDKfeb. 2023PANTOPRAZOL 20 MG - 56 COMP.GASTRO-RESIST.2
18SINVASTATINA 20MG - 60 COMP.80BDKfeb. 2023PANTOPRAZOL 40 MG - 56 COMP.GASTRO-RESIST2
19SINVASTATINA 40 MG - 60 COMP.4BDKfeb. 2023EBASTINA 10MG-20 COMP6
20BDKfeb. 2023QUETIAPINA 25MG 20 COMP12
21BDKfeb. 2023MONTELUCASTE 10MG 28COMP2
22BDKfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.20
23BDKfeb. 2023MIRTAZAPINA 15 MG - 30 COMP.6
24BDKfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C50
25BDKfeb. 2023IRBESARTAN+HCTZ 150 12,5 28MG4
26BDKfeb. 2023IRBESARTAN+HCTZ 300 12,5 28MG2
27BDKfeb. 2023ACIDO IBANDRONICO 150MG 1COMP3
28BDKfeb. 2023CANDESARTAN 16MG 56COMP10
29BDKfeb. 2023CANDESARTAN 32MG 56COMP2
30BDKfeb. 2023LEVETIRACETAM 500MG 60COMP2
31BDKfeb. 2023DONEPEZILO GENERICOS 5MG 56COMP1
32BDKfeb. 2023SINVASTATINA 40 MG - 60 COMP.2
33BDKfeb. 2023DONEPEZILO GENERICOS 10MG 56COMP10
34BDKfeb. 2023TELMISARTAN GENERICOS 40MG 28 COMP10
35BDKfeb. 2023TELMISARTAN GENÉRICOS 80MG 28COMP8
36BDKfeb. 2023PARACETAMOL 1000MG 18C3
37BDKfeb. 2023LOSARTAN + HCTZ 50 MG/12,5 MG - 56 COMP.3
38BDKfeb. 2023DESLORATADINA OROD 5MG 203
39BDKfeb. 2023CANDESARTAN + HCTZ 32 / 25 mg 561
40BDKfeb. 2023LOSARTAN 50 MG - 56 COMP.10
41BDKfeb. 2023LOSARTAN 100 MG - 56 COMP.10
42BDKfeb. 2023PERINDOPRIL + INDAPAMIDA 8mg + 2,5mg 30C5
43BDKfeb. 2023TELMISARTAN+HCTZ 40 mg + 12,5 MG 284
44BDKfeb. 2023TELMISARTAN+HCTZ 80 mg + 12,5 MG 2812
45BDKfeb. 2023ARIPIPRAZOL 5 mg 281
46BDKfeb. 2023PREGABALINA 25 mg 561
47BDKfeb. 2023ENALAPRIL + LERCANIDIPINA 20 mg + 10mg566
48BDKfeb. 2023DULOXETINA 60 mg 284
49BDKfeb. 2023ROSUVASTATINA 20 mg 304
50BDKfeb. 2023ROSUVASTATINA 10 mg 604
51BDKfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 564
52BDKfeb. 2023OLMESARTAN+HCTZ 40 + 25 mg 566
53BDKfeb. 2023EZETIMIBA 10 mg 2810
54BDKfeb. 2023OLMESARTAN 20 mg 281
55BDKfeb. 2023OLMESARTAN 40 mg 281
56BDKfeb. 2023QUETIAPINA SR 50 mg 6010
57BDKfeb. 2023OMEPRAZOL 20 MG 5650
58BDKfeb. 2023PALIPERIDONA 9 MG 282
59BDKfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 5616
60BDKfeb. 2023OMEPRAZOL 40 MG 561
61BDKfeb. 2023SINVASTATINA 10 MG - 60 COMP.6
62BDKfeb. 2023GABAPENTINA 100 MG - 60 CAPS.1
63BDKfeb. 2023GABAPENTINA 300 MG - 60 CAPS.4
64BDKfeb. 2023MIRTAZAPINA 15 MG - 60 COMP.ORODISPERS.4
65XERJOFFfeb. 2023FLUCONAZOL 150 MG 15
66XERJOFFfeb. 2023SINVASTATINA 20 MG - 60 COMP.20
67XERJOFFfeb. 2023AMLODIPINA 10 mg - 60 Com8
68XERJOFFfeb. 2023PRAVASTATINA 20 MG - 60 COMP.4
69XERJOFFfeb. 2023AMLOD+OLME+HCTZ 5 MG +20 MG +12,5 MG 56100
70XERJOFFfeb. 2023PRAVASTATINA 40 MG - 60 COMP.2
71XERJOFFfeb. 2023SERTRALINA 50 MG - 60 COMP.10
72XERJOFFfeb. 2023SERTRALINA 100 MG - 60 COMP.12
73XERJOFFfeb. 2023RAMIPRIL 2,5 MG - 56 CAPS.6
74XERJOFFfeb. 2023RAMIPRIL 5 MG - 56 CAPS.8
75XERJOFFfeb. 2023RAMIPRIL + HCTZ 5 MG/25 MG - 56 COMP.4
76XERJOFFfeb. 2023TOPIRAMATO 25 mg 603
77XERJOFFfeb. 2023PANTOPRAZOL 20 MG - 56 COMP.GASTRO-RESIST.6
78XERJOFFfeb. 2023QUETIAPINA 25MG 20 COMP40
79XERJOFFfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.15
80XERJOFFfeb. 2023MIRTAZAPINA 15 MG - 30 COMP.6
81XERJOFFfeb. 2023MIRTAZAPINA 30 MG - 30 COMP.2
82XERJOFFfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C4
83XERJOFFfeb. 2023IRBESARTAN+HCTZ 300 12,5 28MG3
84XERJOFFfeb. 2023CANDESARTAN 8MG 28COMP5
85XERJOFFfeb. 2023CANDESARTAN 32MG 56COMP2
86XERJOFFfeb. 2023DONEPEZILO GENERICOS 5MG 56COMP5
87XERJOFFfeb. 2023DONEPEZILO GENERICOS 10MG 56COMP10
88XERJOFFfeb. 2023TELMISARTAN GENERICOS 40MG 28 COMP6
89XERJOFFfeb. 2023TELMISARTAN GENÉRICOS 80MG 28COMP2
90XERJOFFfeb. 2023CANDESARTAN + HCTZ 32 / 25 mg 566
91XERJOFFfeb. 2023LOSARTAN 50 MG - 56 COMP.8
92XERJOFFfeb. 2023TELMISARTAN+HCTZ 40 mg + 12,5 MG 286
93XERJOFFfeb. 2023TELMISARTAN+HCTZ 80 mg + 12,5 MG 286
94XERJOFFfeb. 2023PREGABALINA 25 mg 563
95XERJOFFfeb. 2023PREGABALINA 150 mg 564
96XERJOFFfeb. 2023ROSUVASTATINA 20 mg 308
97XERJOFFfeb. 2023ROSUVASTATINA 10 mg 605
98XERJOFFfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 568
99XERJOFFfeb. 2023OLMESARTAN+HCTZ 20 + 25 mg 563
100XERJOFFfeb. 2023OLMESARTAN 20 mg 284
101XERJOFFfeb. 2023QUETIAPINA SR 50 mg 6010
102XERJOFFfeb. 2023TADALAFIL 20 mg 42
103XERJOFFfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 5615
104XERJOFFfeb. 2023SITAGLIPTINA 100MG284
105XERJOFFfeb. 2023SINVASTATINA + EZETIMIBA 40 + 10 mg 282
106XERJOFFfeb. 2023DUTASTERIDA+TANSULOSINAGENERIC 0,5mg+0,4mg302
107XERJOFFfeb. 2023TADALAFIL 20 mg 122
108XERJOFFfeb. 2023VILDAGLIPTINA 50MG562
109XERJOFFfeb. 2023SINVASTATINA 10 MG - 60 COMP.12
110XERJOFFfeb. 2023METFORMINA + VILDAGLIPTINA 1000 + 50 mg 603
111XERJOFFfeb. 2023ROSUVASTATINA + EZETIMIBA 20 + 10 mg 604
112XERJOFFfeb. 2023INDAPAMIDA 1,5 MG - 30 COMP. LIB. PROLONG.15
113XERJOFFfeb. 2023MIRTAZAPINA 45 MG - 30 COMP.ORODISPERS.3
114FLAMENCfeb. 2023ATENOLOL 50 MG - 60 COMP.3
115FLAMENCfeb. 2023ESPIRONOLACTONA 100 MG - 60 COMP.2
116FLAMENCfeb. 2023SINVASTATINA 20 MG - 60 COMP.1
117FLAMENCfeb. 2023AMLODIPINA 10 mg - 60 Com15
118FLAMENCfeb. 2023SERTRALINA 50 MG - 60 COMP.16
119FLAMENCfeb. 2023SERTRALINA 100 MG - 60 COMP.6
120FLAMENCfeb. 2023VENLAFAXINA 75 MG - 30 CAPS.LIB.PROLONG.3
121FLAMENCfeb. 2023OLANZAPINA 2,5X28 MG1
122FLAMENCfeb. 2023LOSARTAN + HCTZ 100 MG/25 MG 28 COMP.5
123FLAMENCfeb. 2023QUETIAPINA 25MG 20 COMP8
124FLAMENCfeb. 2023LOSARTAN + HCTZ 100 mg/12,5 mg 28 com3
125FLAMENCfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.12
126FLAMENCfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C1
127FLAMENCfeb. 2023ACIDO IBANDRONICO 150MG 1COMP1
128FLAMENCfeb. 2023LEVETIRACETAM 500MG 60COMP3
129FLAMENCfeb. 2023OLANZAPINA ODT 10X28 MG1
130FLAMENCfeb. 2023LOSARTAN + HCTZ 50 MG/12,5 MG - 56 COMP.5
131FLAMENCfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 562
132FLAMENCfeb. 2023LOSARTAN 50 MG - 56 COMP.3
133FLAMENCfeb. 2023LOSARTAN 100 MG - 56 COMP.1
134FLAMENCfeb. 2023QUETIAPINA SR 50 mg 601
135FLAMENCfeb. 2023QUETIAPINA SR 200 mg 601
136FLAMENCfeb. 2023OMEPRAZOL 20 MG 5613
137FLAMENCfeb. 2023SINVASTATINA 10 MG - 60 COMP.3
138FLAMENCfeb. 2023MIRTAZAPINA 15 MG - 60 COMP.ORODISPERS.1
139FLAMENCfeb. 2023VIT C LARANJA 20 COM1
140BOADICEAfeb. 2023ROSUVASTATINA + EZETIMIBA 5 + 10 mg 304
141MEMOIRfeb. 2023AMLODIPINA 10 mg - 60 Com2
142MEMOIRfeb. 2023FLUOXETINA 20 MG - 56 CAPS.3
143MEMOIRfeb. 2023SINVASTATINA 40 MG - 60 COMP.3
144MEMOIRfeb. 2023RAMIPRIL 5 MG - 56 CAPS.1
145MEMOIRfeb. 2023TOPIRAMATO 50 mg 601
146MEMOIRfeb. 2023VENLAFAXINA 37,5 MG - 30 CAPS.LIB.PROLONG.2
147MEMOIRfeb. 2023OLANZAPINA 5X28 MG8
148MEMOIRfeb. 2023PANTOPRAZOL 40 MG - 56 COMP.GASTRO-RESIST1
149MEMOIRfeb. 2023EBASTINA 10MG-20 COMP1
150MEMOIRfeb. 2023MONTELUCASTE 10MG 28COMP2
151MEMOIRfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C20
152MEMOIRfeb. 2023NIMESULIDA 100 MG - 10 COMP.2
153MEMOIRfeb. 2023IRBESARTAN+HCTZ 300 12,5 28MG1
154MEMOIRfeb. 2023LERCANIDIPINA 10 mg 562
155MEMOIRfeb. 2023CANDESARTAN 8MG 28COMP4
156MEMOIRfeb. 2023CANDESARTAN 16MG 56COMP3
157MEMOIRfeb. 2023CANDESARTAN 32MG 56COMP1
158MEMOIRfeb. 2023ESOMEPRAZOL 20MG 56COMP1
159MEMOIRfeb. 2023TELMISARTAN GENÉRICOS 80MG 28COMP2
160MEMOIRfeb. 2023PARACETAMOL 1000MG 18C200
161MEMOIRfeb. 2023OLANZAPINA ODT 10X28 MG1
162MEMOIRfeb. 2023OLANZAPINA ODT 20 MG 282
163MEMOIRfeb. 2023DESLORATADINA COMPRIMIDOS 5MG 203
164MEMOIRfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 563
165MEMOIRfeb. 2023CANDESARTAN + HCTZ 32 / 25 mg 561
166MEMOIRfeb. 2023LOSARTAN 50 MG - 56 COMP.4
167MEMOIRfeb. 2023TELMISARTAN+HCTZ 80 mg + 25 MG 283
168MEMOIRfeb. 2023PREGABALINA 25 mg 561
169MEMOIRfeb. 2023PREGABALINA 50 mg 561
170MEMOIRfeb. 2023PREGABALINA 100 mg 845
171MEMOIRfeb. 2023DULOXETINA 30 mg 285
172MEMOIRfeb. 2023DULOXETINA 60 mg 283
173MEMOIRfeb. 2023ROSUVASTATINA 5 mg 602
174MEMOIRfeb. 2023ROSUVASTATINA 20 mg 306
175MEMOIRfeb. 2023ROSUVASTATINA 10 mg 604
176MEMOIRfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 562
177MEMOIRfeb. 2023OLMESARTAN 20 mg 282
178MEMOIRfeb. 2023OLMESARTAN 40 mg 281
179MEMOIRfeb. 2023QUETIAPINA SR 50 mg 602
180MEMOIRfeb. 2023QUETIAPINA SR 200 mg 601
181MEMOIRfeb. 2023QUETIAPINA SR 300 mg 601
182MEMOIRfeb. 2023ETORICOXIB 90 mg 202
183MEMOIRfeb. 2023ETORICOXIB 60 mg 201
184MEMOIRfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 561
185MEMOIRfeb. 2023ROSUVASTATINA + EZETIMIBA 10 + 10 mg 602
186MEMOIRfeb. 2023VIT C LARANJA 20 COM6
187ANFASfeb. 2023SINVASTATINA 20 MG - 60 COMP.1
188ANFASfeb. 2023AZITROMICINA 500 MG - 3 COMP.5
189ANFASfeb. 2023FLUOXETINA 20 MG - 56 CAPS.1
190ANFASfeb. 2023NIFEDIPINA 20 MG - 60 COMP.2
191ANFASfeb. 2023VENLAFAXINA 75 MG - 30 CAPS.LIB.PROLONG.2
192ANFASfeb. 2023OLANZAPINA 5X28 MG6
193ANFASfeb. 2023OLANZAPINA 10X28 MG8
194ANFASfeb. 2023PANTOPRAZOL 20 MG - 56 COMP.GASTRO-RESIST.2
195ANFASfeb. 2023QUETIAPINA 300MG 60 COMP1
196ANFASfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.6
197ANFASfeb. 2023MIRTAZAPINA 30 MG - 30 COMP.3
198ANFASfeb. 2023OLANZAPINA ODT 5X28 MG2
199ANFASfeb. 2023LOSARTAN + HCTZ 50 MG/12,5 MG - 56 COMP.4
200ANFASfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 562
201ANFASfeb. 2023LOSARTAN 100 MG - 56 COMP.1
202ANFASfeb. 2023TELMISARTAN+HCTZ 80 mg + 25 MG 282
203ANFASfeb. 2023ROSUVASTATINA 10 mg 603
204ANFASfeb. 2023QUETIAPINA SR 50 mg 601
205ANFASfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 565
206ANFASfeb. 2023SITAGLIPTINA 50MG281
207ANFASfeb. 2023PITAVASTATINA 4 MG 281
208MARCfeb. 2023ROSUVASTATINA + EZETIMIBA 20 + 10 mg 604
209CREEDfeb. 2023ESPIRONOLACTONA 100 MG - 60 COMP.2
210CREEDfeb. 2023AMLODIPINA 10 mg - 60 Com10
211CREEDfeb. 2023SERTRALINA 50 MG - 60 COMP.6
212CREEDfeb. 2023SERTRALINA 100 MG - 60 COMP.15
213CREEDfeb. 2023TOPIRAMATO 25 mg 604
214CREEDfeb. 2023VENLAFAXINA 37,5 MG - 30 CAPS.LIB.PROLONG.3
215CREEDfeb. 2023OLANZAPINA 2,5X28 MG6
216CREEDfeb. 2023OLANZAPINA 5X28 MG3
217CREEDfeb. 2023OLANZAPINA 10X28 MG10
218CREEDfeb. 2023EBASTINA 10MG-20 COMP5
219CREEDfeb. 2023QUETIAPINA 25MG 20 COMP40
220CREEDfeb. 2023LOSARTAN + HCTZ 100 mg/12,5 mg 28 com4
221CREEDfeb. 2023MONTELUCASTE 10MG 28COMP10
222CREEDfeb. 2023RISPERIDONA 1 MG - 60 COMP.3
223CREEDfeb. 2023ESPIRONOLACTONA 25 MG - 60 COMP.10
224CREEDfeb. 2023CLOPIDOGREL GENERICOS 75MG 28C15
225CREEDfeb. 2023LERCANIDIPINA 10 mg 566
226CREEDfeb. 2023LERCANIDIPINA 20 mg 284
227CREEDfeb. 2023OLANZAPINA ODT 5X28 MG6
228CREEDfeb. 2023OLANZAPINA ODT 10X28 MG15
229CREEDfeb. 2023DESLORATADINA OROD 5MG 2010
230CREEDfeb. 2023DESLORATADINA COMPRIMIDOS 5MG 2010
231CREEDfeb. 2023CANDESARTAN + HCTZ 16 / 12,5 mg 564
232CREEDfeb. 2023PERINDOPRIL + INDAPAMIDA 4mg + 1,25mg 30C10
233CREEDfeb. 2023GLICLAZIDA 60 mg 3010
234CREEDfeb. 2023ROSUVASTATINA 10 mg 606
235CREEDfeb. 2023OLMESARTAN+HCTZ 20 + 12,5 mg 564
236CREEDfeb. 2023OLMESARTAN 20 mg 282
237CREEDfeb. 2023OMEPRAZOL 20 MG 566
238CREEDfeb. 2023AMLODIPINA+OLMESARTAN MEDOXOMILO 5MG+20MG 566
239CREEDfeb. 2023DUTASTERIDA+TANSULOSINAGENERIC 0,5mg+0,4mg3010
240CREEDfeb. 2023SINVASTATINA 10 MG - 60 COMP.4
241CREEDfeb. 2023ROSUVASTATINA + EZETIMIBA 10 + 10 mg 608
242CREEDfeb. 2023MIRTAZAPINA 15 MG - 60 COMP.ORODISPERS.6
243CREEDfeb. 2023VIT C LARANJA 20 COM4
Sheet1
Cell Formulas
RangeFormula
F4:F11F4=SORT(UNIQUE(G4:G243))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
A4:A5List=$F$4:$F$12
 
Upvote 0
Lacan said:
Perhaps didnt explain right.
In table below will show the results thats my goal.
After that will go to next last step. Think its more easier to begin with that.
PLease let me know if its clear for you?
Thanks a lot.
Click to expand...
I can't help you with a standard excel formula.
I offer you my help with the macro.
If you don't want to use the macro, no problem.
Wait until someone can help you with a standard excel formula.

If you still want to try the macro, then follow the instructions in post #4 and run the macro in post #2.
Forget cell M3, just run the macro.
Put your data as you explained in post #5 and run the macro.
The macro will put the results in the cells of columns A and B from row 17 onwards.
 
Upvote 0
Dear @DanteAmor

Sorry it works but have to run macro everytime that change criteria in my work its not quite pratical and not time efficient.
How can it reproduce in a standard excel formula so it can generate immediatly results?
Thanks again Bro.🙏👍👍
 
Upvote 0
Lacan said:
Sorry it works but have to run macro everytime that change criteria
Click to expand...

So that my macro works in automatic, that is, every time you modify the criteria range, in automatic my macro will be executed. Put the following code in the sheet events:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A4:C13")) Is Nothing Then
    Call extract_records
  End If
End Sub
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Also change the macro to the following:
VBA Code: 
Sub extract_records()
  Dim a As Variant, b As Variant
  Dim i&, j&, k&, m&
    
  a = Range("A4:C14").Value
  ReDim b(1 To UBound(a) * UBound(a) * UBound(a), 1 To 3)
  
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" Then
      For j = 1 To UBound(a)
        If a(j, 2) <> "" Then
          For k = 1 To UBound(a)
            If a(k, 3) <> "" Then
              m = m + 1
              b(m, 1) = a(i, 1)
              b(m, 2) = a(j, 2)
              b(m, 3) = "*" & Replace(a(k, 3), "+", "*+*") & "*"
            End If
          Next
        End If
      Next
    End If
  Next
  
  Range("A17:B" & Rows.Count).ClearContents
  If m > 0 Then
    With Range("M3")
      .Resize(1, 3).EntireColumn.Clear
      .Resize(1, 3).Value = Range("G3:I3").Value
      .Offset(1).Resize(m, 3).Value = b
      Range("G3:J" & Range("G" & Rows.Count).End(3).Row).AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=.Resize(m + 1, 3), _
        CopyToRange:=Range("A16:B16"), Unique:=False
      .Resize(1, 3).EntireColumn.Clear
    End With
  End If
End Sub

Lacan said:
How can it reproduce in a standard excel formula so it can generate immediatly results?
Click to expand...
I can't help you with standard formulas, as I told you, if you don't want the macro, no problem, just be patient and wait for someone to help you with standard formulas for excel 365.
 
Upvote 0
DanteAmor said:
So that my macro works in automatic, that is, every time you modify the criteria range, in automatic my macro will be executed. Put the following code in the sheet events:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A4:C13")) Is Nothing Then
    Call extract_records
  End If
End Sub
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Also change the macro to the following:
VBA Code: 
Sub extract_records()
  Dim a As Variant, b As Variant
  Dim i&, j&, k&, m&
   
  a = Range("A4:C14").Value
  ReDim b(1 To UBound(a) * UBound(a) * UBound(a), 1 To 3)
 
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" Then
      For j = 1 To UBound(a)
        If a(j, 2) <> "" Then
          For k = 1 To UBound(a)
            If a(k, 3) <> "" Then
              m = m + 1
              b(m, 1) = a(i, 1)
              b(m, 2) = a(j, 2)
              b(m, 3) = "*" & Replace(a(k, 3), "+", "*+*") & "*"
            End If
          Next
        End If
      Next
    End If
  Next
 
  Range("A17:B" & Rows.Count).ClearContents
  If m > 0 Then
    With Range("M3")
      .Resize(1, 3).EntireColumn.Clear
      .Resize(1, 3).Value = Range("G3:I3").Value
      .Offset(1).Resize(m, 3).Value = b
      Range("G3:J" & Range("G" & Rows.Count).End(3).Row).AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=.Resize(m + 1, 3), _
        CopyToRange:=Range("A16:B16"), Unique:=False
      .Resize(1, 3).EntireColumn.Clear
    End With
  End If
End Sub


I can't help you with standard formulas, as I told you, if you don't want the macro, no problem, just be patient and wait for someone to help you with standard formulas for excel 365.
Click to expand...
Dear @DanteAmor

Thank you very much for the help.🙏👍
Ok, will wait that someone could hlep with standard formulas for excel 365 for my personal excel level it will be more learnable.

Dear @Dave Patton đź‘Ť
Hope you are fine. Just remember from my last post think you can give a hand in this one?
Very very grateful for your help guys.🙏💪🍻
Big hug.
 
Upvote 0
Hello Guys,

Can you please give a hand?
Think its very simple.
Thank you very much.đź’Şđź‘Ť
Big hug 🍻
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
How to filter function and SUM based on Partial Text Match (SUMIFS with wildcards)
2
Replies
17
Views
754
Lacan
L
L
  • Question
How to extract data using extensions Names and Complete Names ???
Replies
3
Views
339
Lacan
L
SkywardPalm
  • Question
Best Match (Fuzzy Match Alternative) for Product Description and Pulling Respective Product ID Code
Replies
5
Views
539
Akuini
Akuini
L
  • Solved
How to use Filter Function, Unique Function and Large Function all together?
2 3
Replies
21
Views
1K
Lacan
L
R
  • Solved
Extract Unique Values from Multiple Sheets
2
Replies
10
Views
800
Zot
Z

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top