How to extract data with PARTIAL TEXT CRITERIA based on the NAMES

GerryZ

GerryZ

Well-known Member
Joined
Jul 4, 2014
Messages
1,224
Office Version
  1. 365
Platform
  1. Windows
Hi all
I've a big data base (COLMNS A B C ) an from this data i need to extract all the records with NAME CRITERIA but I can use only a part of the critiria
PARTIAL NAME (PARTIAL TEXT)
thank you very much everybody!



ABCDEFG
Elisa
Serena
Alice
Giorgia
Ilaria
Luisa
Giulia
Alessandra
Federico
Andrea
Elisa
Giada
Alessandra
Martina
Alessandro
Francesca
Lucia
Elisa
Davide
Alessia
Elisa

<COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 76px"><COL style="WIDTH: 72px"><COL style="WIDTH: 56px"><COL style="WIDTH: 24px"><COL style="WIDTH: 78px"><COL style="WIDTH: 66px"><COL style="WIDTH: 67px"></COLGROUP><TBODY>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #C0C0C0"]DATA[/TD]
[TD="bgcolor: #C0C0C0"]NOME[/TD]
[TD="bgcolor: #C0C0C0"]TOTALE[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]03/08/2014[/TD]

[TD="align: right"]€ 49,00[/TD]

[TD="bgcolor: #FF0000"]CRITERIO[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]24/08/2014[/TD]

[TD="align: right"]€ 40,00[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]14/09/2014[/TD]

[TD="align: right"]€ 38,00[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]05/10/2014[/TD]

[TD="align: right"]€ 28,00[/TD]

[TD="bgcolor: #C0C0C0"]DATA[/TD]
[TD="bgcolor: #C0C0C0"]NOME[/TD]
[TD="bgcolor: #C0C0C0"]TOTALE[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]26/10/2014[/TD]

[TD="align: right"]€ 38,00[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]16/11/2014[/TD]

[TD="align: right"]€ 43,00[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]07/12/2014[/TD]

[TD="align: right"]€ 47,00[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]28/12/2014[/TD]

