Question description:
This user has given permission to use the problem statement for this
blog.
I am trying to create a condition or something wherein if the username (Column A) and student ID (Column B) from sheet1 matches the username (Column A) and student ID (Column B) from sheet2 then the (Column C) data from sheet2 gets automatically copied over to (Column C) on sheet1.
Solved by T. B. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/09/2017 - 09:01
hi
Excelchat Expert
10/09/2017 - 09:02
now i understand what u need
User
10/09/2017 - 09:02
Hello
User
10/09/2017 - 09:02
I think that is all
User
10/09/2017 - 09:02
But I am happy to explain further
Excelchat Expert
10/09/2017 - 09:02
yeah wait
User
10/09/2017 - 09:02
if needed
Excelchat Expert
10/09/2017 - 09:03
the data i made is correct
Excelchat Expert
10/09/2017 - 09:03
?
User
10/09/2017 - 09:04
yes
User
10/09/2017 - 09:04
The username has some numbers
User
10/09/2017 - 09:04
like abc123
User
10/09/2017 - 09:04
and the student ID is lil 15496456
User
10/09/2017 - 09:05
*like
User
10/09/2017 - 09:05
excellent
Excelchat Expert
10/09/2017 - 09:07
both of username and ID has to be same with sheet 2, right?
User
10/09/2017 - 09:07
yes
Excelchat Expert
10/09/2017 - 09:07
i will do this by combining those 2 text
User
10/09/2017 - 09:07
and the data to copy is like: Woodland court, Norwood court, Eastwood,
User
10/09/2017 - 09:07
perfect
Excelchat Expert
10/09/2017 - 09:08
i will breakdown the formula in column E to J
Excelchat Expert
10/09/2017 - 09:09
oh wait i will just use a index and match formula
User
10/09/2017 - 09:09
alright
User
10/09/2017 - 09:14
everything ok?
Excelchat Expert
10/09/2017 - 09:15
wait
Excelchat Expert
10/09/2017 - 09:19
see
User
10/09/2017 - 09:19
yes
Excelchat Expert
10/09/2017 - 09:19
is the result ok?
Excelchat Expert
10/09/2017 - 09:19
how you want to deal with N/A
Excelchat Expert
10/09/2017 - 09:20
what value you would return instead
User
10/09/2017 - 09:20
Number 0 would be fine
Excelchat Expert
10/09/2017 - 09:20
now try to change the data
User
10/09/2017 - 09:20
Also can I ask for another favour
Excelchat Expert
10/09/2017 - 09:21
yes please
Excelchat Expert
10/09/2017 - 09:21
just tell me
User
10/09/2017 - 09:21
could we make the Studetn ID As A1 and Username as B1
User
10/09/2017 - 09:21
in both sheets
User
10/09/2017 - 09:21
:)
Excelchat Expert
10/09/2017 - 09:22
ok i will swap it
User
10/09/2017 - 09:22
I just want to be able to just copy the formula :D
User
10/09/2017 - 09:22
also on more thing
User
10/09/2017 - 09:23
In Sheet1, can you put Copied data as J1
User
10/09/2017 - 09:23
In Sheet2, can you put data to copy as K1
User
10/09/2017 - 09:24
Just to make it exactly the way I see it in my spreadsheet :)
Excelchat Expert
10/09/2017 - 09:25
have i done right?
User
10/09/2017 - 09:25
Let me try to copy the formula
Excelchat Expert
10/09/2017 - 09:26
you need to extend the range of my formula
Excelchat Expert
10/09/2017 - 09:26
you also can attach your file and i will do it for you
User
10/09/2017 - 09:27
It is confidential data, sorry
Excelchat Expert
10/09/2017 - 09:28
ok, you need to extend the ranges in the formula
User
10/09/2017 - 09:28
The name of Sheet1 is SAMIS
User
10/09/2017 - 09:28
The name of Sheet2 is ROOM SERVICE
Excelchat Expert
10/09/2017 - 09:30
Some excel version need you to press SHIFT+ENTER in the formula instead of just ENTER
User
10/09/2017 - 09:30
the range neds to be extended from (A2:A5000)
User
10/09/2017 - 09:30
the same for B
User
10/09/2017 - 09:31
as well as J and K
Excelchat Expert
10/09/2017 - 09:32
Ok extended
User
10/09/2017 - 09:33
For some reason it just gives me 0
User
10/09/2017 - 09:33
in all cells
Excelchat Expert
10/09/2017 - 09:33
please select range J2:J5000, click on the formula bar, press SHIFT+ENTER
Excelchat Expert
10/09/2017 - 09:34
some excel version consider this as a 'matrix formula'
Excelchat Expert
10/09/2017 - 09:34
sorry, CTRL+SHIFT+ENTER
User
10/09/2017 - 09:36
still the same
Excelchat Expert
10/09/2017 - 09:37
do you use excel 2016?
User
10/09/2017 - 09:37
yes
Excelchat Expert
10/09/2017 - 09:37
let me try on my excel
User
10/09/2017 - 09:41
Sheet1 is SAMIS
User
10/09/2017 - 09:41
maybe that is the issue?
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.