Paul,
Thank you so much for taking the time to find a solution for me. Unfortunately
the data cannot be sorted to make a formula in col H work. It's dependent on
another table which is then dependent on another. I really appreciate all the
time you took to try to find something that works for me. I think I'm giving
up
at this point and will just manually enter the numbers.
Thanks again.
Nadine
________________________________
From: Paul Schreiner <schreiner_p...@att.net>
To: excel-macros@googlegroups.com
Sent: Thu, October 7, 2010 6:42:43 AM
Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
That depends on if by: "the results I want" you mean the exact numbers you
gave as examples, then no, it may not.
However, if they're sorted by columns A/B/C & F,
then this MAY work for you:
I created a column "H" and inserted:
=A2&B2&C2&F2
then, in column "I":
=IF(NOT(ISNA(VLOOKUP(H2,H$1:I1,2,FALSE))),VLOOKUP(H2,H$1:I1,2,FALSE),
IF(NOT(ISNA(VLOOKUP(G2+1,I$1:I1,1,FALSE))),MAX(I$1:I1)+1,G2+1))
and copied H and I down through the list.
give it a shot..
Paul
>
>From: None <n8dine4ma...@yahoo.com>
>To: excel-macros@googlegroups.com
>Sent: Wed, October 6, 2010 5:27:43 PM
>Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
>
>
>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
>--
----------------------------------------------------------------------------------
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
