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
