If the max function gets me the results I want, I'm certainly open to it. :)
________________________________
From: Paul Schreiner <schreiner_p...@att.net>
To: excel-macros@googlegroups.com
Sent: Wed, October 6, 2010 4:17:15 AM
Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
I cannot see how there could be a formula option because it needs to iterate
(loop)
through the previous values to check for duplicates.
Now, if instead it were acceptable to use the max() function,
then it would be possible to get unique values, but depending on the sort state
of the data, the values would most likely not be what you were looking for.
Paul
>
>From: None <n8dine4ma...@yahoo.com>
>To: excel-macros@googlegroups.com
>Sent: Tue, October 5, 2010 5:42:29 PM
>Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
>
>
>Let me try to explain a little better. Col H is where I need a formula but
>for
>now have entered the result the formula should return.
>
>
>Starting at the top:
>Cell H2 needs a formula result of 1 greater than what's in cell G2 for a
>result
>of 12. That's the easy one.
>
>Cell H3 needs one greater than G3 but because this number is already used in
>cell H2, and the combination in col A-C and F do not match, it needs to
>increment by 1 for a formula result of 13.
>This continues for rows 4 and 5.
>Rows 6 needs one greater than row 5 for a formula result in cell H6 of 16.
>Row 7 is identical to row 6 for columns A-C and F so the formula result
>in cell
>H6 should be the same in cell H7.
>And so on.
>Row 10 should have a formula result in cell H10 of 26 because it is 1 more
>than
>cell G10 and because there is no other unique combination of A-C and F in the
>rows above.
>
>Is this making a little more sense?
>
>I haven't tried the Macro yet. If there's no formula option I will look into
>the Macro option stated below. Thank you, everyone.
>
>
>
>
________________________________
From: Paul Schreiner <schreiner_p...@att.net>
>To: excel-macros@googlegroups.com
>Sent: Tue, October 5, 2010 5:05:23 AM
>Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
>
>
>I agree.
>
>Your description SEEMS to imply that if you're identifying records as a
>combination of A-C and F.
>Then, the first time a combination is encountered, increment the value in G
>and
>place the result in H.
>
> That portion of the statement:
>"unless there is already a number for that combo in col H, then it gets
>increased by 1 from that number in col H"
>seems to say that the subsequent times a record identifier is encountered,
>increase the value
>last calculated FOR THAT RECORD by 1.
>
>But from your examples, every time a record set is duplicated, the SAME "Next
>Trans#" is used...
>Also,
>A-C for rows 2 and 3 are the same.
>but column F is different, so the COMBINATION of A-C,F is unique.
>Yet in your example, column H of row 2 is G (11) + 1 = 12.
>row 3 should be G(11) + 1 = 12 also, but instead is 13!
>
>that would imply that your logic says to create unique "Trans#" for each
>unique
>record
>But the trick is that you want it to START one above the previous "Trans#"...
>
>I'm not sure we can do that without a macro.
>
>How about:
>
>Sub New_Trans()
> Dim Dict_Record, Dict_Trans
> Dim RowCnt, R
> Dim RecID, TransNo, TempNo
>
> Set Dict_Record = CreateObject("Scripting.Dictionary")
> Set Dict_Trans = CreateObject("Scripting.Dictionary")
> Dict_Record.RemoveAll
> Dict_Trans.RemoveAll
>
> RowCnt =
>Application.WorksheetFunction.CountA(ActiveSheet.Range("A1:A65000"))
>
> For R = 2 To RowCnt
> RecID = ActiveSheet.Cells(R, "A").Value _
> & ActiveSheet.Cells(R, "B").Value _
> & ActiveSheet.Cells(R, "C").Value _
> & ActiveSheet.Cells(R, "F").Value
> If (Dict_Record.exists(RecID)) Then
> ActiveSheet.Cells(R, "H").Value = Dict_Record.Item(RecID)
> Else
> TempNo = ActiveSheet.Cells(R, "G").Value + 1
> While (Dict_Trans.exists(TempNo))
> TempNo = TempNo + 1
> Wend
> Dict_Trans.Add TempNo, TempNo
> Dict_Record.Add RecID, TempNo
> ActiveSheet.Cells(R, "H").Value = TempNo
> End If
> Next R
>End Sub
>
>
>Paul
>
>>
>>From: Dave Bonallack <davebonall...@hotmail.com>
>>To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
>>Sent: Tue, October 5, 2010 5:09:01 AM
>>Subject: RE: $$Excel-Macros$$ Re: Need next number with a twist
>>
>>Hi,
>>"For every unique value of col A - C and col F, then the number is increased
>>by
>>1 from col G unless there is already a number for that combo in col H, then
>>it
>>gets increased by 1 from that number in col H"
>>
>>I think I understand the first part of the sentence, but the meaning of the
>>second part escapes me.
>>"unless there is already a number for that combo in col H, then it gets
>>increased by 1 from that number in col H"
>> It appears that there is always a number in Col H, but is it "a number for
>>that
>>combo" ? I can't tell, since it is just a number, and not derived by a
>>formula.
>>Regards - Dave.
>>
>>
>>
>>________________________________
Date: Mon, 4 Oct 2010 13:47:08 -0700
>>From: n8dine4ma...@yahoo.com
>>Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
>>To: excel-macros@googlegroups.com
>>
>>
>>Does anyone have any thoughts on this? I've attached the spreadsheet again
>>if
>>anyone wants to take a look. Is there a way maybe to concatenate some cells
>>and
>>then write a formula?
>>
>>
>>
>>
________________________________
From: None <n8dine4ma...@yahoo.com>
>>To: excel-macros@googlegroups.com
>>Sent: Thu, September 30, 2010 2:52:14 PM
>>Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
>>
>>
>>Joshua,
>>
>>Thank you so much for your response. Unfortunately this isn't working. The
>>formula in col I is referencing col H which is where I originally wanted the
>>formula. There will be no data in col H unless I can't get a formula to work
>>in
>>there, then it will be manually entered. Can you help me with how the
>>formula
>>should look in cell H2 so that it will return the same numbers I manually
>>entered there to show what the formula result should be? Thank you.
>>
>>Nadine
>>
>>
>>
>>
________________________________
From: Joshua Leuthauser <leu...@gmail.com>
>>To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
>>Sent: Wed, September 29, 2010 5:32:44 PM
>>Subject: $$Excel-Macros$$ Re: Need next number with a twist
>>
>>Build a "key" in column g. The formula for the key should be:
>>=a2&b2&c2&f2
>>
>>Populate that down for all of the rows.
>>
>>Basically what I built says -- look at the column of keys, if you
>>don't find a match then take the max of all transactions used thus far
>>and increment by one. If you do find a match, use the same
>>transaction number that was used by that key (the combination of a-c2
>>& f2).
>>
>>You'll notice that it doesn't "reserve" the next transaction that
>>should be used, it just assigns the next transaction to whichever key
>>shows up that is unique.
>>
>>After you have your column built with the key, here is the formula I
>>put in I2:
>>=IF(ISNA(VLOOKUP(G2,G1:H1,2,FALSE)),MAX(H:H)
>>+1,VLOOKUP(G2,G1:H1,2,FALSE))
>>
>>Fill that down the remainder of column I and you have a working
>>formula that will either give the same transaction number if a key
>>match is found otherwise give you the next available transaction
>>number.
>>
>>
>>
>>On Sep 28, 10:16 pm, None <n8dine4ma...@yahoo.com> wrote:
>>> Hi there. I need some help finding the next number but there's a twist.
>>> Attached is a file that shows what I need.
>>>
>>> There's a sheet titled "Transactions" where the data is continually added
>>> to
>>>and
>>> the order cannot be changed so sorting the data is out of the question. The
>>> next sheet is called "Need Formula" and this is where I need a formula to be
>>> entered into cells H2 - H24 for this sample spreadsheet. The actual file
>will
>>> have more rows.
>>>
>>> The formula needs to look at the number in cell G2 and increment it by one
>>> UNLESS, and here's the trick, there is already a number in this column (H)
>for
>>> the combination of cells A2-C2 and F2. The sample will give you a better
>idea
>>> as I've entered the end result in column H already. I just need a formula
>>that
>>> will give me the same result. Thank you so much for your help.
>>>
>>> next numbers.xls
>>> 43KViewDownload
>>
>>--
>>----------------------------------------------------------------------------------
>>
>>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
>--
----------------------------------------------------------------------------------
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
