Generating New Lines Based On Lookup

[alphabet1111]

Hi! This is my first time on MrExcel, and I'm also new to VBA. I have a rather specific task I need to accomplish in Excel:

• I am working with two tables
  • A large dataset (~160k lines) with various descriptive columns, as well as the obligated spend in various years (2016-2020)
    • One of the descriptive columns is the code column, called "CODE"
  • A table that maps each CODE to a certain market segment - we'll call that column "SEGMENT"
    • Some of the CODE values map to multiple SEGMENTs
    • If a CODE maps to multiple segments, each segment has an associate % comprising the CODE (for example - Code1 might map to Segment1-40% and Segment2-60%)
• I would like to map each line in the dataset to a particular SEGMENT by creating a new segment column in the dataset with the assigned segment. If one of the data rows has a CODE that maps to multiple SEGMENTs, what I would like to do is:
  • Duplicate the data row N times for the N segments that the code maps to
  • Multiply ONLY the spend lines by the percentage associated with each segment, and label each of the new lines with the corresponding segment
  • (If the CODE maps to just one segment, just simply assign the existing line to the segment)

Is this possible to do in PowerQuery or VBA?

 

[jorismoerings]

Hi and welcome to MrExcel,

To answer your question: both is possible. So it's just what you prefer.

A power BI solution is quit simple, I dropped a quick example-file see this link .
If you want a VBA solution we need more info on the way your tables are structured.

If you want the VBA solution, please provide the information on both table using XL2BB. More information on that to be found here

Hope this helps

 

[alphabet1111]

Hi and welcome to MrExcel,

To answer your question: both is possible. So it's just what you prefer.

A power BI solution is quit simple, I dropped a quick example-file see this link .
If you want a VBA solution we need more info on the way your tables are structured.

If you want the VBA solution, please provide the information on both table using XL2BB. More information on that to be found here

Hope this helps

Hi,

Thank you so much for your response - a PowerQuery/PowerBI solution is perfect for me. This looks like exactly what I was looking for! I just downloaded the file and will take a look, but would you mind explaining at a high level what you did?

Thank you so so much!!

 

[jorismoerings]

Hi,

Of course,

1. created two tables - going on the infor ation provided by you.
2. created 2 queries via "Get Data from table"
3. The first table (code segment mapping) was loaded flat, so no manipulation of data
4. The second table is (what you called) the large dataset
5. After loading to Power Query, I merged the Lage datatable with the code segment mapping table based upon the field named "code"
6. Chose to Extended the lines - basically assigning segment and the percentage to the CODE from the original file
7. Inserted a new column named "Segment Spend" - multiplying the Value from the org data file with the assigned percentage hence creating a new value field
8. Removed the original Value column
9. done

You can pretty much see al steps in Power Query.

Click Data - Queries & Connections

Right click on Dataset and choose - edit

 

[alphabet1111]

lines - ba

Hi,

Of course,

1. created two tables - going on the infor ation provided by you.
2. created 2 queries via "Get Data from table"
3. The first table (code segment mapping) was loaded flat, so no manipulation of data
4. The second table is (what you called) the large dataset
5. After loading to Power Query, I merged the Lage datatable with the code segment mapping table based upon the field named "code"
6. Chose to Extended the lines - basically assigning segment and the percentage to the CODE from the original file
7. Inserted a new column named "Segment Spend" - multiplying the Value from the org data file with the assigned percentage hence creating a new value field
8. Removed the original Value column
9. done

You can pretty much see al steps in Power Query.

View attachment 44804 Click Data - Queries & Connections
View attachment 44805Right click on Dataset and choose - edit

Thank you so so much again - this made so much sense. You have save me hours of frustration!!! I almost tried to do this with a convoluted approach in Excel outside of PowerQuery.

 

[jorismoerings]

Glad I could help.
If my post and proposal suits you, please mark it as solution if not already done.