Insurance lookup

kenc

New Member
Joined
Jul 25, 2006
Messages
10
I need to look down a column of numbers and do the following:
if cell a1 is the same as cell a2 next cell
If cell a2 is different than cell a3 then cell f2 get an E1

If cell a2 and a3 are the same then next cell
if cell a3 and a4 are different then cell f3 gets E2

If cell a1 and cell a2 are different then cell f1 gets E

This needs to go all the way to the bottom of the column
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
A simple macro can solve your problem

Goto tools > macro > macros.. > give a name and create

paste the following code between private sub and end sub

For a = 2 To 10
If Cells(a, 1) <> Cells(a + 1, 1) Then
Cells(a, 6) = Cells(a - 1, 5)
End If
Next a

instead of 10 you can use the actual number of rows in your column
press F5 to run the macro

Ravi shankar
 

kenc

New Member
Joined
Jul 25, 2006
Messages
10
Insurance macro

Nope that did not work. it just copied what was in cell a. I need to look down the column and put an E in cell f if it is a single match and E1 if two matches and if more than two matches than an E2 in cell f.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Kenc

Have you considered a Pivot Table? It sounds like it may well suit your needs (or facilitate another solution).

Alternatively, and I don't think it's perfect, enter the following formula in F1:

Code:
=IF(A1=A2,"","E1")

and then the following in F2 and copied down:

Code:
=IF(A2=A3,"","E"&COUNTIF($F$1:F1,"E*")+1)

Hope this helps!

Richard
 

kenc

New Member
Joined
Jul 25, 2006
Messages
10
Thanks for your help, unfortunately it did not give me what i need. I could upload a sample.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,148
Members
410,666
Latest member
Al3cs
Top