# 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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### 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
6
Views
144
Replies
1
Views
236
Replies
2
Views
402
Replies
1
Views
188
Replies
8
Views
120

1,141,931
Messages
5,709,403
Members
421,635
Latest member
mehdi hannechi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back