Solved: Tricky one: possible to look for a value in a **cell** with many values?

[whats_this]

(Don 't be intimidated by the length of this post; the essence is simple; I'm just a bit talky; I think it gives a clearer picture )

Hi guys. This is my first time here. I've been searching for almost an entire year now for a solution to this, and found nothing. I found your forum. Thank you so much for all the help you've given to people. I hope one of you can find a solution for this one.

I work for a huge multinational company with a massive number of employees. One of my tasks is to maintain a database containing the training certificates the employees are awarded. Regularly (a few times a daily), I get requests from different departments asking if the so and so employee has been awarded something/anything. They always send me excel sheets containing employee numbers and I have to compare their sheet to the master sheet I maintain.

Now here's the tricky part: I have two columns only, one for the certificate number, and the other for the employee numbers (dummy file attached). I put all the employee numbers in one cell, separated by space. Is there a way I can search for a value inside a cell, instead of just simply matching cells? This can easily be done using the search function, but often I get huge numbers of employee numbers, and using Ctrl+F in such cases consumes a ridiculous amount of time.

My question is easier to understand if you open the file.


Thanks in advance.

Alex


P.S. A secondary request is to suggest to me a better way to store the employee numbers. I'm forced to put them all in one cell because some cells have literally hundreds of employee numbers, and that would be easier if it's all in one cell, thanks to the formula bar at the top, instead of scrolling to the right.

 

