Forum Discussion

MansoorSadat's avatar
MansoorSadat
Copper Contributor
Dec 08, 2022
Solved

Multiple Data in Single Cell with One Corresponding Value In Different Cell

Hi, 

Hoping someone can help here. I have multiple data starting with "BMO" in column AQ but one unique corresponding value (Incident#) in column A. How do I create a list of items from column AQ with their corresponding values in column A? I am not new to excel or VBA, but this is challenging. Ideally I will have one BMO in each row with repeated incident# from column A.

 

Thanks in advance for any help I can get.  

 

 

BI & Data Analysis
excel
Macros and VBA
power bi
  • Lorenzo's avatar
    Lorenzo
    Dec 09, 2022

    MansoorSadat 

     

    (sample attached)

    #1 Format your data range as Table
    #2 Click somewhere in the Table
    #3 Go to Data (tab) > In Get & Transform Data group click From Table/Range (Power Query Editor opens)
    #4 (not mandatory) delete "Changed Type" in APPLIED STEPS
    #5 Select column [BMO]
    #6 Click on Split Column > By Delimiter
       - Check the determined delimiter is OK ortherwise adjust it
       - Click Advanced options > Split into: Rows > OK

     

     

    #7 Click Close & Load at the top left corner of the window

5 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 09, 2022

    Hi MansoorSadat 

     

    Just to clarify things...

    1 Does the following reflect what you expect to acheive where data in B:C is what you currently have an E:F where you want to land?

     

     

    2 What version of Excel do you run and on which OS (Windows, MacOS...)?

    3 Are your data already https://www.bing.com/ck/a?!&&p=bea58f081ee2969dJmltdHM9MTY3MDQ1NzYwMCZpZ3VpZD0zZTg3MzRhZS1iNjU5LTZlYzItMWNlMC0yNmMzYjc3NzZmZGMmaW5zaWQ9NTE3OQ&ptn=3&hsh=3&fclid=3e8734ae-b659-6ec2-1ce0-26c3b7776fdc&psq=excel+format+as+table&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL2Zvcm1hdC1hbi1leGNlbC10YWJsZS02Nzg5NjE5Zi1jODg5LTQ5NWMtOTljMi0yZjk3MWMwZTIzNzA&ntb=1?

    • MansoorSadat's avatar
      MansoorSadat
      Copper Contributor
      Dec 09, 2022
      Hey L z. - Thanks for responding to me.
      1. Yes this is exactly what I need.
      2. Excel 365
      3. Data is not in a table, but can convert if needed...

      Thanks
      Mansoor
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 09, 2022

        MansoorSadat 

         

        (sample attached)

        #1 Format your data range as Table
        #2 Click somewhere in the Table
        #3 Go to Data (tab) > In Get & Transform Data group click From Table/Range (Power Query Editor opens)
        #4 (not mandatory) delete "Changed Type" in APPLIED STEPS
        #5 Select column [BMO]
        #6 Click on Split Column > By Delimiter
           - Check the determined delimiter is OK ortherwise adjust it
           - Click Advanced options > Split into: Rows > OK

         

         

        #7 Click Close & Load at the top left corner of the window

Resources