# Insurance lookup

#### kenc

##### New Member
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

### 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
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
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
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
Thanks for your help, unfortunately it did not give me what i need. I could upload a sample.

Replies
5
Views
30
Replies
1
Views
17
Replies
9
Views
142
Replies
3
Views
68
Replies
1
Views
62