Forum Discussion
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.
(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
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 Formatted as Table?
- 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(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
