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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thanks for your help, unfortunately it did not give me what i need. I could upload a sample.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top