# need logic in vba

#### arian29

##### In the zone
i need the logic in vba(using any loops) for this >

if col A has repeted vales then col B should have the first instance value for such repetations as x and rest as y. basically i need to populate col B in this way, col A is only present.

eg,

A B
2 x
2 y
1 y
3 x
3 y
3 y

#### asingh

##### Aspiring Novelist
^^
The first instance of 2 = x, for 1 = y, how can 3 = y again.

Give me the logic more clear, will help.

OP
A

#### arian29

##### In the zone
No its not that 2 = anything. Nothing is equal to anything. There are 2 columns A and B. Column A is already populated with the given numbers. some numbers are repeted like number 2 and 3. 2 is always repeted twice and 3 is repeted thrice. now i want x in column B , against the first instance of 2 or 3 and y against the rest of the instances. So i am filling column B.
its in vba (excel)
Thanks

#### asingh

##### Aspiring Novelist
^^
But this logic will not apply to the first instance of '1'..?

Only for '2' and '3'.

OP
A

#### arian29

##### In the zone
ya.. actually the data in column A is like

1
2
2
1
1
1
1
3
3
3
1
1
2
2
1
2
2
1
1
3
3
3
1
1
1
3
3
3
1
2
2

like this.. so i need x against the 'first' instance of 2 and 3. getting y against 1 is easy as there is only one instance but having problem with 2 and 3. rest of the instances will have a y.

#### Zangetsu

##### I am the master of my Fate.
eg,

A B
2 x
2 y
1 y
3 x
3 y
3 y

I think the e.g is like this

A B
2 x
2 y
1 x
3 x
3 y
3 y

OP
A

#### arian29

##### In the zone
you can have x or y against 1, dosent matter, prob is with 2 and 3.

#### asingh

##### Aspiring Novelist
Would a formulaic approach work here......

I pasted the data from post number 5 on a range of D10 : D40.

Then I wrote this formula in E10:
=IF(COUNTIF(\$D\$10 : D10,D10)=1,"x","y")

Once you type the last bracket, press CONTROL+SHIFT+ENTER, and the formula will get {} around it.

Now double click down..>!

OP
A

#### arian29

##### In the zone
no its not working..

#### Zangetsu

##### I am the master of my Fate.
you can have x or y against 1, dosent matter, prob is with 2 and 3.

sorry i m n00b in vba.But
the logic will be like this

consider range of column A is A1:A20 => output will be B1:B20

start from A1 -> store it in a temp variable
Read A2 cell value & compare it with the temp variable if = then put y else put x
this will go on till A20
temp variable will always store the previous cell value

#### abhidev

##### Human Spambot
I don't think this logic will work...coz what happens if any of those numbers repeat in the sequence....e.g 1 2 2 2 3 3 3 1 1 1 2 2 2....correct me if I am wrong...the output for this sequence should be x x y y x y y y y y y y y ...right?

#### asingh

##### Aspiring Novelist
no its not working..

Yes, because you did not follow the way I asked you to.

sorry i m n00b in vba.But
the logic will be like this

consider range of column A is A1:A20 => output will be B1:B20

start from A1 -> store it in a temp variable
Read A2 cell value & compare it with the temp variable if = then put y else put x
this will go on till A20
temp variable will always store the previous cell value

There is absolutely no USE for VBA for this. VBA is used only when native excel cannot do the job.

Please see the sheet I have attached. Look at the yellow cells, and understand the formula I wrote. It is an array function, and does the job perfect.