Searching similar names in a list and returning all values

[ishpahuja]

Friends,
I am looking for an Excel solution for the problem below:
I have a list of names (A1:A50) with names like this: ABC, PwC, pwc LLP, YYZ, PwC Corp, KKR, pWc Intl.....so on
I want to build a search function where the user will input pwc in a cell (C1) and the result will display all the names which has pwc (pwc can be in the middle or at the end or at the start in any cell and all such full names be displayed).
Can you help me with the formula that will go in this? I tried with Match and Index function but that displays only the first pwc in the list and I don't get all the names.
please guide.
Thanks

 

[AhoyNC]

Maybe something like this:
Copy formula down as needed.

Excel Workbook
	A	B	C
1	ABC		pwc
2	PwC		List
3	pwc LLP		PwC
4	YYZ		pwc LLP
5	PwC Corp		PwC Corp
6	KKR		pWc Intl
7	pWc Intl
Sheet

 

[ishpahuja]

You are God!
Could you please share the logic as in what is happening behind the scene?
Thanks once again, Cheers!

 

[AhoyNC]

Your welcome. Thanks for the feedback and welcome to the forum.

Here is a break down of the formula.
I've shortened the range to just the first 7 rows in the example above to make it easier to see the data.
IFERROR(INDEX($A$1:$A$7,AGGREGATE(15,6,(ROW($A$1:$A$7)-ROW($A$1)+1)/(ISNUMBER(SEARCH($C$1,$A$1:$A$7))),ROWS($G$3:G3))),"")

ROW($A$1:$A$7)-ROW($A$1)+1
this part just returns the row numbers
{1;2;3;4;5;6;7}

SEARCH($C$1,$A$1:$A$7)
This looks for the value in cell C1 and returns the following.
{#VALUE !;1;1;#VALUE !;1;#VALUE !;1}

When you put the ISNUMBER function in front it returns (changing the #VALUE to FALSE).
{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}

(ROW($A$1:$A$7)-ROW($A$1)+1)/(ISNUMBER(SEARCH($C$1,$A$1:$A$7)))
which is:
{1;2;3;4;5;6;7}/{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}
will return
{#DIV /0!;2;3;#DIV /0!;5;#DIV /0!;7} this gives us the row numbers (2,3,5,7) which match pwc and #DIV errors for rows that don't match.

The AGGREGATE(15,6,.....
The 15 is for the SMALL function and the 6 tells AGGREGATE to ignore all errors (the #DIV /0 in this case).

ROWS($G$3:G3) - This is just a counter as you copy it down the rows ROWS($G$3:G3)=1 then ROWS($G$3:G4) =2 etc. This is the "k" value in the small function, in the first cell it returns 2 then row 3, 5 and 7 which are the rows numbers needed for INDEX.

After the first 4 rows in this case we would get an error msg. as no more data matches the pwc. The IFERROR part of the formula just tells the formula to return a "" if there is an error.

 

[sayme]

could be referencing issue with your cells. How you looked into this

 

[Rick Rothstein]

For those who might be interested, here is a macro that will produce the desired list (I used layout AhoyNC posted in Message #2 )...

[table="width: 500"]
[tr]
	[td]Sub PWC()
  Dim Arr As Variant
  Arr = Filter(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), [c1], , vbTextCompare)
  Range("C3").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]

 

[ishpahuja]

ROW($A$1:$A$7)-ROW($A$1)+1
this part just returns the row numbers
{1;2;3;4;5;6;7}

Why would we have ROW($A$1)+1
The value will always be 0, isn't it?

 

[AhoyNC]

No, it returns an array of values {1,2,3,4,5,6,7}.
This formula subtracts Row A1 from Row A1 which equals 0 and then adds 1 to get 1
It then subtracts Row A2 - Row A1 which equals 1 and then adds 1 to get 2

First value is Row(A1)-ROW(A1) = 0 +1 = 1
2nd value in the array becomes ROW(A2)-ROW(A1)=1+1 = 2 etc.

This is an array formula, but by using the AGGREATE function we can just enter the formula with ENTER and don't have to use CTRL-SHIFT-ENTER.