Solved excel challenge comparing two data sets

July 17, 2015 at 17:32:06
Specs: Windows 7
hi guys,

I have different data sets from two different software programs these two programs share data.
these are bookkeeping entries.

debet credit
bank 100
bank1 200.18

soft 100
soft 200.18

the first data set consist of the last two entries of soft but then like a a lot of entries of soft. the second data set consist als of soft

debet credit
soft 300.18
person1 100
person2 200.18

i have to match soft of the first data set that consist of soft 200.18 and 100 with the second data set dat consist of the same soft but the sum of the individuals soft.

can sombody give me an idea on how to look at these problems in excel.

See More: excel challenge comparing two data sets

Report •

July 17, 2015 at 20:34:58
We probably have a language issue here, so it may a few tries before we can offer a solution. At this point I am totally confused by your request. Perhaps you could post some more examples of your input along with the desired output. We want to help, really we do!

Please click on the following line and read the instructions on how to post example data in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 18, 2015 at 02:36:17
thanks for your fast reply derbydad and excus me for mine language. I forgot to say that i am working with excel 2003.

  DATA SET 1            DATA SET 2
       A      B              D       E 
 1    soft   100.33         soft    150
 2    soft   200            soft    700
 3    soft   75             soft    300.33 
 4    soft   75             soft    222.12
 5    soft   200            soft    225
 6    soft   250            soft    356
 7    soft   250            soft    850

the idea is to match for example A1 A2&B1 B2 FROM DATA SET 1 WITH D3 E3 FROM DATA SET 2. D3 E3 from data set 2 is the sum of A1 A2&B1 B2 from data set 1. Same goes for A5,6,7 &B,5,6,7=D2E2.

Report •

July 18, 2015 at 13:32:04
✔ Best Answer
This site claims to have a VBA solution that works and also offers a template that uses the built in Excel Solver feature.

Perhaps you could adapt your data and/or the solutions offered to put together something that works.

The VBA solution is pretty complex, so I would think that adapting your data layout to fit the code would be easier than adapting the code to fit your data. I haven't had a chance to try any of the suggested solutions, I'm just passing them along. I may give them a try later/tomorrow.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

July 18, 2015 at 16:50:17
thank you so much for the link derbydad y tried ti used the template with the solver solucion but its gives load model invalid constraint formula besides it only uses one target amount and i am looking for a way to match a serie of amounts to a serie of target totals from those amounts.

Report •

Report •

July 19, 2015 at 01:53:08
Yes i used the VBA code and this code gives al possible matches for a target amount. Using one value multiple times to come up witth combinations for the chosen target amount. This would not be feasible for mine data set because i am looking for a way that it uses the vaules from data set 1 only once to match one target amount in data set 2. In the course of the week i wil put a example that is more clearer. I really appreciate your feedback derbydad and i am thankful.

Report •

Ask Question