Some times need to automate your tasks for better results and reduce the time that you need to complete your task. Last week i had to finish a very diffcult task. This is the reason which write the specific article. Let's start from the beginning.
I receive an Excel file from My Manager with 500 lines with name of comapnies and requested to search for the companies in every user folder. If i found any company then must transfer it in new folder.
Imagine that every user folder has minimum 50 subfolders. So the first solution was to check one by one the companies from Excel file with the companies from every user. This is at least one full day work and probably lot of errors.
So i decide to search and automate my task for right results.
I found a very powerfull function in Excel that i didn't know until now. SUMPRODUCT is one of worksheet function that can use it to search text in one cell for many items.
Let's create a scenario for better understanding.
Scenario
- You have an Excel file with 500 lines of product names.
- You have 10 user folders which include minimum 50 subfolders. Every subfolder it is a product name.
- You want to find any product name from Excel file in every user folder.
What suppose that will do. If start to search one by one product names from Excel files with one by one product names from users subfolders maybe 2 days work it's not enough.
Let's see how can proceed
- Open a folder from a user. With Ctrl-A select all the subfolders.
- Keep holding the Shift button and with right click select copy as Path.
- Open the Excel with the Product names and choose cells to paste the product names.
- Because it will paste all the path you can use the replace with CTRL-f to remove the full path.
- Add the full path without the subfolder and in Replace don't type anything.
- Now you will have only the names of subfolders
Select an empty cell and type the following function
=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))>0,"1","")
- Change the $A$1:$A$3 value with the cell range from your product names of the user subfolder.
- Change the C1 with the column which start the product names from your Excel
- The 0,"1" means if not found anything the cell will be empty. If found then the cell return the value 1. These values you can change it if you don't prefer 0 and 1.
- Press Enter.
- Drag down the value until the last line of producy names.
If the cell has value 1 means that user has the specific product name.
I don't want to explain the function with more details. If someone needs more details for the specific function you can send me an email in info@askme4tech.com or commented in the post.
I hope to find intresting my article. It's sure that if you use big Excel files and search will be very usefull function.
Sponsor
>
Free Tools for everyone
