+ Reply to Thread
Results 1 to 7 of 7

Grouping by one criteria and then bild values per group based on each different criteria

  1. #1
    Registered User
    Join Date
    01-22-2017
    Location
    Munich, Germany
    MS-Off Ver
    2010
    Posts
    6

    Grouping by one criteria and then bild values per group based on each different criteria

    Hello everybody,

    I wish you all are doing well.

    I've got a problem, which I cannot solve.

    At the moment the forum doesn't let me to upload a file or send a link here. Actually I have a table, which I would like to upload here.

    In this table I would like to group the data based on column NREF.

    So far so good.

    Then I would like to have following columns per group with the following criteria in the same row of the subtotal:
    LF (on of the values, they are always the same per group)
    X- not necessary
    N minimum
    VY absolute maximum

    and so on.

    Does any body have any idea?

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Grouping by one criteria and then bild values per group based on each different criter

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    01-22-2017
    Location
    Munich, Germany
    MS-Off Ver
    2010
    Posts
    6

    Re: Grouping by one criteria and then bild values per group based on each different criter

    Hello Pepe Le Mokko,

    the button for attchment doesn't work for me.

    When I click on it, a tiny windows, just like a very small drop down menu opens and I cannot do anything with it.

  4. #4
    Registered User
    Join Date
    01-22-2017
    Location
    Munich, Germany
    MS-Off Ver
    2010
    Posts
    6

    Re: Grouping by one criteria and then bild values per group based on each different criter

    and when I want to post a linke I get this:

    You are not allowed to post any kinds of links, images or videos until you post a few times.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Grouping by one criteria and then bild values per group based on each different criter

    As per attachments: The paperclip icon does not work. Please follow Pepe's instructions for uploading particularly paying attention to the last line in post #2.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    01-22-2017
    Location
    Munich, Germany
    MS-Off Ver
    2010
    Posts
    6

    Re: Grouping by one criteria and then bild values per group based on each different criter

    Thank you both, I should have read Pepe's post more carefully.

    I think the file is now attached. The file is called "Stützen 1622.xlsx"
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Grouping by one criteria and then bild values per group based on each different criter

    This proposal isn't fully automatic, however it may be a time saver and perhaps someone could write some code (VBA) to more fully automate the process
    1. Column M, headed LF(2), is populated using: =IF(ISNUMBER(K2),B2,B1)
    2. Column N, headed N[kN](2), is populated using: =IF(ISNUMBER(K2),D2,MIN(OFFSET(D2,-1,0,-4)))
    3. Column O, headed VY[kN](2), is populated using: =IF(ISNUMBER(K2),E2,MAX(ABS(MAX(OFFSET(E2,-1,0,-4))),ABS(MIN(OFFSET(E2,-1,0,-4)))))
    4. Select cell M2 and press the Ctrl, Shift and down arrow keys to select the range M2:M2119
    5. Select cell B2 and press the Ctrl, Alt and v keys to bring up the paste special dialog box and select Values and OK
    6. Select cells N2:O2 and press the Ctrl, Shift and down arrow keys to select the range N2:O2119
    7. Select cell D2 and press the Ctrl, Alt and v keys to bring up the paste special dialog box and select Values and OK
    Note that this method has been applied to the "Before" sheet in the attached copy of the file.
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. Formula for grouping based on criteria
    By KelvinT in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-04-2018, 07:28 PM
  2. VBA Macro Dividing Values Based upon Matching Criteria and Description Criteria
    By pvsvprasad in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-18-2016, 12:39 PM
  3. [SOLVED] Grouping Row Values Based On Set Criteria
    By RRa in forum Excel General
    Replies: 6
    Last Post: 06-21-2014, 05:21 AM
  4. [SOLVED] How to sum values based on multiple criteria with multple possibilities for each criteria?
    By boredaxel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-05-2013, 02:26 AM
  5. Replies: 6
    Last Post: 10-18-2012, 03:10 PM
  6. Grouping and counting based on several criteria
    By Stroem in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2012, 05:22 AM
  7. Finding top 2 values per group based on multiple criteria
    By schuc in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-22-2007, 10:51 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