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 V. E. 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 - 08:13
Welcome to Excelchat, I see that your question is about merging data in a master sheet.
Excelchat Expert
11/10/2018 - 08:14
I would need to see the sheet and understand what you need done to be sure I can help using basic EXCEL.
Excelchat Expert
11/10/2018 - 08:14
Can you upload your sheet?
User
11/10/2018 - 08:15
The file I am working with I quite large so it takes so time to load
Excelchat Expert
11/10/2018 - 08:15
You can just load part of it to see the structure and requirements.
User
11/10/2018 - 08:16
I tried that before and the expert wasn't able to help me completely
Excelchat Expert
11/10/2018 - 08:17
Yes, I saw RE_POST and the concern is you may need support beyond basic EXCEL.
User
11/10/2018 - 08:17
Ok everything is loaded
User
11/10/2018 - 08:18
The goal is to mark all of the names of people living at houses listed in sheet 2
User
11/10/2018 - 08:18
as Household support in sheet 1
Excelchat Expert
11/10/2018 - 08:19
Are you able to upload an EXCEL file using the paper clip icon, in the chat box?
Excelchat Expert
11/10/2018 - 08:19
If not , I will load these sheets into EXCEL on my end.
User
11/10/2018 - 08:20
Ok you can do that but you must delete them when you are done because it is private information
Excelchat Expert
11/10/2018 - 08:20
We can review table structure here first to make sure I can help.
User
11/10/2018 - 08:20
When we did this last time only a fraction of the household supports came up as yes
Excelchat Expert
11/10/2018 - 08:21
What is the key field Sheet2 to Sheet1?
User
11/10/2018 - 08:21
I am trying to move data from sheet two to sheet one yes
Excelchat Expert
11/10/2018 - 08:22
But to do that, we need some common link between the 2 files.
Excelchat Expert
11/10/2018 - 08:23
I am guessing this was set up as a lookup KEY:
Excelchat Expert
11/10/2018 - 08:23
Woodson Street19- combo of street and number
Excelchat Expert
11/10/2018 - 08:25
If the street and number in SHEET 2 are found in SHEET 1, then update the Household Support column in SHEET 1-correct?
User
11/10/2018 - 08:25
Do whatever you think will make it work
Excelchat Expert
11/10/2018 - 08:27
Ok, let me look at a sample of the SHEET1 data and see what can be done on your very large sheet.
Excelchat Expert
11/10/2018 - 08:27
Please stand by.
User
11/10/2018 - 08:27
Alright
Excelchat Expert
11/10/2018 - 08:32
Sheet is very large and taking a while to setup
User
11/10/2018 - 08:32
I understand
Excelchat Expert
11/10/2018 - 08:33
Got the big one in, trying for sheet 2 now
User
11/10/2018 - 08:33
Ok thanks
User
11/10/2018 - 08:38
is it loaded yet
Excelchat Expert
11/10/2018 - 08:38
Yes, loaded and I think I see some problems we can fix.
User
11/10/2018 - 08:39
Thats great
Excelchat Expert
11/10/2018 - 08:40
I am a little worried that maybe the size prevented the lookup? It looks to be a very basic formula.
Excelchat Expert
11/10/2018 - 08:41
Lets make sure the Sheet2 table is setup correctly, then you copy the formula in your version of the sheet.
Excelchat Expert
11/10/2018 - 08:41
This is too large to pass back and fortth
User
11/10/2018 - 08:42
Ok can you do it all on your end and then send me a file ?
Excelchat Expert
11/10/2018 - 08:42
I can try, but size is going to be a challenge
User
11/10/2018 - 08:43
Is there anything I could do to help
User
11/10/2018 - 08:46
...
Excelchat Expert
11/10/2018 - 08:46
Get a smaller file, but no. I am testing now.
User
11/10/2018 - 08:49
ok
Excelchat Expert
11/10/2018 - 08:50
Still testing
Excelchat Expert
11/10/2018 - 08:53
I am not getting an YES entries , other than the test entry I created.
Excelchat Expert
11/10/2018 - 08:53
I will keep testing
Excelchat Expert
11/10/2018 - 08:55
Ok, the problem is the address data is not consistent-at least one test problem
Excelchat Expert
11/10/2018 - 08:56
Sheet 2 example: Willow Creek Circle
Trying to match : WILLOW CREEK CIR
in SHEET 1.
User
11/10/2018 - 08:57
Right I can see that
Excelchat Expert
11/10/2018 - 08:57
I am afraid the data would need to be cleaned up to match
User
11/10/2018 - 08:58
Ok the first guy that helped me was able to match 1200
User
11/10/2018 - 08:58
but there were many missing which could be clearly manually identified as being a yes because they matched
User
11/10/2018 - 08:58
to fix that is all I am asking
Excelchat Expert
11/10/2018 - 08:58
The expert must have done some data clean up
User
11/10/2018 - 08:59
=IF(IFERROR(VLOOKUP(E2&F2,Sheet2!$C:$D,1,0),"No")="No","No","Yes")
User
11/10/2018 - 08:59
He used this
User
11/10/2018 - 08:59
no he did not
Excelchat Expert
11/10/2018 - 08:59
=IFERROR(VLOOKUP(E5869&F5869,Sheet2!$C$2:$D$3470,2,FALSE),"No")
Excelchat Expert
11/10/2018 - 09:00
same formula, more or less
Excelchat Expert
11/10/2018 - 09:01
The FALSE is the same a zero in the first formula, means EXACT match.
User
11/10/2018 - 09:01
Can you give me the best formula you have now and let me try
Excelchat Expert
11/10/2018 - 09:02
=IFERROR(VLOOKUP(E5869&F5869,Sheet2!$C$2:$D$3470,2,FALSE),"No")
User
11/10/2018 - 09:03
Can you give me a formula to count the yes please
Excelchat Expert
11/10/2018 - 09:03
Sheet 2 needs clean up first. Make sure cois are sorted , since text data,
Excelchat Expert
11/10/2018 - 09:04
The Yes data in SHeet 1?
User
11/10/2018 - 09:04
the old formula may have loaded more yes values I want to check it now
User
11/10/2018 - 09:04
the yes data in sheet 1 yes
User
11/10/2018 - 09:06
?
Excelchat Expert
11/10/2018 - 09:07
1226 in the current sheet
Excelchat Expert
11/10/2018 - 09:08
We are running out of time. I can try an INDEX/match formula, that may have better memory performance.
Excelchat Expert
11/10/2018 - 09:08
I can work that and clean up off-line and send to you via site support.
User
11/10/2018 - 09:09
Try the index now
User
11/10/2018 - 09:09
and then we can clean up offline
User
11/10/2018 - 09:12
Hey hurry up I dont want to run out of time
Excelchat Expert
11/10/2018 - 09:12
Ok, let me try
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.
