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

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
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.
 

Forum statistics

Threads
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.
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
Top