+ Reply to Thread
Results 1 to 10 of 10

formula for column G searcched, exclusion of value in column E if values are repeated

  1. #1
    Registered User
    Join Date
    05-14-2020
    Location
    Madrid
    MS-Off Ver
    Office 2016
    Posts
    5

    formula for column G searcched, exclusion of value in column E if values are repeated

    Hello together,

    looking for a formula, really hope you can help me out. You can find the example file attached, here's the case:

    Target:
    complete column G contains the same formula
    only in the green marked fields (G3, G8, G13, G19, etc) appears a value, the remaining yellow fields remain blank "" (G4-7, G9-12, G14-18, etc.)
    value in column E can only be considered 1 time

    general Condition for the formula:
    to be calculated: value in column E minus value in column F (E3-F3=G3)
    if column A, C and E are equal in the following rows, only the value in column F should be counted (means column E can only be used for calculation once) = if A3 and C3 and E3 equal A8 and C8 and E8 then F3=G3
    there can be multiple entries (no limitation) with the same information of column A, C and E

    If the values of column A, C and E are blank, column G also should be blank

    important:
    between the entries are different amounts of rows considered (for example line 3 to line 7 = 4 rows; line 13 to line 18 = 5 rows)
    -> can vary between only 2 rows up to 10 rows difference

    I hope the problem is understood and I hope you can help me out!
    Thanks a lot!
    Attached Files Attached Files
    Last edited by treewithgreenleaves; 05-15-2020 at 03:54 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: formula wanted - complex if condition

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done, and explain what the IF statement is meant to achieve.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. I would normally do this for you, as you are new, but I am not clear on the requirement.)
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    05-14-2020
    Location
    Madrid
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: formula wanted - complex if condition

    Hello AliGW,

    thank you for your fast response. I am trying to change the title, not sure though how to describe it in just a few words. Maybe you can even give me a hint here.

    So here is the case:
    I have a very long list of data and I am trying to find the right formula to calculate the values in column "G" (see the picture).
    The value base for the calculation result in column G (where I look the formula for) comes from column E and F.

    But column E can contain repeatedly data, and I want to make sure that, if the value is repeated, it is only considered once in the calculation (only counts as repeatedly data when the data in column A, C and E are repeated).

    Does that explanation makes it more clear?

    Also, the data is linked to a few other files and makros, so it necessarily need to be column G to be updated with the correct data. As this is the only column where I am able to manually change input and put a formula in.
    Last edited by treewithgreenleaves; 05-14-2020 at 02:12 PM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: formula for column G searcched, exclusion of value in column E if values are repeated

    There are instructions at the top of the page explaining how to attach your sample workbook.

  5. #5
    Registered User
    Join Date
    05-14-2020
    Location
    Madrid
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: formula for column G searcched, exclusion of value in column E if values are repeated

    I already tried to attach the file at the beginning, but it is not working.
    I get an error message "Upload Errors" Upload of file failed, which is why I attached a picture to make my question better understand.
    I would really appreciate some help. thanks a lot.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: formula for column G searcched, exclusion of value in column E if values are repeated

    If the file is too big, then read the instructions again and prepare a much smaller representative sample file.

  7. #7
    Registered User
    Join Date
    05-14-2020
    Location
    Madrid
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: formula for column G searcched, exclusion of value in column E if values are repeated

    Hello Ali,
    it finally worked out now and the example file is attached. Does the sheet help you understand my problem?
    Greetings and thank you again, Anna

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: formula for column G searcched, exclusion of value in column E if values are repeated

    In G3 copied down:

    =IF(B3="","",IF(COUNTIFS($C$3:$C3,C3,$D$3:$D3,D3,$E$3:$E3,E3)=1,E3-F3,F3))

  9. #9
    Registered User
    Join Date
    05-14-2020
    Location
    Madrid
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: formula for column G searcched, exclusion of value in column E if values are repeated

    Dear Ali,

    thank you so much, this is a really cool formula, it worked out!
    Only, I realized i forgot to consider one more criteria, is there any possibility to make it a little more complex and include one more argument?

    I try to explain and also uploaded the updated Excel with the additional argument:
    If C3 and E3 are equal to C8 and E8, but A3 and F3 differ to A8 and F8, then F3 should only be considered once (when it first appears)

    so there are 3 possible calculations to get the correct result:
    =E3+F3-G3 - if its the first argument (argument covered by the solution provided by you)
    =G8 - if the value has already been considered (argument covered by the solution provided by you)
    =F13-G13 - additional argument

    I tried to find a solution by my own but I am not able to get a proper result.

    Thanks again,
    tree wgl
    Attached Files Attached Files

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

    Re: formula for column G searcched, exclusion of value in column E if values are repeated

    Based on the narrative in post #9 it seems that the following modification of Ali's formula is what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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.

+ 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 wanted for a table please
    By Andrewmark in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2016, 05:31 AM
  2. Formula wanted
    By George R in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-24-2013, 01:21 PM
  3. Replies: 1
    Last Post: 06-17-2012, 03:26 PM
  4. Help wanted to create a formula
    By flds in forum Excel General
    Replies: 1
    Last Post: 02-03-2010, 07:31 PM
  5. The right Formula to get result wanted
    By kaseyleigh in forum Excel General
    Replies: 1
    Last Post: 10-29-2009, 06:14 AM
  6. Formula help wanted
    By LPlate in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2009, 07:41 AM
  7. help wanted for macro used in cell formula
    By jérome Yacc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2005, 01:44 PM

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