Question description:
This user has given permission to use the problem statement for this
blog.
I have a sheet of 4 columns.
Step1 - Column C has addresses and I want that column separately worked on: remove bad addresses and leave the good ones.
Step2 - I want Column C (now with only the good addresses) brought back into the sheet and for all the "good addresses" cells to align back with the other cells that were on their individual rows.
Solved by T. C. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
21/02/2018 - 01:46
Hello, Welcome to Got It pro
User
21/02/2018 - 01:46
Okay. Hello.
Excelchat Expert
21/02/2018 - 01:47
Please share your file with me so that I can help you
User
21/02/2018 - 01:47
Sensitive - Sorry I can't
Excelchat Expert
21/02/2018 - 01:48
Then, Pleas write an example similar to that on screen
Excelchat Expert
21/02/2018 - 01:48
and tell me about good and bad address
Excelchat Expert
21/02/2018 - 01:48
that I can help
User
21/02/2018 - 01:48
Column C has email addresses
Excelchat Expert
21/02/2018 - 01:49
Ok, so what is the difference between good and bad address?
Excelchat Expert
21/02/2018 - 01:49
Please write some data on the screen
User
21/02/2018 - 01:50
https://i.imgur.com/fZPrcI1.png
User
21/02/2018 - 01:50
screenshot
Excelchat Expert
21/02/2018 - 01:51
ok, so how to differentiate netween good and bad address?
Excelchat Expert
21/02/2018 - 01:51
between*
User
21/02/2018 - 01:51
Someone is going to work on Column C to remove the bad email addresses and after that there will obviously be difference between old Col. C and new Col. C
Excelchat Expert
21/02/2018 - 01:52
so how to differentiate netween good and bad address?
User
21/02/2018 - 01:52
I now want someone to replace the data from new Col.C into their respective cells (as they were in old Col.C)
Excelchat Expert
21/02/2018 - 01:53
I a not getting you sir
User
21/02/2018 - 01:53
It's not your job to remove and filter bad addresses - your job is to restore cells from new Col.C in their respective slots as they were before.
Excelchat Expert
21/02/2018 - 01:53
Ok got it
Excelchat Expert
21/02/2018 - 01:54
but the answer depends on how the person is going to remove the bad adderesses?
Excelchat Expert
21/02/2018 - 01:54
Is he going to copy the whole sheet and paste in a new sheet and then edit?
User
21/02/2018 - 01:55
Okay, so for example the addresses in the screenshot had the 3rd, 7th and 8th cell in the email column as bad. I want to know if the remaining email addresses (after the cleaning has taken place) be restored in their respective cells.
User
21/02/2018 - 01:55
The output of the cleaning comes back in txt form
Excelchat Expert
21/02/2018 - 01:56
I didn't get you
Excelchat Expert
21/02/2018 - 01:56
Can you see the screen?
User
21/02/2018 - 01:57
Yes
Excelchat Expert
21/02/2018 - 01:57
Som I just wrote an example on Sheet1 and copied that data and pasted on sheet2
Excelchat Expert
21/02/2018 - 01:57
Now, please make the changes in sheet2
Excelchat Expert
21/02/2018 - 01:58
i.e. remove bad addresses on sheet2
User
21/02/2018 - 01:58
After cleaning this column only C3 C7 remain but they appear in text result. How will you be able to know in which cell they should go.
Excelchat Expert
21/02/2018 - 01:58
I will help, just remove the bad addresses
Excelchat Expert
21/02/2018 - 01:59
Done?
User
21/02/2018 - 01:59
Yes
Excelchat Expert
21/02/2018 - 01:59
ok
Excelchat Expert
21/02/2018 - 01:59
please wait
User
21/02/2018 - 01:59
That will not be good because some cells will go on wrong rows - Do you get it now.
Excelchat Expert
21/02/2018 - 02:00
I am working on it
Excelchat Expert
21/02/2018 - 02:00
please wait
User
21/02/2018 - 02:00
If you understand the concept that's fine.
User
21/02/2018 - 02:01
The rows are about 1.9m by the way - they don't open in normal excel sheet :)
Excelchat Expert
21/02/2018 - 02:03
Done
Excelchat Expert
21/02/2018 - 02:04
Please check Sheet1 Column C now
User
21/02/2018 - 02:05
I really don't think that's it. You know what I appreciate your effort but I think you don't fully get my issue.
User
21/02/2018 - 02:05
Sorry.
Excelchat Expert
21/02/2018 - 02:06
I am sorry, please tell me again step by step
User
21/02/2018 - 02:06
Hmm...
User
21/02/2018 - 02:06
Hold on let me get you some data
Excelchat Expert
21/02/2018 - 02:06
Ok
User
21/02/2018 - 02:09
I cant open the EDIT DOCUMENT
User
21/02/2018 - 02:11
I cant edit document
Excelchat Expert
21/02/2018 - 02:11
where?
Excelchat Expert
21/02/2018 - 02:12
what is it showing?
User
21/02/2018 - 02:13
It's working now
Excelchat Expert
21/02/2018 - 02:13
Ok
User
21/02/2018 - 02:13
Can you see the sheet?
Excelchat Expert
21/02/2018 - 02:13
yes
Excelchat Expert
21/02/2018 - 02:13
S'heet3
Excelchat Expert
21/02/2018 - 02:13
right?
User
21/02/2018 - 02:14
Yes.
Excelchat Expert
21/02/2018 - 02:14
now tell me
User
21/02/2018 - 02:16
Now what I'm going to do is remove some cells in EMAIL column. I will put the remaining in a new sheet 5. I want you to match the remaining cells with the names that you will see in SHEET 6.
Excelchat Expert
21/02/2018 - 02:18
where is sheet6 ?
User
21/02/2018 - 02:19
Now go to sheet 5 - take the addresses there and match them against the right names in Sheet 6
Excelchat Expert
21/02/2018 - 02:19
Ok, please wait
User
21/02/2018 - 02:20
That's what I've been trying to explain. I hope it makes sense :)
Excelchat Expert
21/02/2018 - 02:20
you want email id on sheet 6 in column c
Excelchat Expert
21/02/2018 - 02:20
right?
User
21/02/2018 - 02:21
Erm....there are only 2 columns for now. Yes I want a Email column in Sheet6 But I want the email addresses to go to the right Name (Column A)
Excelchat Expert
21/02/2018 - 02:22
Ok
User
21/02/2018 - 02:22
The names should match the remaining email addresses as they were on the same rows in Sheet3
Excelchat Expert
21/02/2018 - 02:24
Ok
Excelchat Expert
21/02/2018 - 02:27
Done
Excelchat Expert
21/02/2018 - 02:27
No, wait a min.
User
21/02/2018 - 02:28
Forgive perhaps I'm bad at explaining things. The final result would have empty cells (the bad email addresses that were removed)
Excelchat Expert
21/02/2018 - 02:28
yes yes, got it
User
21/02/2018 - 02:29
Take the cells in Sheet5 and go Sheet6 using some special formula or whatever Excel Pros do and THEN match the remaining cells from Sheet5 with its corresponding cells under NAME Column.
User
21/02/2018 - 02:30
Is there a formula that can do this because I have about 2m records
Excelchat Expert
21/02/2018 - 02:41
Done
Excelchat Expert
21/02/2018 - 02:41
Please check now
Excelchat Expert
21/02/2018 - 02:42
Please see Sheet 6
Excelchat Expert
21/02/2018 - 02:42
I have used Sheet 3 and Sheet 5 to get Sheet 6
Excelchat Expert
21/02/2018 - 02:42
there?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