[bomb #21]

1. I'd say "welcome to the board", but ... this is horrible.

2. What kind of solution are you looking for? Formulas, or are you able to use macros in your environment?

 

[bomb #21]

OK, initial thought follows.

Enter a label Match in Sheet1!A3.

Enter this formula in the cell below:

=FIND($A$1,C4)

, then copy down to A14.

From there you can enter the Sheet 2 numbers sequentially in Sheet1!A1. When you enter the first (1144), the formulas return #VALUE! except for A7 because 1144 is present in C7.

From there it's a question of whether you want this automated/how much is "massive". (?)

 

[slurpee55]

What about using the =SEARCH function to find the data within the listings?

 

[MRdNk]

Is this an Access database or is it in Excel?

I would put this in Access, and re-organise the data, so that each employee has a record and each certificate has a number, and then set-up for database to import your spreadsheet information request employee numbers and get Access to do the work of matching the employee to the certificates.

Its possible to write an Excel macro to separate your data into Employees and Certificate numbers.

 

[Zack Barresse]

Hi there,

Is the data on sheet 1 how you actually have it stored? If not, can we see how you have it stored or can you explain in explicit detail? Initially I'm thinking macro. A one-click solution. We would need to know exactly how you have your data stored, where it's stored, data structure, if the data comes in (people looking for certificates) the same and if it's like you posted in the sample file. The version of Excel you're working with would help, and also if you are the only one who would be using this. If you want to go this route, would an add-in suffice? Or you could always put a routine in your Personal.xls file. Is there any other functions you would like with this file? Would you like a menu structure or custom toolbar? Please list all functionality you would like to see here (whether you think it's possible or not).

 

[whats_this]

bomb #21:

Nah it's alright

Thanks for your reply. What I do now manually is this: I copy a cell containing the employee number, I paste it in the search box, I search, if it is not found, I put "Employee not certified yet" (or anything to that effect), if i find their number, it means they were certified, and so I need to know their certificate number, so I just press Shift+Tab to go the cell before it and copy that certificate number and paste it next to the employee number.

The solution I'm looking for is a function that can automate this; i.e., one that checks to see if the a value in a cell is found **inside** another cell that has many values (in this case, if A7 in sheet 2 is found in column C of sheet 1), and if it is found, it copies the cell next to/before it (B7 in this case).

So say I search for the first employee in sheet 2. His number is 1144. That number is found in C7 of sheet 2. I want Excel to copy B7 for me, so I'd know that the employee's number *does* exist as someone who was awarded a certificate and at the same time I get the number of the certificate he/she were awarded (in this case C007).

I prefer a function. VBA gives me the willies

If there's absolutely no way this can be done outside of VBA, then VBA is fine. Automating the process is what I'm looking for. I do it manually and it's time consuming and exhausting like you wouldn't believe. I have thousands of rows, and each cell has hundreds of employee numbers (sometimes over a thousand in one cell).

=========

slurpee55:

Hi

But after Excel searches for a value inside a cell and finds it, I want it to copy the cell before it.

=========

MrdNk:

Hello

Access is too complicated for me, that's why I prefer Excel.

As for the macro idea, perhaps, but I have thousands of rows, each one containing hundreds of values inside one cell, and it is increasing by the day (literally), so that might complicate things.

=========

Zack Barresse:

Hey dude

Yes, that's exactly how I have it stored: everything in one cell, separated by spaces. My DB tells me of all the training courses we've sent people to and relevant information (employee numbers, subject of the course, where and when they were taken, etc.)

A macro would be fine, but I prefer a function first. If not possible, no problem.

Data structure? Not sure what that means, sorry! But I attached a file to my original post, could the data structure be understood from that?

The data comes in the same: I get excel files with the employee numbers. Each call has only one value, so I copy it and paste it in the search box and look for that value in the other sheet, exactly like in the file.

I have Office 2003.

Yes, I'm the only one using this file. It's not shared or anything. And I have administrator privileges.

An add-on? If it's free, sure, I'd love that.

I'm ok with a macro if there's no function that can do this. And what the function needs to do is look for a value in a cell that has many values, and if it's there, it copies the cell next to it.

Not sure what "menu structure" means, sorry!

Custom toolbar? If it's made from within Excel, great. If it's some third party thing, only if you highly recommend it.



To summarize by example, let's say the manager of the HR department emailed me and said "Hey, I have an employee number, I want to know if he's been on training before or not, and if so, the certificate number". He gives me the number 1234. I search for that number, I find it in a cell that has these values (no quotes): "5566 2233 1234 8899 0000 3111"

See? 1234 is there. Finding the value alone is not sufficient; they need the information about it. So I copy the certificate number, which is in the cell right next to it: Certificate # C0854, for example. Manually that's easy and doesn't take time, but often, I get lists containing dozens, sometimes hundreds of employee numbers. I get an excel sheet with 300 cells in one column, each one with an employee number, and they want to know what certificate they were awarded (if any). Manually, I copy A1, then paste it in the search box and hit Enter, if it's there, I copy the certificate number, if not, I enter "Employee has no certificates". And so on. You can imagine how much time this takes!

 

[whats_this]

BTW, the closest I've come to finding a solution is using the INDEX-MATCH formula. I attached a file for reference.


What this formula does is look for a value in an array, then if it's there, it copies the adjacent cell.


But the problem with this is that it's matching entire cell contents only. I don't know how to look inside a cell (partial match).

 

[Zack Barresse]

Okay, well, see if this works. It's an add-in. I was bored and couldn't sleep anyway. To install, first save to a location where you can remember where it is. I keep all of my add-ins in one location: C:\WINDOWS\ADDINS. It's quick, it's easy, and most of all it's not hidden - which is where the default add-ins are located, in a hidden directory. So I like to Keep It Simple Stupid (KISS - me being the stupid one generally).

Once the add-in is saved to a good location for you, from within Excel go to Tools | Add-Ins. A dialog box will pop up. Click on the Browse button. Navigate to the folder location where you had the add-in saved to, select it and click the Open button. It should appear in the list (Certsearch) with a checkmark next to it. Click Ok.

You should now see a custom toolbar on the top with three buttons on it. It does take into account a default file location for your list of files. Set it once and you're good, you shouldn't have to mess with it after that. Or, you could change it at any time if you'd like, say if you update the file and change it's directory (just a standard update/save shouldn't change anything).

Let us know how it goes.

Edit: Attachment updated to new version.

 

[bomb #21]

Just a couple of points.

1. Enter active cell value as default for Employee Number?

2. Took me c.10 mins to install, since I don't have WinZip.

 

[bomb #21]

... I prefer a function. VBA gives me the willies ...

Don't let it. If an idiot can get to grips with the rudiments of VBA (and I have ), so can you.

Not that I'm suggesting you're an idiot.

Excel does everything by code; ultimately the only difference is who wrote the code -- Bill's minions or someone else. Since the someone else in this case is an MVP, you have nothing to worry about.

 

[Zack Barresse]

Good points bomb! Taken into account and adjusted. Now all you need to do is (after setting the cert workbook) select all of the cert numbers and hit the button, will run through all of them and quit when finished with selection. Still have options to report on each. New file was updated in previous post (as to not post multiple files).

 

[bomb #21]

Good points bomb! Taken into account and adjusted. Now all you need to do is (after setting the cert workbook) select all of the cert numbers and hit the button, will run through all of them and quit when finished with selection. Still have options to report on each.

Something WendyM once said comes to mind:

"Well now you're just showing off".

 

[MRdNk]

Okay, well, see if this works. It's an add-in. I was bored and couldn't sleep anyway. To install, first save to a location where you can remember where it is. I keep all of my add-ins in one location: C:\WINDOWS\ADDINS. It's quick, it's easy, and most of all it's not hidden - which is where the default add-ins are located, in a hidden directory. So I like to Keep It Simple Stupid (KISS - me being the stupid one generally).

Once the add-in is saved to a good location for you, from within Excel go to Tools | Add-Ins. A dialog box will pop up. Click on the Browse button. Navigate to the folder location where you had the add-in saved to, select it and click the Open button. It should appear in the list (Certsearch) with a checkmark next to it. Click Ok.

You should now see a custom toolbar on the top with three buttons on it. It does take into account a default file location for your list of files. Set it once and you're good, you shouldn't have to mess with it after that. Or, you could change it at any time if you'd like, say if you update the file and change it's directory (just a standard update/save shouldn't change anything).

Let us know how it goes.

Edit: Attachment updated to new version.

Good work!

 

[Zack Barresse]

Thank you very much!