• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Help needed from an Excel expert

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Help needed from an Excel expert

    Morning chaps

    Can someone tell me whether or not it's possible to compare two columns of figures and determine which combination of numbers would, when totalled, equal another number. I have 2 worksheets which each have 100+ rows - 2 columns of data have been compared using Index/Match which works well but, in some cases, a number on worksheet 2 will have been entered on worksheet 1 as several different entries:

    Worksheet 2 Worksheet 1

    1000.00 1000.00
    2000.00 1200.00
    1500.00 800.00
    1200.00 1500.00
    1800.00 600.00
    600.00
    1800.00

    The highlighted entries match exactly but those that are not highlighted match but only as a combination.

    I have looked at the Solver Addin but as far as I can see the number to be checked must be entered and I will need to apply any formula you come up with to variable numbers across 100+rows. I have also looked at VBA code but it looks as though the volume of data would make that option unworkable.

    Any help/advice, even if it's "that's impossible" would be gratefully received
    Connect with me on LinkedIn

    Follow us on Twitter.

    ContractorUK Best Forum Advisor 2015

    #2
    NOTHING is impossible in Excel.

    What are you really trying to achieve though?

    What if you have more than one match/ combination?

    What if you have 20, 30 or 50 entries that match only 1 other entry - is there a limit?

    Comment


      #3
      Originally posted by JRCT View Post
      NOTHING is impossible in Excel.

      What are you really trying to achieve though?

      What if you have more than one match/ combination?

      What if you have 20, 30 or 50 entries that match only 1 other entry - is there a limit?
      The 'nothing is impossible' is encouraging It's unlikely that there would be any combination of entries that would exactly match others
      Connect with me on LinkedIn

      Follow us on Twitter.

      ContractorUK Best Forum Advisor 2015

      Comment


        #4
        I understand you want this

        1000 950
        600 1000

        And this:

        310 950
        600 850
        350 430

        What about this?

        310 950
        430 1000
        200 600
        100 900
        120 1200
        60 1100
        40 1650

        Is there a limit to how many entries could make up a single corresponding entry?

        Comment


          #5
          Originally posted by JRCT View Post
          I understand you want this

          1000 950
          600 1000

          And this:

          310 950
          600 850
          350 430

          What about this?

          310 950
          430 1000
          200 600
          100 900
          120 1200
          60 1100
          40 1650

          Is there a limit to how many entries could make up a single corresponding entry?
          sorry no what I want it to return is the total of the numbers that make up the total of the target number if you see what I mean so that both columns of figures match
          Connect with me on LinkedIn

          Follow us on Twitter.

          ContractorUK Best Forum Advisor 2015

          Comment


            #6
            Ok, leave it with me. This sounds like a Friday job.

            Comment


              #7
              Originally posted by JRCT View Post
              Ok, leave it with me. This sounds like a Friday job.
              Thank you
              Connect with me on LinkedIn

              Follow us on Twitter.

              ContractorUK Best Forum Advisor 2015

              Comment

              Working...
              X