With large data, you may take a bit of time with any method. Perhaps you could
break it up.
If desired, send your real file to ME.
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com
From: Amit Gandhi
Sent: Tuesday, July 03, 2012 3:17 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ VLOOKUP OR SUMPRODUCT FORMULA HELP
Hi Rajan
Get Total function (UDF) is perfectly fine, but it is taking too much time to
process large data.
Is there any other way to get the same results using Excel in-built functions
like VLOOKUP/ SUMPRODUCT/ INDIRECT. I am attaching the output file again.
Regards
Amit
On Mon, Jun 25, 2012 at 11:37 AM, Amit Gandhi <silkyro...@gmail.com> wrote:
Thanks alot Mr. Rajan
This function is Perfectly as per my requirement.
Thanks once again
Amit Gandhi
On Sat, Jun 23, 2012 at 6:45 PM, Rajan_Verma <rajanverma1...@gmail.com> wrote:
HI Amit,
This function can work independently, you just need to understand the
function Arguments :
Function GetTotal(rngLookUpRange, rngMapping As Range, rngLookup As Range,
lngCOlumnresult As Long, lngMinusValue As Double) As Double
rngLookUpRange = it is a value you want to look in table1
rngMapping = Select whole Table1
rngLookup =select the Table2
lngCOlumnresult = Which column you want to Sum from Table2
lngMinusValue = If you want to minus any value from the result formula at
every iteration
so if you are increasing column in table2 you also need to increase
lngCOlumnresult value in the formula
use the below function in J15:
=GetTotal(G15,$A$8:$B$18,$F$4:$I$9,4,0)
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Amit Gandhi
Sent: 23 June 2012 5:19
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ VLOOKUP OR SUMPRODUCT FORMULA HELP
Thanks Mr. Rajan
For given set of values, its working perfectly. But I have some questions.
1. If values in H5:H9 are changed, then it doesn't give correct result.
Pls suggest function, which can work independently and doesn't depend on
adjacent column. I am attaching modified task file for your quick reference.
It will be a great help if you can suggest formula with inbuilt functions
(INDIRECT, MATCH, SUMPRODUCT) as well.
Regards
Amit
On Sat, Jun 23, 2012 at 1:38 PM, Rajan_Verma <rajanverma1...@gmail.com>
wrote:
Hi Amit,
It will fulfil your requirement . J , See the attached.
Function GetTotal(rngLookUpRange, rngMapping As Range, rngLookup As Range,
lngCOlumnresult As Long, lngMinusValue As Double) As Double
Dim VarMapping
Dim VarLookup
Dim strLookUp
Dim lngResult As Double
Dim lngCounter As Long
Dim StrLookupValue As String
VarMapping = rngMapping
VarLookup = rngLookup
StrLookupValue = rngLookUpRange.Value
strLookUp = ""
For lngCounter = LBound(VarMapping) To UBound(VarMapping)
If VarMapping(lngCounter, 1) = StrLookupValue Then
strLookUp = strLookUp & VarMapping(lngCounter, 2) & "|"
End If
Next lngCounter
For lngCounter = LBound(VarLookup) To UBound(VarLookup)
If InStr(strLookUp, VarLookup(lngCounter, 1)) Then
lngResult = lngResult + VarLookup(lngCounter, lngCOlumnresult)
- lngMinusValue
End If
Next lngCounter
GetTotal = lngResult
End Function
Revert if it is not desired
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Amit Gandhi
Sent: 22 June 2012 5:20
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ VLOOKUP OR SUMPRODUCT FORMULA HELP
Hi Experts
I have two Input/Lookup tables, and 1 result table. I want a formula which
can give me desired result instantly. Sample file is attached for your ready
reference.
How it will be possible? Pls help me here.
Regards
Amit
--
-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise,
accurate thread titles. Poor thread titles, like Please Help, Urgent, Need
Help, Formula Problem, Code Problem, and Need Advice will not get quick
attention or may not be answered. 2) Don't post a question in the thread of
another member. 3) Don't post questions regarding breaking or bypassing any
security measure. 4) Acknowledge the responses you receive, good or bad. 5)
Cross-promotion of, or links to, forums competitive to this forum in signatures
are prohibited. NOTE : Don't ever post personal or confidential data in a
workbook. Forum owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com To
unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise,
accurate thread titles. Poor thread titles, like Please Help, Urgent, Need
Help, Formula Problem, Code Problem, and Need Advice will not get quick
attention or may not be answered. 2) Don't post a question in the thread of
another member. 3) Don't post questions regarding breaking or bypassing any
security measure. 4) Acknowledge the responses you receive, good or bad. 5)
Cross-promotion of, or links to, forums competitive to this forum in signatures
are prohibited. NOTE : Don't ever post personal or confidential data in a
workbook. Forum owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com To
unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
'Expecting the world to treat u fairly coz u r a good person is like
expecting the lion not to attack u coz u r a vegetarian.
Think about it.'
Take care
Amit
--
-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise,
accurate thread titles. Poor thread titles, like Please Help, Urgent, Need
Help, Formula Problem, Code Problem, and Need Advice will not get quick
attention or may not be answered. 2) Don't post a question in the thread of
another member. 3) Don't post questions regarding breaking or bypassing any
security measure. 4) Acknowledge the responses you receive, good or bad. 5)
Cross-promotion of, or links to, forums competitive to this forum in signatures
are prohibited. NOTE : Don't ever post personal or confidential data in a
workbook. Forum owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com To
unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise,
accurate thread titles. Poor thread titles, like Please Help, Urgent, Need
Help, Formula Problem, Code Problem, and Need Advice will not get quick
attention or may not be answered. 2) Don't post a question in the thread of
another member. 3) Don't post questions regarding breaking or bypassing any
security measure. 4) Acknowledge the responses you receive, good or bad. 5)
Cross-promotion of, or links to, forums competitive to this forum in signatures
are prohibited. NOTE : Don't ever post personal or confidential data in a
workbook. Forum owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com To
unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
'Expecting the world to treat u fairly coz u r a good person is like
expecting the lion not to attack u coz u r a vegetarian.
Think about it.'
Take care
Amit
--
'Expecting the world to treat u fairly coz u r a good person is like expecting
the lion not to attack u coz u r a vegetarian.
Think about it.'
Take care
Amit
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
