Question description:
This user has given permission to use the problem statement for this
blog.
Hello I am trying to merge data are you able to help me? I have one column in a workbook that needs to be transfer to my master list
Solved by S. Q. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/10/2018 - 07:11
Hi
Excelchat Expert
11/10/2018 - 07:12
Welcome to excelchat
Excelchat Expert
11/10/2018 - 07:12
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows
Excelchat Expert
11/10/2018 - 07:12
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
11/10/2018 - 07:12
Could you please share the file?
User
11/10/2018 - 07:13
Yes I will add it for you now it is very large so I will just add the first couple rows
Excelchat Expert
11/10/2018 - 07:13
sure
User
11/10/2018 - 07:15
I want to mark the address in sheet 2 as being support or not in sheet one
Excelchat Expert
11/10/2018 - 07:16
Which ever combinations available in sheet 2 are available in sheet 1 should be marked as being support?
User
11/10/2018 - 07:17
If a person lives at the same address in sheet 2
User
11/10/2018 - 07:17
I need that marked in sheet 1 under Household Support as a yes or a no
Excelchat Expert
11/10/2018 - 07:17
okay
Excelchat Expert
11/10/2018 - 07:20
I have the formula in column N
User
11/10/2018 - 07:20
ok let me try it
Excelchat Expert
11/10/2018 - 07:20
sure
User
11/10/2018 - 07:21
All of the values come up as No in my excel
Excelchat Expert
11/10/2018 - 07:21
Yes, Since none of the addresses are available in sheet2
User
11/10/2018 - 07:22
Please explain
Excelchat Expert
11/10/2018 - 07:24
I am looking up table in sheet 2 to see if the address in column E & F on sheet 1 is available in available there
Excelchat Expert
11/10/2018 - 07:24
If it is available then yes other wise No
Excelchat Expert
11/10/2018 - 07:25
As an example, Iinserted the address in first row on sheet 2 and the result in first row changed to yes
Excelchat Expert
11/10/2018 - 07:25
First, in the sheet 2, I created a concatenated column in C
Excelchat Expert
11/10/2018 - 07:25
And in D have all the values as Yes
Excelchat Expert
11/10/2018 - 07:26
Then used =IFERROR(VLOOKUP(E2&F2,Sheet2!$C:$D,2,0),"No")
Excelchat Expert
11/10/2018 - 07:26
to map them
Excelchat Expert
11/10/2018 - 07:26
If the address in sheet 1 is found in sheet 2 then Yes is returned other wise No
Excelchat Expert
11/10/2018 - 07:27
Let me know if you have any queries
User
11/10/2018 - 07:27
That still doesn't work in my excel
User
11/10/2018 - 07:27
is the problem that my excel is not named sheet 1
Excelchat Expert
11/10/2018 - 07:28
What is the second sheet named?
User
11/10/2018 - 07:28
First sheet is named WARD22andPhoneNumbers
User
11/10/2018 - 07:29
the second sheet is Sheet2
Excelchat Expert
11/10/2018 - 07:29
Also, are the columns same?
Excelchat Expert
11/10/2018 - 07:29
I made some modification to sheet2, did you replicate that
User
11/10/2018 - 07:30
yes
Excelchat Expert
11/10/2018 - 07:30
okay
Excelchat Expert
11/10/2018 - 07:31
Can you share screenshots of top few rows in each sheet?
User
11/10/2018 - 07:31
yes shure
User
11/10/2018 - 07:31
sure
User
11/10/2018 - 07:33
[Uploaded an Excel file]
User
11/10/2018 - 07:33
[Uploaded an Excel file]
User
11/10/2018 - 07:33
Can you see those alright
Excelchat Expert
11/10/2018 - 07:34
yes
User
11/10/2018 - 07:34
Great can you see the issue
Excelchat Expert
11/10/2018 - 07:35
not yet
Excelchat Expert
11/10/2018 - 07:35
In sheet 2, create column C and D
Excelchat Expert
11/10/2018 - 07:35
in c2, enter the formula: =A2&B2
Excelchat Expert
11/10/2018 - 07:36
And in D2 enter Yes
User
11/10/2018 - 07:37
Ok let me try
User
11/10/2018 - 07:38
I got column C created now what do I do for D
Excelchat Expert
11/10/2018 - 07:39
Just enter Yes thorughout
Excelchat Expert
11/10/2018 - 07:39
Then on N2 in sheet 1, enter =IFERROR(VLOOKUP(E2&F2,Sheet2!$C:$D,2,0),"No")
User
11/10/2018 - 07:39
Oh ok I see what you mean now
Excelchat Expert
11/10/2018 - 07:41
Did that work?
User
11/10/2018 - 07:41
[Uploaded an Excel file]
User
11/10/2018 - 07:42
My table looks like that now I could fit support in D can you change the formula to accommodate that please
Excelchat Expert
11/10/2018 - 07:42
you inserted in column F, insert it in C
Excelchat Expert
11/10/2018 - 07:42
I will modify the formula to not need that at all
User
11/10/2018 - 07:43
Ok
Excelchat Expert
11/10/2018 - 07:43
=IF(IFERROR(VLOOKUP(E2&F2,Sheet2!$C:$D,1,0),"No")="No","No","Yes")
Excelchat Expert
11/10/2018 - 07:43
use this formula in N2
User
11/10/2018 - 07:46
Ok I did that but it didn't seem to get as many names as it should
Excelchat Expert
11/10/2018 - 07:48
i am not sure why that could be happening
Excelchat Expert
11/10/2018 - 07:48
The data might be like that
User
11/10/2018 - 07:50
Is there a way I can calculate how many values are in a colume so I can check
Excelchat Expert
11/10/2018 - 07:51
which column do you want to count?
User
11/10/2018 - 07:52
Sheet 1N
User
11/10/2018 - 07:53
If you give me a formula to do that though for any colume I can check my data to see if they match up
Excelchat Expert
11/10/2018 - 07:53
=countif(N2:N29,"Yes")
Excelchat Expert
11/10/2018 - 07:53
This is to count yes
Excelchat Expert
11/10/2018 - 07:53
=countif(N2:N29,"No") ,to count no
User
11/10/2018 - 07:55
Where to I enter this
Excelchat Expert
11/10/2018 - 07:55
any blank cell
Excelchat Expert
11/10/2018 - 07:56
=countif(N:N,"Yes")
Excelchat Expert
11/10/2018 - 07:56
use this one
User
11/10/2018 - 07:57
I have 3470 support
User
11/10/2018 - 07:57
will that formula it tells me I get 1126
User
11/10/2018 - 07:57
with that
Excelchat Expert
11/10/2018 - 07:58
okay
Excelchat Expert
11/10/2018 - 07:59
Can you identify an example, where it shold say support butis not?
User
11/10/2018 - 07:59
Let me check
Excelchat Expert
11/10/2018 - 08:04
Anything
Excelchat Expert
11/10/2018 - 08:04
?
User
11/10/2018 - 08:05
BAKEWELL CRES 301
User
11/10/2018 - 08:05
To show you list I will have to upload a large file is that ok ?
Excelchat Expert
11/10/2018 - 08:05
yes
User
11/10/2018 - 08:05
moment please
User
11/10/2018 - 08:07
it will take some time to upload
Excelchat Expert
11/10/2018 - 08:07
okay
Excelchat Expert
11/10/2018 - 08:07
The session will end in 4 minutes
User
11/10/2018 - 08:08
It just upload the first hlaf
User
11/10/2018 - 08:10
There you do it is all there now
User
11/10/2018 - 08:10
Can you please check it for me
Excelchat Expert
11/10/2018 - 08:11
which row?
User
11/10/2018 - 08:11
BAKEWELL CRES 301
User
11/10/2018 - 08:11
It should be household support
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.
