Put numbers in column B cells when Value Change in Column A cells
 

Hi all, I have data in column A (see below)

ROW A---------Column
1 Heading
2 BTC
3 BTC
4 BTC
5 GIS
6 GIS
7 VXZ
8 VXZ
9 BTC
10 BTC

I want macro on button which should produce result in column B
something like (see below)

ROW A B---------Columns
1 Heading Heading
2 BTC 1
3 BTC 1
4 BTC 1
5 GIS 2
6 GIS 2
7 VXZ 3
8 VXZ 3
9 BTC 4
10 BTC 4

In other words I want macro to check in column A cells and put numbers
in column B cells and when where value become different in column A
cells then it should also change the value in column B cells. Please
can any friend can help.

Put numbers in column B cells when Value Change in Column A cells
 

Hi,

You don't need a macro for this you can use this in B2 dragged down

=IF(A2=A1,MAX($B$1:B1,1),MAX($B$1:B1)+1)

but if that's what you want then right click your sheet tab, view code and
paste this in and run it.

Sub marine()
Dim num As Long
Dim Myrange As Range
num = 1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Myrange = Range("A3:A" & lastrow)
Range("B2").Value = num
For Each c In Myrange
If c.Value = c.Offset(-1).Value Then
c.Offset(, 1).Value = num
Else
num = num + 1
c.Offset(, 1).Value = num
End If
Next
End Sub


"K" wrote:

Hi all, I have data in column A (see below)

ROW A---------Column
1 Heading
2 BTC
3 BTC
4 BTC
5 GIS
6 GIS
7 VXZ
8 VXZ
9 BTC
10 BTC

I want macro on button which should produce result in column B
something like (see below)

ROW A B---------Columns
1 Heading Heading
2 BTC 1
3 BTC 1
4 BTC 1
5 GIS 2
6 GIS 2
7 VXZ 3
8 VXZ 3
9 BTC 4
10 BTC 4

In other words I want macro to check in column A cells and put numbers
in column B cells and when where value become different in column A
cells then it should also change the value in column B cells. Please
can any friend can help.

Put numbers in column B cells when Value Change in Column A cells
 

Hi

Another formula for B2 is
=IF(A2="","",SUM(B1,--(A2<A1)))
(Copy formula down - and you can have pre-prepared cells with formula there
too)

Arvi Laanemets


"K" wrote in message
...
Hi all, I have data in column A (see below)

ROW A---------Column
1 Heading
2 BTC
3 BTC
4 BTC
5 GIS
6 GIS
7 VXZ
8 VXZ
9 BTC
10 BTC

I want macro on button which should produce result in column B
something like (see below)

ROW A B---------Columns
1 Heading Heading
2 BTC 1
3 BTC 1
4 BTC 1
5 GIS 2
6 GIS 2
7 VXZ 3
8 VXZ 3
9 BTC 4
10 BTC 4

In other words I want macro to check in column A cells and put numbers
in column B cells and when where value become different in column A
cells then it should also change the value in column B cells. Please
can any friend can help.

Put numbers in column B cells when Value Change in Column A cells
 

On Nov 14, 12:53*pm, "Arvi Laanemets" wrote:
Hi

Another formula for B2 is
=IF(A2="","",SUM(B1,--(A2<A1)))
(Copy formula down - and you can have pre-prepared cells with formula there
too)

Arvi Laanemets

"K" wrote in message

...



Hi all, *I have data in column A (see below)

ROW * * * * * *A---------Column
1 * * * * * * *Heading
2 * * * * * * *BTC
3 * * * * * * *BTC
4 * * * * * * *BTC
5 * * * * * * *GIS
6 * * * * * * *GIS
7 * * * * * * *VXZ
8 * * * * * * *VXZ
9 * * * * * * *BTC
10 * * * * * *BTC

I want macro on button which should produce result in column B
something like (see below)

ROW * * * * * *A * * * * * * * * B---------Columns
1 * * * * * * *Heading * Heading
2 * * * * * * *BTC * * * * * * * * 1
3 * * * * * * *BTC * * * * * * * * 1
4 * * * * * * *BTC * * * * * * * * 1
5 * * * * * * *GIS * * * * * * * * *2
6 * * * * * * *GIS * * * * * * * * *2
7 * * * * * * *VXZ * * * * * * * * *3
8 * * * * * * *VXZ * * * * * * * * *3
9 * * * * * * *BTC * * * * * * * * 4
10 * * * * * *BTC * * * * * * * * 4

In other words I want macro to check in column A cells and put numbers
in column B cells and when where value become different in column A
cells then it should also change the value in column B cells. *Please
can any friend can help.- Hide quoted text -

- Show quoted text -

thanks guys