Forum

Notifications
Clear all

Automatically move a given column to another column

4 Posts
2 Users
0 Reactions
80 Views
(@usb)
Posts: 244
Honorable Member
Topic starter
 

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

 
Posted : 01/11/2019 10:52 am
(@sunnykow)
Posts: 1417
Noble Member
 

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

 
Posted : 01/11/2019 12:55 pm
(@usb)
Posts: 244
Honorable Member
Topic starter
 

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

 
Posted : 01/11/2019 11:12 pm
(@sunnykow)
Posts: 1417
Noble Member
 

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

 
Posted : 02/11/2019 12:50 am
Share: