none
Sharepoint List VLooup and Concatinate Functions RRS feed

  • Question

  • Hi All,

    I am trying to migrate our excel spreadsheet onto a SharePoint list and one issue I am having at the moment is that I cannot find a way to generate our "Work Codes" column. When a new piece of work comes in the code associated is generated by other lookup columns in the sheet eg:


    There is a list of customers:

    • Customer1 = CU1
    • Customer2= CU2 

    A List for Type of Work:

    • Lay Bricks = BRI
    • Remove Rubbish = RUB

    An example of a code would be "CU1BRI" which would be a concatenation of a removal rubbish job for customer one.

    Could anyone help

    Tuesday, February 21, 2017 11:20 AM

All replies

  • Hi Sam,

    In SharePoint, you can create two list, one for customers  and one for the Work. In the customers list, add a lookup field to lookup the work in Work list which is very similiar with Excel Vlookup function.


    Then create a calculated with formula to concatenation the job and customer:

    Concatenate Columns Using Calculated Column in SharePoint

    Thanks

    Best Regards


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, February 22, 2017 9:11 AM
  • Hi Jerry, 

    Thanks for the response. The only problem with that is that I want to look up the customer and return the lookup value to then concatenate.  I can use the lookup feature to search for a customer and then it will allow me to return the lookup in another field in Customer: Lookup Value (As Below) but then I cannot use that field to concatenate. 

    Wednesday, February 22, 2017 5:04 PM
  • Hi Sam,

    In Calculated column, it's not available to use lookup field for concatenating.

    A workaround is get the lookup relationship value in excel using Vlookup function, then import the excel sheet into SharePoint list to make the two fields as text field, then Concatenate the fields in Calculated field.

    Thanks

    Best Regards


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, March 1, 2017 2:59 AM