Dynamically Assign a Number in Data Set

Closed
iluvhc Posts 2 Registration date Tuesday August 27, 2019 Status Member Last seen August 27, 2019 - Aug 27, 2019 at 11:03 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Aug 29, 2019 at 12:10 PM
Hello,

I have a large data set and need to assign a number to each row if the status changes. Is there a short cut / dynamic way that I can assign a number or tag anytime

System Configuration: Windows / Chrome 76.0.3809.100

2 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 27, 2019 at 11:51 AM
Hi Iluvhc,

Could you be more specific, preferably with an example.

Like:
Row 1 is used for header, column A for data and column B for assigned numbers.
Row1 Header
Row2 data 1
Row3 data 2
Row4 data 3
Where 1, 2 and 3 are the assigned numbers.

And when you change the data on row 3 it will look like:
Row1 Header
Row2 data 1
Row3 changed data 4
Row4 data 3
Where 1, 4 and 3 are the assigned numbers.

Something like that?


Best regards,
Trowa
1
iluvhc Posts 2 Registration date Tuesday August 27, 2019 Status Member Last seen August 27, 2019
Aug 27, 2019 at 02:26 PM
Hello - Please see example below. In Column A is the status and in column B i would like to assign a # to that row whenever when the status changes to Active. I would like column B to count how many times that status appears but not the total / sum (so 1, 2, 3, 4 - i need each of the results to be unique). My end goal is to then create another tab where I am only pulling in those with an Active status.
Status Number Name
Active 1 Jane
Retired 0 Jon
Retired 0 Tim
Retired 0 Yve
Active 2 Ellen
Retired 0 Wendy
Active 3 Ryan

Thanks for your help!
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 29, 2019 at 12:10 PM
Ok Iluvhc, that is more clear.

For that use the following formula:
=IF(A2="Active",COUNTIF($A$2:A2,"Active"),0)

Drag the formula down as far as you have data.

Best regards,
Trowa
0