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 encounterd,
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
