+ Reply to Thread
Results 1 to 7 of 7

Return vertical values to horizontal value

  1. #1
    Registered User
    Join Date
    08-12-2019
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    5

    Return vertical values to horizontal value

    Hi guys, so.. I need to make those values from deposit in table 1 into table 2. So that whenever table 1 is updated with project name and deposit, table 2 will be updated automatically. Values in red are examples of supposedly appear everytime table 1 is updated.

    Untitled.png

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,285

    Re: Return vertical values to horizontal value

    Welcome to the forum.

    Get the PowerQuery add-in, then load table 1 into it - pivot on Project Name with Deposit as the value - advanced > don't aggregate - rename the columns as suits you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    08-12-2019
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    5

    Red face Re: Return vertical values to horizontal value

    Sorry but thanks, I actually need it to be like this;

    IF B4:B15 CONTAINS E3 THEN F3 WILL RETURN 1ST MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E3 THEN G3 WILL RETURN 2ND MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E3 THEN H3 WILL RETURN 3RD MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E3 THEN I3 WILL RETURN 4TH MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E3 THEN J3 WILL RETURN 5TH MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"

    IF B4:B15 CONTAINS E4 THEN F4 WILL RETURN 1ST MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E4 THEN G4 WILL RETURN 2ND MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E4 THEN H4 WILL RETURN 3RD MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E4 THEN I4 WILL RETURN 4TH MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"
    IF B4:B15 CONTAINS E4 THEN J4 WILL RETURN 5TH MINIMUM VALUE OF DEPOSIT IF NOT THEN "0"

    AND SO ON..

    But I cant figure out the formula string for that..

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,285

    Re: Return vertical values to horizontal value

    Do you have the PowerQuery add-in? It's a free download from Microsoft.

    Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  5. #5
    Registered User
    Join Date
    08-12-2019
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    5

    Re: Return vertical values to horizontal value

    Thanks for the advice, so I'm tying to attach the file hopefully its working.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-12-2019
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    5

    Re: Return vertical values to horizontal value

    Sorry for bothering you again but I think I just solved it. I figured it out myself just now. You may take a look at the file attached.

    IF YOU HAVE ANY COMMENT OF HOW I CAN IMPROVE THAT FUNCTION LET ME KNOW. ANYWAY THANKS FOR TRYING TO HELP.
    Attached Files Attached Files

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Return vertical values to horizontal value

    NAXIB,

    Glad you found solution, and thank you for reading us in on what it is.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Dave

+ 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] Similar to transpose values from vertical to horizontal
    By soreno in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2019, 01:15 PM
  2. [SOLVED] SUM of values using horizontal and vertical criteria of a Table
    By d_whys in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-15-2018, 06:37 PM
  3. Sum row values based on horizontal and vertical criteria
    By excelgeek1234 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2018, 11:32 AM
  4. Match vertical and horizontal values and return value
    By Nick2512 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-28-2018, 10:46 AM
  5. [SOLVED] Return Value in Cell by matching Horizontal and vertical names
    By emsiti in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2016, 08:16 AM
  6. Replies: 4
    Last Post: 11-30-2008, 04:55 AM
  7. Return horizontal and vertical values
    By tsanodze in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-27-2008, 07:04 PM

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