Log In
Register
Home
March 10, 2016
Offline
I have a file with 7000 data
I have attached a small section here
I need to filter all duplicate names
Then, check the filtered list -
The double values in the city as well.
And now -
Anyone whose name + his city is the same -
It is written twice because it has 2 phone numbers
I want to transfer one phone number to a separate column.
How to do it?
Thanks for replying, Leah
VIP
Trusted Members
June 25, 2016
Offline
Hi Lea
No idea which phone number (1st, 2nd etc) you wanted to transfer.
Maybe something like this?
In cell D2 enter
=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=2,C2,"")
It will transfer the 2nd phone number into column D.
Adjust the number in RED if necessary.
Hope this helps
Sunny
March 10, 2016
Offline
Thanks for the response!
I wrote the function and it is attached here in the file.
But I have 2 problems:
1. Phone 1 + Phone 2 receives the same phone number
And I have to define it - next to the first number - the second number appears.
2. Another thing -
After moving the number I need to delete the line of the unnecessary number.
I would love to know how to do it.
Because I have thousands of rows and it can save me a lot of time! Thanks!!
VIP
Trusted Members
June 25, 2016
Offline
Hi Lea
I believe you wanted the 2nd TEL to appear in TEL 2 if the NAME and CITY appears a second time (duplicate).
This is my suggestion. It will only work if the NAME and CITY appears a maximum of 2 times.
1) Sort your data by NAME, CITY, TEL
2) Insert the formula (refer attachment) in the TEL 2 column.
3) Filter TEL 2 ignoring blanks.
4) Copy the filtered data to a new sheet.
Hope this helps
Sunny
1 Guest(s)
