I have a spreadsheet I use as a database of all the resources I might use on a project – suppliers, subcontractors, consultants etc
It is a large sheet as I have all contacts for all departments within a particular company on one line with their office phone no, direct dial,cell/mobile number and email addresses and a lot more details about company registrations, taxes, insurances and other specific details.
So the columns go to from column B to HY and there are about 8500 lines of companies – which I add to regularly. I have 15 “person name” contacts per company set up each 8 columns for the particular person ie the person columns are 9 apart.
As I don’t use some of the resources for long periods I forget the details, so, I am trying to create some wildcard searches – such that I can put in a part of a name and the result will bring up a list of people, companies etc that contain the shortened details I have entered
I have used Countries and US States to illustrate the data in the attached sheet
With company name it is easy – I only have one column to search – so in the first (blue) results column (column G) if I search “AR” in the input cell D5, I get a correct list of all the US States that contain “AR” in column G. Cell D8 tells me how many results I should have – and is currently set for the “person name” based on the later search: it would read 6 in the first column results check.
However if I try and extend the search/results area to column C (to represent a much wider column search for the whole worksheet) by extending B4:B54 to B4:C54 I get an error message in the second results column in column J.
If I concatenate columns B&C and just add a random word in that contains “AR” into column C, the formula in the third results column in column M recognizes that “park” is in column C but adds in the data from Column B so the result is “Californiapark” not “park” ie the formula is recognizing something containing “AR” is in line 9, but not isolating data in the two columns on line 9 to give the correct result.
I then created the list of States, European Countries and African States in Columns S, V and Y to represent a sample of the form of the data for the “person names” in my spreadsheet (where “person names” in each company are separated by detail such as phone number and email columns following the “person name” column for each individual in a company as noted above). I tried again to concatenate the data columns and got gobbledegook as an answer – the fourth results column in column Q – the results are not separated for all three columns.
So, I wondered if creating a defined name for the multiple columns S, V and Y (that represent the “person names” columns in my spreadsheet. In my example I used the defined name “States” for the data in columns S, V and Y. Again I got gobbledegook as an answer in the 5th results column in column T - as per the previous version in column Q.
Desired results are in results column 6 - column W
It could be that the basic formula I am using cannot work with a multi-column array – but I am not proficient enough to come up with a different formula!
I just wondered if anyone had any ideas? I am sure there must be a way to do this – I just can’t find it!
Cheers!
Bookmarks