Results 1 to 4 of 4

Thread: Unigue match

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unigue match

    Hi All, I need to do the computation highlighted in red in the attached file based on a match between two tables with different orientation/format, any ideas how to do it in an automated format. dubdub
    Attached Files Attached Files
    Last edited by dubdub; 2011-10-17 at 06:39.
    TIA
    dubdub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the table has unique entries you can use something like the array formula( confirm with ctrl-shift-enter) in O7:
    =SUM(IF(($A$2:$A$3=$A7)*($B$2:$B$3=$B7)*($C$2:$C$3 =$C7)*($D$2:$D$3=$D7)*($E$2:$E$3=$F7)*($F$1:$T$1=$ E7),$F$2:$T$3))*G7/365

    It can be copied across the columns and down the rows. You will also have to fill in the blanks in the table (cols A-E) so that the formula will have values to lookup. You can use cond formatting to hide the entries to appear as "blanks" if desired.

    If the table must remain blanks in those columns, then you must create intermediate columns for the formulas to lookup so that there are no blanks in the data.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many thanks Steve.

    dubdub
    TIA
    dubdub

  4. #4
    New Lounger
    Join Date
    Oct 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    oh I've encountered the same problem before.. I'll remember this in case I encounter it next time. thanks for the info!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •