• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find an amount from another sheet?

A

AGM

New Member
Hello,
My subject line is simple but the real difficulty is trying to find the amount from the same policy, but the other sheet has a combination of different amounts. It may amount to the original amount being searched or it may not. Both sheets has the policy number column A, different amounts on column B, and date on column C. I am creating the results on column D(sheet1) to match the amount from sheet 2, which has a variety of amounts that can equal to the amount on sheet 1, but it may not. If the correct amount is found, it corresponds with the date, but the date is not important, matching the right amount with the policy number is important. Please help.
 
AGM said:
Hello,
My subject line is simple but the real difficulty is trying to find the amount from the same policy, but the other sheet has a combination of different amounts. It may amount to the original amount being searched or it may not. Both sheets has the policy number column A, different amounts on column B, and date on column C. I am creating the results on column D(sheet1) to match the amount from sheet 2, which has a variety of amounts that can equal to the amount on sheet 1, but it may not. If the correct amount is found, it corresponds with the date, but the date is not important, matching the right amount with the policy number is important. Please help.
Click to expand...
Hi,

It may well be me but I can't follow this. Can you attach a redacted copy of your workbook to allow us to visualise your data layout more easily?
 
AGM said:
Please see attached.
Click to expand...
Hi,

Thanks for posting your file. It may be an age thing on my part, elderly moments are becoming more frequent<g> but I can't see what amount you want to match.

If we take policy 22355232 which has an amount of -49.00 and a date of 30/6/2014 then what do you want to return from sheet 2? Please explain the logic for returning this value.
 
Policy 22355232 has the amount -49.00, find that amount that will equal in sheet 2 from the same policy. It is simple if you can locate it without a formula, but I have many of these policies. I have tried Vlookups and pivot tables for the dates to narrow them down but unsuccessful. I dont want to manually find them. What would be the best formula?
 
AGM said:
Policy 22355232 has the amount -49.00, find that amount that will equal in sheet 2 from the same policy. It is simple if you can locate it without a formula, but I have many of these policies. I have tried Vlookups and pivot tables for the dates to narrow them down but unsuccessful. I dont want to manually find them. What would be the best formula?
Click to expand...

by the way, sheet 2.... (106.00) + 57.00 = -49.00
 
AGM said:
Policy 22355232 has the amount -49.00, find that amount that will equal in sheet 2 from the same policy. It is simple if you can locate it without a formula, but I have many of these policies. I have tried Vlookups and pivot tables for the dates to narrow them down but unsuccessful. I dont want to manually find them. What would be the best formula?
Click to expand...
Hi,

Taking again the -49.00 the only matching values for the same policy would seem to be B2+B4 which equal -49.00. Likewise for 22355261 (1799.00) which would be B57+B60. None of this gets me any closer to understanding what to return so regrettably I think I'm going to pass on this. Someone, I'm sure will help.
 
You are correct, the only possible return is B2 + B4 which is the exact amount. Now what would be the formula to get those amounts? Returning the amount in Sheet 1, D2? I am only looking for exact amounts that will equal -49.00 in sheet 2. Policy 22355261 has the amount for 1,799.00, the amount is in sheet 2, but in a combination of amounts. What would be the best formula to get the results from sheet 2?
 
Hi AGM,

I had gone through the file you posted and found one confusion. If I create Pivot Table from the data on Sheet2, below are my observation.

If you take example of first policy number on Sheet1, I get something like this.
Capture.JPG

Here an exact match is possible.

But if you consider the second example it shows like this:

Capture1.JPG

Here the sum is matching. So my doubt is if an exact match found it's OK otherwise go for summing, but summing all the numbers may or may not be equal so there has to be a logic to do the sum let us say add all the numbers before some date or like that.

If there is no such logic and we do manually adding say add 1 & 3 than add 2 & 4 than it will not be possible as creating a combinations of adding through formula will be difficult or not possible (as per my knowledge).

May be you should find some logic.

Just a thought.

Regards,
 
Hi ,

If the desired value can be obtained from adding only 2 other values , then you can try this technique.

Narayan
 

Attachments

  • Book2 (1).xlsx
    49.6 KB · Views: 6
Somendra Misra said:
Hi AGM,

I had gone through the file you posted and found one confusion. If I create Pivot Table from the data on Sheet2, below are my observation.

If you take example of first policy number on Sheet1, I get something like this.
View attachment 9972

Here an exact match is possible.

But if you consider the second example it shows like this:

View attachment 9973

Here the sum is matching. So my doubt is if an exact match found it's OK otherwise go for summing, but summing all the numbers may or may not be equal so there has to be a logic to do the sum let us say add all the numbers before some date or like that.

If there is no such logic and we do manually adding say add 1 & 3 than add 2 & 4 than it will not be possible as creating a combinations of adding through formula will be difficult or not possible (as per my knowledge).

May be you should find some logic.

Just a thought.

Regards,
Click to expand...


Thanks for your reply by the way. I have done a pivot as similar to this, but it still leaves me with more manual addition. This is just a small amount of example that I work with, I have about several thousands of these and I am looking for a faster way to get these results faster.
 
NARAYANK991 said:
Hi ,

If the desired value can be obtained from adding only 2 other values , then you can try this technique.

Narayan
Click to expand...


Hi Narayan,

I see the example but the formula you have has an error. It does not show me the results, just the formula. Can you please check again?
 
Hi ,

I am sorry , but I don't understand what you are looking for ; do you want that given -49 , the formula should display -49 ? That will be an exact match !

Or should the formula display the components which make up -49 ? If this is the case , use the INDIRECT function around each of the components of the formula to get these values.

If the formula is returning an output such as $B$2 + $B$4 , we can change it to :

=INDIRECT($B$2) & "," & INDIRECT($B$4)

which will return the individual components that make up -49.

Narayan
 
This is s simple search, but an almost impossible formula. Let me explain what I need again. I have sheet 1 under Column A Policies that has the amount I need to match, column B. In sheet 2, there are various policies with the same policy numbers under Column A, but has different amounts that can or may not equal the same amount on the policies in sheet 2. Now, I need a formula on sheet 1 under column D to match any of the policies with the same amount. The first policy has the amount -49 and you can easily find that in sheet two -106 + 57 = -49. I do not want to do this manually because I have thousands of these policies to do. I need a formula to get these matching amounts in sheet 1. This is s simple logic...I need some expertise in Excel formulation. Is this impossible or too tough for the Excel experts??
 
Hi ,

The problem is not impossible to solve ; the problem description is not clear.

What exactly would be achieved by putting two like amounts together , I fail to understand ; in one cell you will manually enter -49 ; you want a formula to do a search , and if the amount entered is available in another sheet as a combination of 2 amounts , you want -49 to be put in another cell through a formula ; is this correct ?

What purpose will be served by this ?

Your initial problem requirement was that the formula should tell you which cells when added together would give you the manually entered amount ; this was much more reasonable.

Your latest requirement sounds , to say the least , strange.

Narayan
 
The purpose is to find the amount that will equal in sheet 1. Does it matter to anyone, it only matters to how the formula will work? I just want to return a value that will equal the amount or not? Why does it matter to you what it serves? If you can't figure out a formula I need someone that can, thank you.
 
Hi Narayan,

Can you include the policy number as I indicated from the original sheet? If the formula only matches the amount, it can match from any other policies, it has to match the amounts only from the same policy. Thanks.
 
Hi Narayan,

I thank you, this might work and actually the previous one is what I prefer. This returns a True or False, I need the actual amount that matches the asking value. I want to know that sheet 2 has all those formulas and this case another sheet 3. Is there a way of eliminating them or making them much simpler because I have thousands of these policies to match and I'm afraid that it can fill the Excel capacity and might not work at all?
 
You must log in or register to reply here.
Back
Top