Excel Functions
-
Here it is, if this is too much of programming, just say and I will delete it. I have a table, to columns, one is client id and the other payments. There can be multiple of the same client id, so lets say on a different sheet I want to just enter the client id, it then must lookup the Id and sum all the payments, now I'm looking for the function to do that.
He who laughs last is a bit on the slow side
This means that you have to find out what client IDs exists in the first sheet by searching it since you cannot know this otherwise, right? I'm not aware of any built-in function or macro that can accomplish what you ask for. I would write a VBA macro that searches the first sheet for client IDs and place them into a map that maps the ID with a sum. When the search is completed I'd write the results to the other sheet. A word of caution: we're closing up on programming questions in the Lounge. ;)
"It's supposed to be hard, otherwise anybody could do it!" - selfquote
"High speed never compensates for wrong direction!" - unknown -
HLOOKUP and VLOOKUP functions exist, just use the in-built help file for these functions
If I'm not mistaking, HLOOKUP and VLOOKUP cannot be used in this situation since... - they require something to search for which we don't have - they require a range which we dont know about - they would only return one value and we have to iterate over multiple rows
"It's supposed to be hard, otherwise anybody could do it!" - selfquote
"High speed never compensates for wrong direction!" - unknown -
If I'm not mistaking, HLOOKUP and VLOOKUP cannot be used in this situation since... - they require something to search for which we don't have - they require a range which we dont know about - they would only return one value and we have to iterate over multiple rows
"It's supposed to be hard, otherwise anybody could do it!" - selfquote
"High speed never compensates for wrong direction!" - unknown -
Richard A. Abbott wrote:
Think I misread his question
Quite allright. :) Happens to myself from time to time as well.
"It's supposed to be hard, otherwise anybody could do it!" - selfquote
"High speed never compensates for wrong direction!" - unknown -
Here it is, if this is too much of programming, just say and I will delete it. I have a table, to columns, one is client id and the other payments. There can be multiple of the same client id, so lets say on a different sheet I want to just enter the client id, it then must lookup the Id and sum all the payments, now I'm looking for the function to do that.
He who laughs last is a bit on the slow side
Once you have the IDs you could use COUNTIF. If the client IDs are sequential then you could have a table of 1 to the current highest client ID and then add a few more rows for good luck and use either a formula or conditional formatting to hide the cell's value if the COUNT of a particular ClientID is 0 Otherwise it's VBA Macro time (have fun)...
-
This means that you have to find out what client IDs exists in the first sheet by searching it since you cannot know this otherwise, right? I'm not aware of any built-in function or macro that can accomplish what you ask for. I would write a VBA macro that searches the first sheet for client IDs and place them into a map that maps the ID with a sum. When the search is completed I'd write the results to the other sheet. A word of caution: we're closing up on programming questions in the Lounge. ;)
"It's supposed to be hard, otherwise anybody could do it!" - selfquote
"High speed never compensates for wrong direction!" - unknown -
Once you have the IDs you could use COUNTIF. If the client IDs are sequential then you could have a table of 1 to the current highest client ID and then add a few more rows for good luck and use either a formula or conditional formatting to hide the cell's value if the COUNT of a particular ClientID is 0 Otherwise it's VBA Macro time (have fun)...
-
HLOOKUP and VLOOKUP functions exist, just use the in-built help file for these functions
-
Here it is, if this is too much of programming, just say and I will delete it. I have a table, to columns, one is client id and the other payments. There can be multiple of the same client id, so lets say on a different sheet I want to just enter the client id, it then must lookup the Id and sum all the payments, now I'm looking for the function to do that.
He who laughs last is a bit on the slow side
-
-
Thanks, exactly what I wanted to do. Works 100
He who laughs last is a bit on the slow side