One to many items match

**Pavan_Jack** · 03-09-2012, 03:37 AM

I have a workbook where i need to match many amounts to one amount.. i even tried reading an other post in this forum, but i could not find one.

Please help me out

**dilipandey** · 03-09-2012, 03:47 AM

Hi Pavan,

Welcome to the forum.

Do you want to match many amounts (many cells) to one amount (one column)?
I believe a sample file will facilitate better understanding, click on "Go Advanced" while replying and look for paper clip icon to attach the sample file. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Pavan_Jack** · 08-29-2012, 07:40 AM

Sorry for being so late! am attaching the file now..

**dilipandey** · 08-29-2012, 09:42 AM

Okay.. but you have not mentioned which amount (cell reference) need to be matched with what amounts (range reference) ?

Regards,
DILIPandey

<click on below 'star' if this helps>

**Pavan_Jack** · 08-30-2012, 08:21 AM

Hello,

It is in the column "D".

both positives and negative amounts are in the same column and the offsets should be identified.. and i do not know how!

Pavan.

**dilipandey** · 08-30-2012, 09:20 AM

Hi Pavan,

column D has many values (negatives / positives).. what you need ? do you need the row numbers of next available matches?

See the attached file where I have tried obtaining a match of a cell which is highlighted in yellow.. you can get some hint from that. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Pavan_Jack** · 08-30-2012, 10:18 AM

Hi,

where column D has too many positive and negative values, i would want to find out the direct offset, when summed up would
result in zero. (eg = +45-45)

i have attached another wb wherein i've highlighted cells, which i expect to be my result.

i could not find out a way to crack this

Thanks!
Pavan.

**dilipandey** · 08-30-2012, 11:57 AM

sorry... still not clear.

This data you already have..
column D would always be there..
In column D, there would be some positive and negative number.. that's fine
now what ?

Do you want to highlight the common numbers.. like 45 and then -45 ?
do you want some information to be extracted from column D or form the table to another location ?

would want to find out the direct offset, when summed up would result in zero

can you show an example of above... ? do it manually and I will try a formula for that

Regards,
DILIPandey

<click on below 'star' if this helps>

**jaslake** · 08-30-2012, 01:16 PM

Hi Pavan_Jack

Welcome to the Forum!

I've modified some Bank Reconciliation code I have (Adapted from http://www.sqldrill.com/excel/genera...um-amount.html) that appears to do as you require. Let me know of issues.