+ Reply to Thread
Results 1 to 10 of 10

How to pick up correct data in multiple rows and columns in a table

  1. #1
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    186

    How to pick up correct data in multiple rows and columns in a table

    We are selling products to over 6 different sales channels, with each product selling over not more than 4 different channels. The cost prices, margins and selling prices are entered in a table (Table 1).

    I am trying to pick up the correct selling prices from this table for each sales channel for each product into another table, as highlighted in yellow in Table 2, using formulas.

    Any help is very much appreciated. Thank you in advance.

    Joseph

    Pick up correct data.png

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,725

    Re: How to pick up correct data in multiple rows and columns in a table

    I used Power Query/Get and Transform found on the Data Tab of the Ribbon to get your expected results.

    Here is the Mcode from those steps

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    Q
    R
    S
    T
    U
    V
    W
    25
    Attribute.1 CV MT FR OT FS GT
    26
    Product A
    16.66666667
    16.85393258
    17.24137931
    17.04545455
    27
    Product B
    22.22222222
    23.52941176
    22.72727273
    28
    Product C
    47.5
    46.34146341
    Sheet: Sheet1

    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced

    File attached

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,228

    Re: How to pick up correct data in multiple rows and columns in a table

    Put in R6 and copied down and cross:

    =IFERROR(INDEX($D$6:$N$8,MATCH("Our Selling Price",$B$6:$B$8,0),MATCH(1,INDEX(($Q6=LOOKUP(COLUMN($C$4:$N$4),COLUMN($C$4:$N$4)/($C$4:$N$4<>""),$C$4:$N$4))*(R$5=$C$5:$N$5),,),0)),IFERROR(INDEX($D$10:$N$12,MATCH("Our Selling Price",$B$10:$B$12,0),MATCH(1,INDEX(($Q6=LOOKUP(COLUMN($C$4:$N$4),COLUMN($C$4:$N$4)/($C$4:$N$4<>""),$C$4:$N$4))*(R$5=$C$9:$N$9),,),0)),""))

  4. #4
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,624

    Re: How to pick up correct data in multiple rows and columns in a table

    Another formula
    R6
    =IF($Q6="","",SUMPRODUCT(--(LOOKUP(COLUMN($C$4:$N$4),COLUMN($C$4:$N$4)/($C$4:$N$4>0))-COLUMN($B$4)&$C$5:$N$9=MATCH($Q6,$C$4:$N$4,)&R$5),$D$8:$O$12))

    Another PQ

    Please Login or Register  to view this content.
    Last edited by Bo_Ry; 07-01-2020 at 12:10 PM.

  5. #5
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    186

    Re: How to pick up correct data in multiple rows and columns in a table

    Thank you alansidman, azum and Bo_Ry for your prompt help! The formulas are a little complicated for me and the steps for PQ are plenty. I am still trying to understand them all!

  6. #6
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    186

    Re: How to pick up correct data in multiple rows and columns in a table

    When I changed the formulas to expand to the whole row, e.g. $C$4:$N$4 to $4:$4 because of the ever expanding products, the formulas don't seem to work any longer. My Selling Price Summary is actually on a separate sheet.

    PQ seems like a good solution, but the problem is user is free to add rows at the top of the data table.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,725

    Re: How to pick up correct data in multiple rows and columns in a table

    PQ seems like a good solution, but the problem is user is free to add rows at the top of the data table.
    Should not be an issue as you are uploading a Table. When data is added to the table, PQ grabs it and updates when you click on Refresh All two times.

  8. #8
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    186

    Re: How to pick up correct data in multiple rows and columns in a table

    Sorry, I did not explain properly. There are other details below the Item and Product rows, and user is free to add new rows there:

    Other Details.png

  9. #9
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    186

    Re: How to pick up correct data in multiple rows and columns in a table

    Quote Originally Posted by alansidman View Post
    Should not be an issue as you are uploading a Table. When data is added to the table, PQ grabs it and updates when you click on Refresh All two times.
    Oh ya, you are right, the PQ is still working correctly even after adding new rows.

  10. #10
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    186

    Re: How to pick up correct data in multiple rows and columns in a table

    I supposed I will just expand the formula from $C$4:$N$4 to $C$4:$ZZ$4. Thanks everyone for your help! Here is a star for you all.

+ 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. Extracting data from a table with multiple rows and columns
    By BranAlex in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-22-2020, 04:26 PM
  2. Date Stamp within Excel table - Changes in multiple rows and columns and new rows added
    By jarheadmctavish in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-09-2016, 03:22 PM
  3. Pick info from Multiple Columns & rows from Unique value
    By Iceman1988 in forum Excel General
    Replies: 13
    Last Post: 03-17-2015, 02:09 PM
  4. Replies: 9
    Last Post: 12-15-2013, 10:05 PM
  5. Excel Macro - Deducing Correct Rule based on multiple rows of data
    By unkle007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-04-2012, 07:05 AM
  6. Replies: 1
    Last Post: 11-29-2012, 06:24 PM
  7. Multiple Vlookup Formulas to get data from correct table
    By JAMES4228 in forum Excel General
    Replies: 3
    Last Post: 07-14-2009, 07:18 AM

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