using vlookup function to create a coding sys

Microsoft Excel 2007
January 25, 2010 at 03:01:26
Specs: Windows XP
Hi My name is Tyrone

I would like to enter a date then use only the day and month to start the code in a new cell , i also want a function that will automatically generate a value to be part of that code...e.g if i enter 25/01/2010 then the code in the new cell should become 2501-01 ...if i enter 25/01/2010 later that day the code should become 2501-02 and so forth...PLEASE HELP!!!!

See More: using vlookup function to create a coding sys

January 25, 2010 at 05:21:05

Here is one way to do this.
In this example, dates are entered in column A, starting at cell A2
(There must be one row above the first date entry that does not contain a date, for this to work)

I have split the creation of the 'Date-Serial' number into two columns, to make it a bit easier to follow, but you could combine it into one formula in one column.

In cell B2 enter this formula:
This creates the base of your date-Serial number e.g., 2501-
The TEXT() functions ensure that the day and month always display as two characters, so that the base is always the same length.

In cell C2 enter this formula:

This looks to see if the date in the row above is the same as the one entered on this row. If it is different it is the first one for this day so -01
If it is the same date it takes the last two characters which are the serial number, converts them to a value, adds 1 and then converts it back to text, always as two characters.

If you were likely to go over 99 entries in a day, you would have to format the serial part as "000" and test the right three characters instead of the two ...(RIGHT(C1,3)...

Drag the formulas in B2 and C2 down as required.

If you want this in place before you enter dates, the above formula returns 0001-01 when column A is empty.
To avoid this, wrap the formulas in a test for an empty cell in column A and return "" instead:

Here is my example:

	A		B	C
1	Date		Base	Day-Serial
2	22-Jan-10	2201-	2201-01
3	22-Jan-10	2201-	2201-02
4	22-Jan-10	2201-	2201-03
5	22-Jan-10	2201-	2201-04
6	22-Jan-10	2201-	2201-05
7	23-Jan-10	2301-	2301-01
8	23-Jan-10	2301-	2301-02
9	23-Jan-10	2301-	2301-03
10	25-Jan-10	2501-	2501-01


Report •

January 25, 2010 at 06:57:09
Here's another possible solution.

With your first date in A1, place this in B1 and drag it down.

As with Humar's suggestion, you would wrap this in an IF statement to prevent 0001-01 when Ax is empty.

(This is one formula that should be on one line)

=TEXT(DAY(A1),"00")&TEXT(MONTH(A1),"00")&" - "&TEXT(COUNTIF(INDIRECT("A1:A"&ROW()),A1),"00")

The INDIRECT("A1:A"&ROW()) builds the range for the COUNTIF function by increasing the range by one row at a time.

Row 1 -> =COUNTIF(A1:A1,A1)
Row 2 -> =COUNTIF(A1:A2,A2)
Row 3 -> =COUNTIF(A1:A3,A3)

Report •

January 25, 2010 at 07:13:56

Here is another modification
In cell B2 enter:

This avoids the need for the INDIRECT function.

Drag down as required and wrap in IF as before.


Report •

Related Solutions

January 26, 2010 at 00:36:46
you guys rock

this completely sorted out my problem...THANK YOU SOOOO MUCH...and i was told by a "genius" this cannot be done...woo hoo

Report •

January 26, 2010 at 22:14:33
Hi Humar

your formula worked for what i asked..but the problem that arises is that when a certain project is logged for more than on day ,the "project number" or code changes as per the day , which it obviously would do, so now i need to create a means to secure that code for by using the customers order number...only when a new order number is entered will that particular code change...can you please help again

Report •

January 28, 2010 at 10:57:42

This one is a bit more difficult !

I have had a couple of goes at it, but not right yet.


Report •

January 28, 2010 at 11:16:41

Have a look at this. It isn't quite right as it skips numbers if there are two project codes the same on the same day.

It requires an extra column, but the column can be moved out of the way later - it is a combined date and order number (using Excel's date numbers)

	A		B	C		D
1	Date		O/N	Date-ON		Day-Serial
2	22-Jan-10	47	4020047		2201-01
3	22-Jan-10	47	4020047		2201-01
4	22-Jan-10	50	4020050		2201-03
5	22-Jan-10	51	4020051		2201-04
6	22-Jan-10	47	4020047		2201-01
7	23-Jan-10	47	4020147		2301-01
8	23-Jan-10	48	4020148		2301-02
9	23-Jan-10	49	4020149		2301-03
10	25-Jan-10	53	4020353		2501-01

The formula in C2 is:
and in D2 is:

This is all one line - I just split it up for ease of viewing.


Report •

Ask Question