Help with Macros in Excel

[Narce]

I need some help to make my little project work. !! Any help is very
appreciated

What I want the macros to do ?

Compare cells A2:A28 with cells B2:B28

It should first check cell A2 to see if a matching value in found inthe
range of cells B2:B28
Should make a Z in C2 if a matching value in found in the range of cells
B2:B28. This is the case, found it in B2, so it makes a Z in C2 (see the
example)
Should make a Z in C3 if a matching value in found in the range of cells
B2:B28. This is the case, found it in B13, so it makes a Z in C3 (see the
example)
Should make an N in C4 if a non-matching value in found in the range of
cells B2:B28. This is the case, it did not find it, so it makes a N in C4
(see the example)

This checking should be made for every value from cell A2 to A28


A B C
0517684287 0517684287 Z
0517685100 517685109 Z
517543288 517543288 N
517543433 517543332
517543403 517543403
517543424 517543424
517543332 517555288
517543456 517555403
517544100 517552274
517544288 5175522
517544334 517552403
517544403 517545100
517544409 517545288
517544423 517553100
517544450 517553332
517560334 517553403
517560691 517553456
517551100 0517685100
517551274 517554274
517551334 517554288
517551403 517554403
517551523 517578403
517551682 517578682
517551691 517686100
517550274 517686274
517550332 517686334
517550403 517686403
517550691 517686691


Thanks in advance,
Narce

 

[JE McGimpsey]

One way:

Public Sub ZorN()
Dim nLast As Long
nLast = Range("A" & Rows.Count).End(xlUp).Row
Const sFORMULA As String = _
"=IF(COUNTIF(R2C2:R%%C2,R2C1:R%%C1),""Z"",""N"")"
With Range("C2:C" & nLast)
.FormulaArray = Application.Substitute(sFORMULA, "%%", nLast)
.Value = .Value
End With
End Sub

 

[Narce]

Thanks a lot.

This is the best solution.

Have a nice day

 

[Narce]

Hi JE McGimpsey,

It was a great solution the one you posted.

If it is not too much to ask........could you give me a hand with the
following...

The result I would like now is as follows.

Data entered in column A will be changing.
Data in column B will be static.


It should first check cell B2 to see if a matching value in found in the
range of cells A2:A28 Should make a Z in C2 if a matching value is found in
the range of cells A2:A28. and add the current date to the respective row in
column C.
This is the case, found it in A2, so it makes a Z in C2 and add the current
date in cell D2 (see the example).

Should make a Z in C3 if a matching value in found in the range of cells
A2:A28.
This is the case, found it in B13, so it makes a Z in C3 (see the example)

Should make an N in C4 if a non-matching value in found in the range of
cells B2:B28.
This is the case, it did not find it, so it makes a N in C4 (see the
example)

There will be some cases where the value of some of the cells in column A is
the same. In those cases the result should be as follows:

When a matching value is found and a value in column C is already Z, then it
should look for...
Column E and enter a Z
Culumn F enter the current date.


When coulmns C and E have a Z value....
it should enter the data in column G and the current date in coulmn H (see
example in row 3). Note that colums E and G was entered in the same date,
that's why columns F and H have the same date.

If not maching value is found in the range of cells A2:A28 then...
Enter a N in column E (No current date is needed in column F) (see example
in row 4).

This checking should be made for every value from cell A2 to A28 (A28 may
change to any row number)



A B C D E F G H
0517684287 0517684287 Z 22-nOV-2004
0517685100 517685109 Z 21-nOV-2004 Z 23-nov-2004 Z
23-nov-2004
5175432884 517543288 N
517543433 517543332 Z 23-nov-2004 N
517543403 517543403
517543332 517543424
517543332 517555288
517543332 517555403
517543337 517552274
517544288 5175522
517544334 517552403
517685109 517545100
517544559 517545288
517544423 517553100
517544450 517553332
517560334 517553403
517560691 517553456
517551100 0517685100
517551274 517554274
517551334 517554288
517551403 517554403
517551523 517578403
517551682 517578682
517551691 517686100
517550274 517686274
517550332 517686334
517550403 517686403
517550691 517686691

 

[Narce]

Hi JE McGimpsey,

Could you help me with this as well ?

I really thank you for your previous help. Just in case you can not help me
with this one

----------------------------------------------

The result I would like now is as follows.

Data entered in column A will be changing.
Data in column B will be static.




It should first check cell B2 to see if a matching value in found in the
range of cells A2:A28 Should make a Z in C2 if a matching value is found in
the range of cells A2:A28. and add the current date to the respective row in
column C.
This is the case, found it in A2, so it makes a Z in C2 and add the current
date in cell D2 (see the example).

Should make a Z in C3 if a matching value in found in the range of cells
A2:A28.
This is the case, found it in B13, so it makes a Z in C3 (see the example)

Should make an N in C4 if a non-matching value in found in the range of
cells B2:B28.
This is the case, it did not find it, so it makes a N in C4 (see the
example)

There will be some cases where the value of some of the cells in column A is
the same. In those cases the result should be as follows:

When a matching value is found and a value in column C is already Z, then it
should look for...
Column E and enter a Z
Culumn F enter the current date.


When coulmns C and E have a Z value....
it should enter the data in column G and the current date in coulmn H (see
example in row 3). Note that colums E and G was entered in the same date,
that's why columns F and H have the same date.

If not maching value is found in the range of cells A2:A28 then...
Enter a N in column E (No current date is needed in column F) (see example
in row 4).

This checking should be made for every value from cell A2 to A28 (A28 may
change to any row number)





A B C D
E F G H
0517684287 0517684287 Z 22-nOV-2004
0517685100 517685109 Z 21-nOV-2004 Z
23-nov-2004 Z 23-nov-2004
5175432884 517543288 N
517543433 517543332 Z 23-nov-2004 N
517543403 517543403
517543332 517543424
517543332 517555288
517543332 517555403
517543337 517552274
517544288 5175522
517544334 517552403
517685109 517545100
517544559 517545288
517544423 517553100
517544450 517553332
517560334 517553403
517560691 517553456
517551100 0517685100
517551274 517554274
517551334 517554288
517551403 517554403
517551523 517578403
517551682 517578682
517551691 517686100
517550274 517686274
517550332 517686334
517550403 517686403
517550691 517686691