need logic in vba

[arian29]

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]

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

Give me the logic more clear, will help.

 

[arian29]

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]

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

Only for '2' and '3'.

 

[arian29]

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]

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

 

[arian29]

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

 

[asingh]

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..>!

 

[arian29]

no its not working..

 

[Zangetsu]

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]

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]

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.

Sample.xls - 4shared.com - online file sharing and storage - download

And I repeat again, there is no need to do this with VBA. It is shooting a fly with a cannon. Unless your data is crossing 50,000 rows or something.