+ Reply to Thread
Results 1 to 5 of 5

Using Multiple Columns to Match against a List then Multiply

  1. #1
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Using Multiple Columns to Match against a List then Multiply

    Hello Excellers,

    I'm currently stuck with a problem that I'm hoping someone can help shed so light on. I receive two different reports one with the proper cost and another that has less cost than the proper cost what I did was take the difference from the proper cost and the other cost to get the difference, but now I need to add that difference back in according to Type and Topping and that is the trouble I'm running into. The goal with this is to get the difference multiplied by the cost based on the matching columns, for example, if column 1 = Pizza and column 2 = Pepperoni then multiply the cost by the value in the table above. I tried using isnumber and search but I was having trouble matching multiple columns. Enclosed is a sample problem of this whereas the actual is thousands of rows in which I'm hoping there is a formula that would help streamline this.

    Thanks for any help with this!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Using Multiple Columns to Match against a List then Multiply

    this can be easily accomplished using Power Query, however, when I do that because in your example you have two soft Tacos with no other distinguishing factors it replicates the additional cost factor for each to the other. If you have multiple soft tacos, you may need to have an additional criterion to match them for the proper increase. Anyway, attached you will find the solution that PQ created by joining the two tables in an outer join.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Excel 2016 (Windows) 64 bit
    I
    J
    K
    L
    12
    Type Topping Cost NewCost
    13
    Pizza Cheese
    77
    78.9
    14
    Pizza Chicken
    120
    124.6
    15
    Pizza Pepperoni
    20
    22
    16
    Pizza Supreme
    33
    35.8
    17
    Pizza Three Cheese
    80
    82
    18
    Tacos Hard
    65
    72.5
    19
    Tacos Soft
    95
    110.6
    20
    Tacos Soft
    15
    15.8
    21
    Tacos Soft
    15
    17.5
    22
    Tacos Soft
    95
    100.3
    Sheet: Topping Sales
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Using Multiple Columns to Match against a List then Multiply

    Hi Alan,

    Thanks for looking into this...A few items of note within the sample is only a part of the table since the actual spreadsheet has over 15 columns and I worry that using power query will disrupt the actual report itself. I have also enclosed updated versions to fix that typo (thanks!)
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Using Multiple Columns to Match against a List then Multiply

    Power Query will not disrupt as it will create a new table in the output. So, if the results do not look as desired, then you can go back into PQ and continue to manipulate the data.

    I have modified the original data in the first worksheet as you have in the second. There was no need to change the PQ Mcode. Once I made the changes to the tables by adding er to soft for the applicable items, I then clicked on Refresh All and PQ ran and updated the output. I have attached for your review.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Using Multiple Columns to Match against a List then Multiply

    Sorry for the delay..Thank you for the additional information this makes things a lot easier dealing with this! Thanks so much for your help...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index Match on multiply columns
    By B02T in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-28-2022, 01:10 PM
  2. Multiply two columns and then sum based on multiple sheet in the same column
    By hkbhansali in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2020, 11:40 AM
  3. If criteria match, multiply then sum across multiply worksheets
    By ciayers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2013, 02:20 PM
  4. [SOLVED] INDEX and MATCH across multiple columns based on dropdown list
    By omni72 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2012, 03:00 AM
  5. Match multiple columns with different values to make a master list of both
    By Niper in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-07-2012, 06:53 AM
  6. Match a condition and multiply columns
    By rniedzia in forum Excel General
    Replies: 2
    Last Post: 02-27-2012, 03:12 PM
  7. Match multiple columns with different values to make a master list of both
    By Niper in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2011, 07:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1