I have two spreadsheets, one is my company database and the second is a spreadsheet which has manufacturing item details. What I need is something that will find the buyer from the database spreadsheet according to the id number of parts which are in manufacturing item spreadsheet, can someone help me with this?
Dear Marlin,
There is a function called vlookup in excel which can be used to compare data in two different excel sheets having some unique reference present in both the excel sheets. As in your case item number is present in both the sheets, you can use this field to map or compare the rest of the data in both the sheets. Enter = right besides the cell you want the data to be mapped from second sheet. Click on the insert functions menu.
Select Lookup & References in the Select a category drop down list. Then select VLOOKUP from it. The fields required in this function are:
Vlookup(row_num,column_num,abs_num,a1,sheet_text)
Id no of parts should be the leftmost column in both the excel sheets. In the buyer column on the first cell insert = and type vlookup( now it will ask you for lookup_value, your lookup_value will be the first cell of the id no of parts column in the above example it is A2.
Then it will ask you for table_array so after you insert the lookup_value enter a comma and then write the table_array that is the data you have to map against id no of parts in your second excel sheet.
So after entering comma go to the second excel sheet by using the ALT tab function and select the whole column starting from id no of parts of the column where your buyers are listed. As shown below:
Know while selecting the columns just count the number of columns from the id no of parts column, the same is also displayed when you make the selection in the upper right corner. Enter comma now the next field is the col_index_num this will be the number of columns you counted as in the case shown it is 2.
Again enter a comma and the last field is the range_lookup enter 0 for it and close the bracket. All your data will be mapped against the id no of parts.
You can see the formulae in the status bar above for reference.
Hello Marlin,
I have read your concern regarding Microsoft Excel and I recognize that you have come across a problem regarding file searching in both spreadsheets. As an experienced Excel user, it would be easier for you to look for the ID number via manufacturing item by creating a link to each product so that when the ID number will be searched, they can just simply click the “ID number link” to proceed to the manufacturing item.
For example:
If you have 100 ID numbers with the corresponding manufacturing item. You can create a link from ID number 1 which will lead you to the manufacturing item 1. Next, create another link for ID number 2 to its corresponding manufacturing item which is 2 also. You can continue creating links to the last product that needs to be hyperlinked.
You can also sort ID numbers for easy exploration or sort the manufacturing items too. Finding a word or text including numbers can also be done easily via excel or any other Microsoft Office programs. Simply hit “ctrl+f” then the search box will appear. You can type anything you like for fast access to the items.
I hope I am on the right track of understanding your concern and give you a hand to fix the problem. I hope this information is of good help.
Regards,
Mali
