+ Reply to Thread
Results 1 to 17 of 17

Need to find column matches and total values from corresponding rows

  1. #1
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Need to find column matches and total values from corresponding rows

    I've attached a worksheet showing my table with the relevant column info displayed.

    The formula would be located in column Q and needs to be able to look at columns I,J and L and find rows where all three values are the same.
    Where it finds rows with all of these values matched it should then total the corresponding values in column O. I have provided the result
    that I expect throughout column O.

    Example: Q3 is totalling O3,O4 and O5 because the formula would have found that columns I,J and L contain the same information on rows 3,4,5

    I'm not sure where I'd actually start with creating this formula. Whether a LOOKUP would suffice or whether I'd need to INDEX & MATCH. Can
    anyone point me in the right direction? Thanks.
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to find column matches and total values from corresponding rows

    In Q3, then copied down:

    =SUMIFS(O:O,I:I, I3,J:J, J3,L:L, L3)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    Thanks JBeaucaire. I originally posted a simpler version of the formula I actually intended to create, as I thought I'd be able to use the principles in
    the formula that you suggested, =SUMIFS(O:O,I:I, I3,J:J, J3,L:L, L3) to then adapt it into the actual formula I am after. It turns out that
    it is more complicated than I thought.

    I have re-attached an updated version of the spreadsheet with my actual current formula in column Q, which shows the results that I want returned.
    Again, the formula needs to be able to look at columns I,J and L and find rows where all three values are the same.
    Where it finds rows with all of these values matched it should then apply the formula only to those rows. I have provided the result
    that I expect throughout column Q.

    Commonly, matches would be found over 1, 2 or 3 rows and this is where it gets complicated for me, having to create a dynamic formula. If anyone has any suggestions,
    they'd be greatly appreciated. Thanks.

    Dan
    Attached Files Attached Files

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Need to find column matches and total values from corresponding rows

    Hi Dan,

    Am not sure what the reference to 'column U' is for. In any case, please refer to column R that should yield you the results you are after.
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    Thanks Ace_XL. That's exactly what I am after. I've just tried to apply these principles to a new column 'Yield Total', but can't make sense of your original
    formula in Q3 and why it works to even begin with. I've updated the workbook and re-attached it. In a few cells below the table I tried to break down the formula
    from Q3, but, for some reason the result comes out as 34.97% and not 4.37%.

    Anyway, what I wanted to do was to gain the results as displayed in the 'Yield Total' column (highlighted in yellow). Again, I have made the raw calculations that should
    be made if the original conditons are met; "look at columns I,J and L and find rows where all three values are the same. Where it finds rows with all of these values
    matched it should then apply the formula only to those rows."

    Notice that a result is only returned in the last row of the rows where the 3 matches are found (as with column P). In 'column P' I used the following formula and want to incorporate
    it into this formula.

    =IF(SUMIFS(O:O,I:I,I5,J:J,J5,L:L,L5)=SUMIFS(O:O,I:I,I6,J:J,J6,L:L,L6),"--",Otherwise make calculation)

    Thanks again.
    Attached Files Attached Files

  6. #6
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Need to find column matches and total values from corresponding rows

    Have populated desired results in (Column 'T') with the aid of a helper column (Column 'S'). let me know if this helps!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    Thanks again Ace_XL, that's very very helpful and saves me a lot of time. I really appreciate your help. Thanks.

    Dan

  8. #8
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    Is it possible to adjust columns P, Q and T so that they only reference the area within the table. At present I:I, J:J, L:L and O:O are referenced which cause problems when I duplicate the table below (I intend to do this multiple times). I tried to adjust for example, I:I to $I$3:$I$23, but this would mean that any new duplicates of the table will continue to reference $I$3:$I$23 and not the new area of the duplicated table. If I manually adjusted each row of the formula to reference I3:I23 (without '$'), I'd be able to copy my table no problem and everthing would reference correctly, but if I wanted to add new rows to the table, the formula would not copy down automatically because each rows formula would be unique.

    Is there a way around this? Thanks.

  9. #9
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Need to find column matches and total values from corresponding rows

    I'd suggest assigning separate names to the arrays in Columns I,J and O. In that case, if you are creating a duplicate table all that you would need to change is the reference to the arrays.

  10. #10
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    I've assigned names to the arrays using the Name Manager, adapted the formulas so that they reference these arrays (and not the entire columns), and then copied a table down as a test.
    The new table's formulas are referencing the 1st tables named ranges as expected.

    This is an example of one of the original formulas for the 1st table:
    =IF(Q11="","--",SUM((((R11*(Q11-1))*(100%-Y11))-SUMIFS(R:R,L:L,L11,M:M,M11,O:O,O11)+R11)/SUMIFS(R:R,L:L,L11,M:M,M11,O:O,O11)))

    This is an example of one of the adapted formulas for the 1st table:
    =IF(Q11="","--",SUM((((R11*(Q11-1))*(100%-Y11))-SUMIFS(Tab1R,Tab1L,L11,Tab1M,M11,Tab1O,O11)+R11)/SUMIFS(Tab1R,Tab1L,L11,Tab1M,M11,Tab1O,O11)))

    I've named ranges in the following way:
    1st Table Column R:R = Tab1R
    1st Table Column L:L = Tab1L
    1st Table Column M:M = Tab1M
    1st Table Column O:O = Tab1O

    Are you suggesting that I create named ranges for the 2nd table, lets say, as follows:
    Tab2R, Tab2L, Tab2M, Tab2O

    and then manually edit the above formula in the 2nd table to reference these new named ranges?

    I plan on duplicating and using new tables possibly 2 times a day, archiving used tables on another sheet and also giving the sheet to a friend to use who is not very competent in excel. Ideally he would be able to just
    copy and paste the table if and when required without additional work.

    Is it possible to use the original formula, but restrict it to only make calculations in the table that the forumla resides of ranges R:R,L:L,M:M and O:O?

    If not, I could manually give each table a number in the header row and create another helper column that would look up this number. So, for instance Table 1 would have a column with all 1's in and the formula would only
    consider rows with a 1 in the new helper column. Ideally if it's possible for the ranges to only total the table in question, that would be preferable. Thanks.

  11. #11
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Need to find column matches and total values from corresponding rows

    I'd suggest changing the array of the 1st Table to the following assuming that the all your original (Table1) data resides between Row 1-23

    1st Table Column R1:R23 = Tab1R
    1st Table Column L1:L23 = Tab1L
    1st Table Column M1:M23 = Tab1M
    1st Table Column O1:O23 = Tab1O

    Post this, (as you correctly thought of) create named ranges for the 2nd table - Tab2R, Tab2L, Tab2M, Tab2O. However the array for these would be (say) from Row 51-73 instead of the 1-23 used in Table 1 ranges.

    This way once you copy data from Table 1 to Table 2, all you need is to ensure that instead of using Tab1R,Tab1L etc. the corresponding formula cells for this second table refer to Tab2R, Tab2L etc.

    Hope this makes sense!

  12. #12
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    Sorry, my last post was not explained very well. I have assigned the following ranges these names already.

    1st Table Column R1:R23 = Tab1R
    1st Table Column L1:L23 = Tab1L
    1st Table Column M1:M23 = Tab1M
    1st Table Column O1:O23 = Tab1O

    I was just looking for another solution other than this because ideally I just want to copy and paste a table and have no further work to do.

    An example of my set up is:
    'Sheet1' contains the standard table. Over the period of a morning I fill out rows in the table and then it is completed. I want to archive this table and so copy and paste it below. I then clear all the data entries from the 1st table so that I can start a fresh with new information. Once completed, I would then copy and paste this table below the 2nd. The process continues.

    If I were to use named ranges I will have to create new named ranges for each table I archive. That's why I was asking if it was possible to use the original formulas that reference the entire columns, but limit them to only considering data in the table where the formula resides. If not, I'd go down the route of creating another Helper Column (see bottom of my previous post). Thanks.

  13. #13
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Need to find column matches and total values from corresponding rows

    Just a couple of cheeky solutions to save all the trouble with changing names and ranges..

    1. Instead of saving the archive data below the table, couldn't you do this alongside (to the right of the worksheet)?
    2. Save each archive as a separate tab in the worksheet?

    Let me know.

  14. #14
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    Option 1
    1. Instead of saving the archive data below the table, couldn't you do this alongside (to the right of the worksheet)?

    Archive data would ideally be saved in a seperate tab in the workbook. I would like the 1st worksheet to have 10 duplicates of the table ready incase I want to have 10 tables in use at one time. When a table is completed I would then want to archive it. Once a table is archived (wherever it may be put) I would want to reset that original table to blank so that it is ready to be used again.

    Duplicating my 10 tables on the main sheet to the right of each other instead of below could be an option, but it is a lot easier to scroll down through the tables I am currently using than across to the right. I could create hyperlinks, but still scrolling down is a lot more user friendly.

    I could create a seperate tab for the archived tables and copy them side by side, but I intend to analyse and compile data on these tables at a later date. Wouldn't it be easier to do this if the tables were archived vertically rather than horizontally?

    Option 2
    2. Save each archive as a separate tab in the worksheet?

    Wouldn't saving each archive as a seperate tab in the workbook use up a large amount of file space if I have created say, 500 or more tabs over a few months? Would it make it hard to analyse the archived results if they are all on seperate tabs?

    Saving each archived table to a seperate tab, I would still not have solved the problem of wanting 10 tables for current use on the same tab.

    The ideal situation which would allow simple viewing and simple copy and pasting of the tables, would ideally be to have the 1st tab for the 10 tables in current use aligned vertically and the 2nd tab for archived tables with the tables aligned vertically also, if this is possible? As I say, I could create another helper column that would make each table unique to solve the problem.

    Thanks Ace_XL, I appreciate all the help you've been giving me.

  15. #15
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Lightbulb Re: Need to find column matches and total values from corresponding rows

    Hmm..separate name ranges is out of the question due to the number of tables you'll end up with!

    Still being cheeky to save creating some monstrous formulae...so bear with me

    Instead of copying & pasting the archive table right below the preceding table, try pasting it in just one column to the right (alongwith the table headers). Do the same every single time you archive a table. I understand this will not perfectly align with your top table and create a cascading view leaning towards the right of the screen. Will still serve the purpose though and not distort viewing completely.

    Sample worksheet attached.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: Need to find column matches and total values from corresponding rows

    Thanks Ace_XL. I went with your original idea of creating named ranges. I created named ranges for 20 tables, with 19 tables duplicated below the 1st. When I have completed these tables I will copy them to an archive tab and then clear the originals and start again. Thanks for your help, it's greatly appreciated.

    Dan

  17. #17
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Need to find column matches and total values from corresponding rows

    Tedious yet effective solution under the circumstances. Glad to help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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