• 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

LisaContractorUmbrella

Umbrella Queen
Joined
Jun 2, 2006
Messages
5,386
Likes received
508
Thanks received
221
Location
Colchester
Morning chaps :wave:

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 :happy
 
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?
 
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 :happy It's unlikely that there would be any combination of entries that would exactly match others
 
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?
 
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
 
Ok, leave it with me. This sounds like a Friday job.
 

Advertisers

Back
Top