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

mab463

New Member
Joined
Sep 5, 2014
Messages
1
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.

Thanks for any help you can provide.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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.
 
Upvote 0
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 ??
 
Upvote 0
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"
 
Upvote 0
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)
 
Upvote 0
Hi Mike
Wasn't sure if the OP wanted to add another column.....hence the Sub...(y)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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