Problem is with the data on the Data tab.
Almost all accounts have more than 1 entry which is causing mismatch in the
results.
On Thu, Nov 18, 2010 at 6:23 PM, Paul Schreiner <schreiner_p...@att.net>wrote:
> I placed a "=countif" function in the column adjacent to your "test"
> =IF(H11<>F11,COUNTIF(Data!A:A,A11),"")
>
> Now, I have no idea why you think that the account "1960D50000" for "Marl"
> should be 0 when in the Data sheet it has:
> 1960D50000 EUR Marl -1155.19 -1380.34
>
> But for the rest of them,
> most have more than one record in the Data sheet.
> VLookup only returns the first of the records.
> If you want it to return a SUM of the records with the matching
> Account number, we can change the VLookup function to SumIF.
>
> In some cases, there is NO matching records, yet YOU think there should be.
> There ARE matching numbers in your "Sheet3", so
> clearly something is missing.
>
> Also, be careful with the cell formats.
> In some cases, because the account number contains only numbers,
> Excel wants to convert it to a number.
> So, in the Report you put a (') in front like: '2470670000
>
> If in the Data sheet, the Account column is a Text format, this value
> may not have the leading ('), so it would not find a match.
>
> If you have any control over the values, you COULD create
> a new column and prefix the values with "Z" (or some alpha character)
>
> Paul
>
>
> *From:* Jagadeesh B S <jagg...@gmail.com>
> *To:* excel-macros@googlegroups.com
> *Sent:* Thu, November 18, 2010 1:18:24 AM
> *Subject:* Re: $$Excel-Macros$$ Help required for formule
>
>
> Hello friends my requirement is met in some way but not the complete
> solution.
>
> Thanks a lot to Paul for the help.
>
> This formula helped me a bit but not able to do well as per my requirement.
> Could you please look it to the attachment and help me again...?
>
> Thanks for your valuable assistance.
>
> Thanks
> Jagadeesh B S
>
>
>
> On Wed, Nov 17, 2010 at 6:56 PM, Paul Schreiner <schreiner_p...@att.net>wrote:
>
>> Your formula for row 3 would look like:
>>
>> =IF(C3="USD",VLOOKUP(A3,Data!A:E,5,FALSE),VLOOKUP(A3,Data!A:E,4,FALSE))
>> Paul
>>
>>
>> *From:* Jagadeesh B S <jagg...@gmail.com>
>> *To:* excel-macros@googlegroups.com
>> *Sent:* Wed, November 17, 2010 7:45:42 AM
>> *Subject:* $$Excel-Macros$$ Help required for formule
>>
>> Hello Friends
>>
>> I need a formula or macro( preferabley formule) need to prepare this
>> report .
>>
>> I need a formula which should get the details for the account # mentioned
>> in the column A with the reference to the currency,form the sheet DATA, if
>> the currency is USD It should pull the value in the column E and if the
>> currency is other then USD it should pull the value in the column D.
>>
>>
>>
>> Please help with this formula…
>>
>>
>>
>> Thanks you very much.:)
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>>
>> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>>
>> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