[TD="align: right"]€ 27,00[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: right"]18/01/2015[/TD]

[TD="align: right"]€ 38,00[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: right"]08/02/2015[/TD]

[TD="align: right"]€ 34,00[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: right"]01/03/2015[/TD]

[TD="align: right"]€ 27,00[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: right"]22/03/2015[/TD]

[TD="align: right"]€ 44,00[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: right"]12/04/2015[/TD]

[TD="align: right"]€ 48,00[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: right"]03/05/2015[/TD]

[TD="align: right"]€ 41,00[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: right"]24/05/2015[/TD]

[TD="align: right"]€ 48,00[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: right"]14/06/2015[/TD]

[TD="align: right"]€ 29,00[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: right"]05/07/2015[/TD]

[TD="align: right"]€ 28,00[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: right"]26/07/2015[/TD]

[TD="align: right"]€ 28,00[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="align: right"]16/08/2015[/TD]

[TD="align: right"]€ 54,00[/TD]

[TD="bgcolor: #CACACA, align: center"]21[/TD]
[TD="align: right"]06/09/2015[/TD]

[TD="align: right"]€ 24,00[/TD]

[TD="bgcolor: #CACACA, align: center"]22[/TD]
[TD="align: right"]27/09/2015[/TD]

[TD="align: right"]€ 46,00[/TD]

</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Example criteria GIUL or ALES and extract correspondent data
Thank you!
 
Upvote 0
GerryZ said:
Example criteria GIUL or ALES and extract correspondent data
Thank you!
Click to expand...


[TABLE="width: 557"]
<TBODY>[TR]
[TD="class: xl63, width: 112, bgcolor: silver"]DATA
[/TD]
[TD="class: xl63, width: 102, bgcolor: silver"]NOME
[/TD]
[TD="class: xl63, width: 86, bgcolor: silver"]TOTALE
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-08-03
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Elisa
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 49,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl67, width: 134, bgcolor: red"]CRITERIO
[/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-08-24
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Serena
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 40,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"]ALES
[/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-09-14
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Alice
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 38,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-10-05
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Giorgia
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 28,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl63, width: 134, bgcolor: silver"]DATA
[/TD]
[TD="class: xl63, width: 123, bgcolor: silver"]NOME
[/TD]
[TD="class: xl63, width: 91, bgcolor: silver"]TOTALE
[/TD]
[TD="class: xl63, width: 64, bgcolor: silver"]IDX
[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-10-26
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Ilaria
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 38,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl69, width: 134, bgcolor: white"]2014-12-28
[/TD]
[TD="class: xl64, width: 123, bgcolor: white"]Alessandra
[/TD]
[TD="class: xl64, width: 91, bgcolor: white"]€ 27,00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-11-16
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Luisa
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 43,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl69, width: 134, bgcolor: white"]2015-04-12
[/TD]
[TD="class: xl64, width: 123, bgcolor: white"]Alessandra
[/TD]
[TD="class: xl64, width: 91, bgcolor: white"]€ 48,00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-12-07
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Giulia
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 47,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl69, width: 134, bgcolor: white"]2015-05-24
[/TD]
[TD="class: xl64, width: 123, bgcolor: white"]Alessandro
[/TD]
[TD="class: xl64, width: 91, bgcolor: white"]€ 48,00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15
[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2014-12-28
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Alessandra
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 27,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl69, width: 134, bgcolor: white"]2015-09-06
[/TD]
[TD="class: xl64, width: 123, bgcolor: white"]Alessia
[/TD]
[TD="class: xl64, width: 91, bgcolor: white"]€ 24,00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-01-18
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Federico
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 38,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-02-08
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Andrea
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 34,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-03-01
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Elisa
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 27,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-03-22
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Giada
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 44,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-04-12
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Alessandra
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 48,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-05-03
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Martina
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 41,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-05-24
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Alessandro
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 48,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-06-14
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Francesca
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 29,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-07-05
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Lucia
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 28,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-07-26
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Elisa
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 28,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-08-16
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Davide
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 54,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-09-06
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Alessia
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 24,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl64, width: 134, bgcolor: white"][/TD]
[TD="class: xl64, width: 123, bgcolor: white"][/TD]
[TD="class: xl64, width: 91, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 112, bgcolor: white"]2015-09-27
[/TD]
[TD="class: xl64, width: 102, bgcolor: white"]Elisa
[/TD]
[TD="class: xl66, width: 86, bgcolor: white"]€ 46,00
[/TD]
[TD="class: xl64, width: 33, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

E3: ALES

E6, enter and copy across to G6, and down:
Rich (BB code): 

=IF($H6="","",INDEX($A$2:$C$22,$H6,MATCH(E$5,$A$1:$C$1,0)))

H6, just enter and copy down:
Rich (BB code): 
=IFERROR(AGGREGATE(15,6,(ROW($B$2:$B$22)-ROW($B$2)+1)/
  ISNUMBER(SEARCH($E$3,$B$2:$B$22)),ROWS($H$6:H6)),"")
 
Upvote 0
Woooooooowww!!! Aladin it works fine. I like because I don't have to make CT+SHIFT+ENTER But I want ask you if is possible doing it without helper column? Thank you very much!!
 
Upvote 0
GerryZ said:
Woooooooowww!!! Aladin it works fine. I like because I don't have to make CT+SHIFT+ENTER But I want ask you if is possible doing it without helper column? Thank you very much!!
Click to expand...

You should try to revise your position on the array formulas and on dismissing the idx column. The latter provides for more speed. That said, try:

E6, copied to H6 and down:
Rich (BB code): 
=IFERROR(INDEX(A$2:A$22,AGGREGATE(15,6,(ROW($B$2:$B$22)-ROW($B$2)+1)/
  ISNUMBER(SEARCH($E$3,$B$2:$B$22)),ROWS(E$6:E6))),"")
 
Upvote 0
@ Aladin I prefer this formula

Thank you very helpfully!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
540
mrshuck
M
M
  • Question Question
Average weighted price - checking broker data
Replies
5
Views
382
madeye89
M
airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
1K
airforceone
airforceone
A
  • Question Question
Merge partial character before specific word across sheets based on headers
Replies
3
Views
184
Amer Omar
A
S
  • Solved
Partial text match with wildcards using a reference table
2
Replies
10
Views
867
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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