Macro required for changing numbers to specific text

[amolryeolekar]

I have this date I have 3 Euiprments, I want to make a macro for replace numbers to eduipment name ,where cell contains value 1 till before next cell value =1 will be one equipment no i.e EQ1 then for next cell value=1 then EQ2 till next cell value is 1 and so on

All numbers (number of rows of any equipment will be vary time to time )

How to automate by macro


Ex-

Srno

1

2

3

1

2

3

4

5

6

1

2

3


---

srno

EQ1

EQ1

EQ1

EQ2

EQ2

EQ2

EQ2

EQ2

EQ2

EQ3

EQ3

EQ3

 

[NARAYANK991]

Hi Amol ,


You can do the same using formulae ; if your data is in column A , starting from A2 , and you want the equipment numbers to appear in column B , enter the following formula in B2 and copy downwards :


="EQ"&COUNTIF($A$2:A2,1)


I am assuming that the equipment number should change every time a 1 is encountered in column A.


Narayan

 

[SirJB7]

@NARAYANK991

Hi!

It does the job correctly, but I'd like more one of these two slight variations:

="EQ"&COUNTIF(A$2:A2,1)

="EQ"&COUNTIF($A$2:$A2,1)

In that order of choice.

Regards!