• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to link data from one table to excel file

A

Akansha

New Member
Hi


I have data like of 12000+ employees, like their location, project, billing etc and for each of these employees i need to add data of who is their Head manager on the basis of a table.


The table will have data that if an employee belong to X location, Y project and Z billing status then the Head Manager for the employee is ABC. Like this i have many permutations and combinations and over 50 Head Managers. How do I do this task rather than doing it manually everytime.
 
Hi, Akansha!


What are your rules or procedures for assigning a HM to an employee?


I imagine two scenarios, being the most practical this: formula based or table employee-independent

HM=f(Location, Project, BillingStatus, [Employee])


Please validate my assumptions. Having that info, all is about writing down that table and adding column/s with formula/s for each employee.


Regards!
 
Hi, the scenario is as under:


Employee: 1, (location- a, billing status- billed, project- z)

Employee: 2, (location- b, billing status- billed, project- y)........12000 + employees all with different permutation and combinations.


The HM will be as under:

Ram is the HM of all employees in location A, who are billed and part of project z.

Shyam is the the HM of all employees in location B, who are billed and part of project y.

Like this i have 50+ HMs with different permutation and combinations.
 
Hi Akansha ,


Can you give details of all the 50+ HMs along with the workbook itself ?


Given the specific nature of your requirement , I doubt that anyone can give you generic formulae ; whatever solution you are looking for will have to be with reference to the data.


Upload your worksheet , and post the access link here ; as far as the rules for assigning employees to HMs are concerned , see if you can mention them within the workbook itself.


Narayan
 
Hi Akansha,


What appares from your last post is that every HM has unique Location, Project, Billed/Not Billed Status. If it is such, them an employee's Location, Project, Billed/Not Billed Status must match at least one of the 50 HM's data and in that case find HM's Name is simple.


In case that there are overlapping of data or there are data sets with mismatch then there will be problem off course. I am working with fictitious data but would be best as Narayan said if you upload a sample file.


Regards,

Faseeh
 
Hi,


Use a helper column to concatenate the three elements on the first table (12,000 rows), you can use = A2&A3&A4.

Do the same on the table of managers =A2&A3&A4

Now match the two resulting columns and get the data using offset.

'=OFFSET(Sheet1!A1,MATCH(E2,Sheet1!E2:E5,0),0)


Hope this helps
 
You can do with index & match formula.


Index(result u want to display,MATCH('value to be matched A2 & B2',Range to look up for ,FALSE),1)


This is an array formula.


In values to be matched select the cell of employees location, project, billing & Range to look up for select the entire rows & column of head manages details.
 
Hi All


I have uploaded the file here:

http://speedy.sh/x2gKp/Manpower-Dashboard-2.3.2012.xlsx


Sheet 1 is where i need to populate data of HM, and Sheet 2 has the HM Table.


Please help
 
Hi Akansha ,


Put in the following formula in cell F2 on "Sheet 1" tab , and copy down. The formula is an array formula , to be entered using CTRL SHIFT ENTER.


=IFERROR(INDEX('HM Table'!$D$3:$D$24,MATCH(C2&D2&E2,'HM Table'!$A$3:$A$24&'HM Table'!$C$3:$C$24&'HM Table'!$B$3:$B$24,0)),"")


Narayan
 
Hey thanks this works awesome. Just 3 more doubts:

1. Wats an array formula?

2. Incase for certain cases, i want to define the name of the employee for a particular HM is it possible, for eg. employee name Abhishek or an employee id 1382 should have HM as Ashok.

3. Incase i want to learn more about this formula that you have used, where can i refer?


Once again, thanks a ton.
 
Hi Akansha ,


For a detailed explanation of array formulas , and their usage , see the following link :


http://www.xtremevbtalk.com/showthread.php?t=296012


Regarding your second point , I don't see how it can be accommodated within your present system ; your HM Table relates the HM to a particular location , project and billing status. There is no way to specify a HM for a particular employee , unless you have a second table for this. When matching data in Sheet1 , we can first try to do a match within the employee-HM table ; if a match is found , the HM is taken from here ; if no match is found , then a second level of matching is done using the location , project and billing status from the existing HM Table.


Narayan
 
You must log in or register to reply here.
Back
Top