Need Solution on Creating Running/Sequence Number Based on Value

**icol33** · 05-19-2021, 09:10 PM

[SOLVED]
Thanks a ton to the contributors :
- Fluff13

- JohnTopley

Good Morning from GMT+7,

I want to create this kind of running/continuous number based on values (which in my case are name/ID, date, and location).
The current method that I've used to generate the code number is a simple COUNTIF formula, but when it finds the same value, the formula will skip to the next number.

ex :

No	Name	Date	Location	Generated Code (after VLOOKUP)	What I Want
1.	ANDES	03 May 2021	Denmark	ANDES-OUT #1	ANDES-OUT #1
2.	ANDES	03 May 2021	Denmark	ANDES-OUT #1	ANDES-OUT #1
3.	ANDES	03 May 2021	London	ANDES-OUT #3	ANDES-OUT #2

The formula that I currently use for the generated code: B2&"-OUT #"&countif($B$2:B2,B2) ; and to call the data from another sheet using VLOOKUP.
More details can be found in the attachment.

Thanks

**thenewkidd** · 05-19-2021, 09:15 PM

misread the request. sorry.

**JohnTopley** · 05-22-2021, 12:16 PM

In H3

=IF(COUNTIF($B$3:$B3,$B3)=1,VLOOKUP($B3,Database!$B$3:$C$5,2,FALSE)&"-OUT-#1",IF(AND($B3=$B2,$D3=$D2,$C2=$C3),H2,LEFT(H2,9)&(--RIGHT(H2,1))+1))

I don't have 365 so I am sure there is a smarter solution

**Fluff13** · 05-22-2021, 01:01 PM

How about in J3 on the Database sheet

Formula:

Please Login or Register  to view this content.

**icol33** · 05-23-2021, 12:45 PM

Originally Posted by JohnTopley

In H3

=IF(COUNTIF($B$3:$B3,$B3)=1,VLOOKUP($B3,Database!$B$3:$C$5,2,FALSE)&"-OUT-#1",IF(AND($B3=$B2,$D3=$D2,$C2=$C3),H2,LEFT(H2,9)&(--RIGHT(H2,1))+1))

I don't have 365 so I am sure there is a smarter solution

Hi John, it works like charm! It would be better if it’s located on the Database sheet, but yours too solved my question as well.
Thanks thanks a lot!

**icol33** · 05-23-2021, 12:53 PM

Originally Posted by Fluff13

How about in J3 on the Database sheet

Formula:

Please Login or Register  to view this content.

Hi Fluff, your solution works extremely well and it solved my problem as well, thanks a ton!
I think I’ll close this thread as the problem is already solved, thank you guys for the help!

**Fluff13** · 05-23-2021, 12:53 PM

Glad to help & thanks for the feedback.

Need Solution on Creating Running/Sequence Number Based on Value

LinkBack

Thread Tools

Rate This Thread

Display

Need Solution on Creating Running/Sequence Number Based on Value

Re: Need Solution on Creating Running/Sequence Number Based on Value

Re: Need Solution on Creating Running/Sequence Number Based on Value

Re: Need Solution on Creating Running/Sequence Number Based on Value

Re: Need Solution on Creating Running/Sequence Number Based on Value

Re: Need Solution on Creating Running/Sequence Number Based on Value

Re: Need Solution on Creating Running/Sequence Number Based on Value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Require a solution, list of sequential number but ignore a number if it is out of sequence

Creating List Name based levels and Discipline - Formula or VBA Solution?

Creating an ascending number sequence in groups of 6

convert underscore to dash within a number sequence without creating a date

[SOLVED] Creating Unique Number Sequence

Creating Number Sequence in Groups of 10

[SOLVED] Creating a sequence based on numbers in one column and adding "01,02..." to a new sequence

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions