Excel how to change a value automatically

Solved/Closed
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
This is my first question on the forum so I would first like to start by saying hello to all members and moderators :)
My question today which I'm hoping somebody will be able to help me with relates to Excel 2007. Basically I have my phone bill itemised on the operators website each month with the option to download it to excel. I have imported my contacts into worksheet1 and my call list to worksheet2 within the same document. My problem now is that I am trying to set it up so excel looks at the numbers in worksheet 2 and checks for matches in my contacts (worksheet1) and if it matches a contact It will change the number to a name, and at the bottom it will tell me how many time each name appears. I appreciate this may be very hard to do but if anyone can offer a solution it would be very much appreciated.
Thanks in advanced for any help
Ryan.

3 replies

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Rjn239,

So sheet 1 could look like this:
Henkie - 06123456789
Jopie - 06987456321
Kees - 06789654123

Now you need to place the names behind the numbers or add a extra set of names behind the numbers like:
06123456789 - Henkie
06987456321 - Jopie
06789654123 - Kees
or
Henkie - 06123456789 - Henkie
Jopie - 06987456321 - Jopie
Kees - 06789654123 - Kees

Now you have got things set up for the VLOOKUP function.
Create an extra column on sheet 2 and use VLOOKUP to retreive the name corresponding to the number.
Not exactly what you asked for, but the easiest way to get it done imo.

For your second question you could use an array formula.
At the end of sheet 2 you could enter the names again with a formula in the cell next to it which could look like this:
=SUM(IF(D2:D50="Henkie",1,0))
An array formula needs to be confirmed by hitting Ctrl+Shift+Enter.

Best regards,
Trowa
TrowaD, thanks for taking the time to write a reply for me.
I am going to give the VLookup formula ago, but was wondering if you could give me advice on using the formula.
On sheet 1 I have set it up as follows (eg):
Henkie - 06123456789
Jopie - 06987456321
Kees - 06789654123

Now on sheet 2 I have clicked on the Vlookup formula and it pops up with a box for me to fill in (excel 2007) what would I enter in the following:
lookup_value
table_array
col_index_num
range_lookup

and the =SUM(IF(D2:D50="Henkie",1,0)) formula worked perfect thanks again :)
Ryan.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Ryan,

lookup_value = The value (phone number) you want to lookup.
table_array = The table of content containing the value.
col_index_num = The column number which contains the value (name) you want to retrieve.
range_lookup = [optional] false: find the exact value. True or empty: find the closest match.

Now you might understand why I wanted you to rearrange you data like:
06123456789 - Henkie
06987456321 - Jopie
06789654123 - Kees
or
Henkie - 06123456789 - Henkie
Jopie - 06987456321 - Jopie
Kees - 06789654123 - Kees

Since the data you want to lookup needs to be in front of the data you want to retrieve.

So on your second sheet you have a list (eg. column D) of phone numbers. Insert an empty column next to them.
Then your formula would look like this:
=VLOOKUP(D1,Sheet1!\$A\$1:\$B\$3,2,FALSE)
So D1 contains the phone number you want to look up.
Sheet1!\$A\$1:\$B\$3 contains the list of phone numbers and names.
The number 2 tells excel to retrieve the data from the second column.
FALSE: find exact match.

The \$ signs makes the array absolute, which doesn't change the array when dragged down.

Hopefully this makes sense.

Feel free to ask if something is still unclear.

Best regards,
Trowa
Yep after a bit of tweaking it worked perfectly, I understand what you mean about the number ordering and have rearranged them and got it to work. Want to say thanks for your time and effort very much appreicated :)
Ryan.
0
Just a quick query in regards to the formula you gave ryan:
=SUM(IF(D2:D50="Henkie",1,0))
I tried this formula with my own work, when I put this formula into cell A1 for example it all works lovely, but when I drag this down to put the same formula into A2,3,4, and 5 etc the D2:D50 range increases everytime, for example:
In cell A1 the primary cell the formula is =SUM(IF(D2:D50="Henkie",1,0))
In cell A2 =SUM(IF(D3:D51="Henkie",1,0))
In cell A3 =SUM(IF(D4:D52="Henkie",1,0))
In cell A4 =SUM(IF(D5:D53="Henkie",1,0))

How would I stop the range increasing? so in cell A2,3,4,5 it would still read =SUM(IF(D2:D50="Henkie",1,0)) just by dragging it down? Thanks, John.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi John,

You will need to make your cell reference absolute by adding a \$ symbol.

You can do this to the column and/or row by manually adding a \$ symbol or after selecting the range and hitting F4 a couple of times untill the desired result is visible.

In your case you want your row to be absolute so the formula would look like:
=SUM(IF(D\$2:D\$50="Henkie",1,0))

Best regards,
Trowa