# Cell HH14 and HH15 equal but need to be 1 and 2

#### mab463

##### New Member
Hi!

You guys seem to have a wealth of knowledge that I'd love to tap into for a project I'm on. I had to have a spreadsheet with phone numbers. The system needs to know that it's 1 or 2 entries per person, so if Jenna has just a home phone she'll just have a 1, but if she has a work and home phone she'll have 2 rows of data and should have a 1 and 2 in front of her numbers to let the system know they're both associated with her.

I have thousands of these people with 2 numbers, and I can't find a good formula to express what I need. I have spaces between each person, so I know that if I see 1,1, next to each other (like HH14 and HH15 both have a 1) I know that it's really supposed to be 1 and 2 but I can't change it, because all I can do is drag down the one.

I need a formula to state if HH14 and HH15 = 1, make HH15 =2, if not leave blank. Does this make sense? Sorry for the long-winded question.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
How about a macro...try it on a test sheet first
Code:
``````Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "HH").End(xlUp).Row
For r = 1 To lr
If Range("HH" & r).Value = 1 And Range("HH" & r + 1).Value = 1 Then
Range("HH" & r + 1).Value = 2
End If
Next r
End Sub``````

Will check back

Michael M
A great macro. One problem I had with it was that you named the macro "MM1" that is not a proper name for a macro. I had to remove the "1" before it would run. Using Excel 2013.

I'm using 2013....I have no problem with the macro, and the name is one I use for ALL test macros.
What do you define as a "proper" name for a macro ??

It would not work for me with "MM1" I had to remove the number "1" from the macro name. It worked find with the name "MM"

If you have your names in column A, put this formula in B1 and drag down. It should put 1 next to the first occurance of a name, 2 next to the second occurance of that name, etc.

=COUNTIF(\$A\$1:\$A1, \$A1)

Hi Mike
Wasn't sure if the OP wanted to add another column.....hence the Sub...

Replies
2
Views
508
Replies
2
Views
292
Replies
7
Views
516
Replies
7
Views
449
Replies
7
Views
227

1,219,791
Messages
6,150,285
Members
450,949
Latest member
faizanmalik10

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