-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
filtering data that has similar information, not exact?
- Thread starter polyxenne
- Start date
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
polyxenne said:
Try to provide a small sample along with the desired result list.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Re: sample
Why?
D1 must house a 0.
D3, copied down:
=IF((C3<>"")*ISNUMBER(MATCH("*"&C3&"*",$B$3:$B$7,0)),LOOKUP(9.99999999999999E+307,$D$1:D2)+1,"")
F1:
=LOOKUP(9.99999999999999E+307,D3:D6)
F3, copied down:
=IF(ROW()-ROW($F$3)+1<=$F$1,INDEX($B$3:$B$7,MATCH("*"&LOOKUP(ROW()-ROW($F$3)+1,$D$3:$D$6,$C$3:$C$6)&"*",$B$3:$B$7,0)),"")
polyxenne said:
Why?
| Book7 | |||||||
|---|---|---|---|---|---|---|---|
| B | C | D | E | F | |||
| 1 | 0 | 3 | |||||
| 2 | All | Dogs | Match | List | |||
| 3 | jake johnson | jake | 1 | jake johnson | |||
| 4 | david doug | doug | 2 | david doug | |||
| 5 | drew | roger | 3 | roger rabbit | |||
| 6 | ralph | cebe | |||||
| 7 | roger rabbit | ||||||
| 8 | |||||||
Sheet1 | |||||||
D1 must house a 0.
D3, copied down:
=IF((C3<>"")*ISNUMBER(MATCH("*"&C3&"*",$B$3:$B$7,0)),LOOKUP(9.99999999999999E+307,$D$1:D2)+1,"")
F1:
=LOOKUP(9.99999999999999E+307,D3:D6)
F3, copied down:
=IF(ROW()-ROW($F$3)+1<=$F$1,INDEX($B$3:$B$7,MATCH("*"&LOOKUP(ROW()-ROW($F$3)+1,$D$3:$D$6,$C$3:$C$6)&"*",$B$3:$B$7,0)),"")
Upvote
0
