# need logic in vba



## arian29 (May 12, 2011)

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 (May 12, 2011)

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

Give me the logic more clear, will help.


----------



## arian29 (May 12, 2011)

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 (May 12, 2011)

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

Only for '2' and '3'.


----------



## arian29 (May 12, 2011)

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 (May 12, 2011)

arian29 said:


> eg,
> 
> A   B
> 2      x
> ...



I think the e.g is like this

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


----------



## arian29 (May 12, 2011)

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


----------



## asingh (May 12, 2011)

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 (May 12, 2011)

no its not working..


----------



## Zangetsu (May 12, 2011)

arian29 said:


> 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 (May 12, 2011)

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 (May 12, 2011)

arian29 said:


> no its not working..



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



Zangetsu said:


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



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.


----------

