Auto generate number???

**iluminatae** · 02-10-2007, 02:06 AM

I have a few questions regarding a workbook I am working on.

Cell A1 Date
Cell B1 Clause number (generated via vlookup)
Cell C1 Activity number
Cell D1 Description
Cell E1 Voucher Type
Cell F1 Voucher Number
Cell G1 Expense

Is it possible to have Cell F1 generate a number based on Activity number beginning with the number 1 and when another entry occurs with the same activity number occurs on a different date it places the same number generated in a cell beside it? when another activity occurs it needs to add 1 to the previous voucher number.

I can have several expenses for an activity occur throughout a month but only need to create one voucher per activity at months end. This is done via a pivot table which I have already created.

This has stumped me.

**raypayette** · 02-10-2007, 03:45 AM

Excel can undoubtedly do it, but You have to figure out your own formula.

**Carim** · 02-10-2007, 06:26 AM

Hi,

There are many possibilities ...
Jim is explaining two of them ...
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

**iluminatae** · 02-10-2007, 07:41 AM

Hi Carim,

I had a look at that site, thanks for the link. However I may have not explained what I want to do correctly.

I have attached a sample sheet that I made up.

**VBA Noob** · 02-10-2007, 09:17 AM

Hi,

Try

=IF(MAX(C$1:$C1)<MATCH(B2,$B$2:B2,0),MAX($C$1:C1)+1,MATCH(B2,$B$2:B2,0))

VBA Noob

**iluminatae** · 02-10-2007, 05:38 PM

Hi VBA Noob,

Thanks for the formula. Now I need to go try to understand how it works....

I will let you know if I can make it work in my workbook.

Thanks again.

**VBA Noob** · 02-10-2007, 05:53 PM

No problem.

This might help

http://www.contextures.com/xlFunctions03.html#Match

The below finds the first match of a number. When you drag down B2 changes to B3 etc but returns the row of the match

MATCH(B2,$B$2:B2,0)

The if statement checks to see if the row number is greater than the Max in cells above

MAX(C$1:$C1)<MATCH(B2,$B$2:B2,0)

If true is returns the max from the cells above the current cell and adds 1

MAX($C$1:C1)+ 1

if false it returns the row number

MATCH(B2,$B$2:B2,0)

HTH

VBA Noob

**iluminatae** · 02-10-2007, 09:38 PM

I tried this one out and unfortunately it doesn't work when I try this with longer lists. Thanks for the effort nonetheless.

**VBA Noob** · 02-11-2007, 06:02 AM

Can you post the list

VBA Noob

**iluminatae** · 02-11-2007, 07:43 AM

Sure, it is attached.

I think I have found a solution to this. I am still going through some tests to make sure it will work in all situations.

Auto generate number???

LinkBack

Thread Tools

Rate This Thread

Display

Auto generate number???

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions