Question description:
This user has given permission to use the problem statement for this
blog.
I have two columns with ID numbers representing students in a school- if either column has a number that starts with 480, I need that number in a new column.
Solved by V. D. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
15/05/2018 - 01:16
data analysis
Excelchat Expert
15/05/2018 - 01:16
welcome !
User
15/05/2018 - 01:16
Hi
Excelchat Expert
15/05/2018 - 01:17
You have a question on matching values in a column?
User
15/05/2018 - 01:17
yes. I have over 3K data points each representing a student. Some ID numbers came over correctly in column A and some correctly in Column B
User
15/05/2018 - 01:18
I want to only grab the ID numbers from A and B that start with 480..... can I do that easily?
Excelchat Expert
15/05/2018 - 01:18
I think we can figure out a way that is not too complicated.
Excelchat Expert
15/05/2018 - 01:19
can you upload the sheet or a reasonable sample, maybe first 100 rows if each column.
User
15/05/2018 - 01:19
So I cant upload the exact data because of confidentiality but I can give an example....
Excelchat Expert
15/05/2018 - 01:20
Sure , just so we can have a valid test that the function is right for your actual need.
User
15/05/2018 - 01:23
so here is a sample
User
15/05/2018 - 01:24
so if any number in A or B start with 480, I want it to become C....
Excelchat Expert
15/05/2018 - 01:24
i will load off-line, this viewer is hard to get functions in.
Excelchat Expert
15/05/2018 - 01:27
These are labels entered as numbers - is that always the case?
User
15/05/2018 - 01:27
I will enter them as plain text since that's easier for VLookup which is the next step in my process
User
15/05/2018 - 01:28
is that your question?
Excelchat Expert
15/05/2018 - 01:29
Not pertaining to VLOOKUP, but just to match I need to have consistent. Text is fine.
Excelchat Expert
15/05/2018 - 01:29
Also , there is a chance both rows can have 480?
Excelchat Expert
15/05/2018 - 01:30
as the 1st example?
User
15/05/2018 - 01:30
yes
User
15/05/2018 - 01:30
and in that case they will match
Excelchat Expert
15/05/2018 - 01:31
What I mean is if both A1 and b1 match 480, what goes in C1?
User
15/05/2018 - 01:32
they will be identical, so it should be the exact number so in this example it would need to be 4804
Excelchat Expert
15/05/2018 - 01:32
I missed something?
Excelchat Expert
15/05/2018 - 01:33
So column B has a set of data and Column a, you need to just make sure column A starts with 480?
Excelchat Expert
15/05/2018 - 01:34
Am i looking for 480 in either column or checking row by row for a match?
User
15/05/2018 - 01:35
row by row for each 480 number
User
15/05/2018 - 01:36
if A and B both have a 480 number it will be the same 480 number
Excelchat Expert
15/05/2018 - 01:36
So if A and B have a 480 number , OK to grab A
User
15/05/2018 - 01:37
yes
Excelchat Expert
15/05/2018 - 01:37
ok
Excelchat Expert
15/05/2018 - 01:41
Take a look , I think this is all you need
[Uploaded an Excel file]
User
15/05/2018 - 01:42
SO the 999s I need them to be the 480 numbers in B...
User
15/05/2018 - 01:42
how do I do that?
User
15/05/2018 - 01:43
would I have to do separate and then merge somehow?
Excelchat Expert
15/05/2018 - 01:43
the 999 are NON match B to A.
Excelchat Expert
15/05/2018 - 01:44
so IF not a match get B?
Excelchat Expert
15/05/2018 - 01:44
If B is 480?
User
15/05/2018 - 01:44
if not a match get the 480 number
User
15/05/2018 - 01:44
either from A or B
User
15/05/2018 - 01:44
I need a column of only the 480 numbers from A or B...
User
15/05/2018 - 01:44
Its complicated I know :(
Excelchat Expert
15/05/2018 - 01:45
The best we can do without VBA, is a result that you can sort, hence the 999.
User
15/05/2018 - 01:45
ok I can do that and then copy and paste over...
Excelchat Expert
15/05/2018 - 01:45
So , we need and OR for either column =480
Excelchat Expert
15/05/2018 - 01:46
yes
User
15/05/2018 - 01:46
ok I will try that
User
15/05/2018 - 01:46
its easier than copy pasting 3500 rows after sorting
Excelchat Expert
15/05/2018 - 01:47
so , Either Column and if no match in either column then 999
Excelchat Expert
15/05/2018 - 01:47
is that the right logic?
User
15/05/2018 - 01:47
yes
User
15/05/2018 - 01:47
I can work it from here- so that will help!
User
15/05/2018 - 01:48
Thank you!
Excelchat Expert
15/05/2018 - 01:49
You just need to extend the formula for column B, with an IF (OR(....both conditions, "999" is the then if neither are 480 condition.
User
15/05/2018 - 01:49
=IF(LEFT(TRIM(A1),3)="480",A1,"999")
User
15/05/2018 - 01:49
can you write it sp I can copy and paste it?
Excelchat Expert
15/05/2018 - 01:49
Then copy, paste values, then sort and drop the 999.
Excelchat Expert
15/05/2018 - 01:50
Ok, I need to go back into EXCEL
Excelchat Expert
15/05/2018 - 01:54
Not the right logic , NOT OR. just nested IFs.
Excelchat Expert
15/05/2018 - 01:54
Trying again.
User
15/05/2018 - 01:56
no worries I think I got it worked out on my end now
Excelchat Expert
15/05/2018 - 01:56
=IF(LEFT(TRIM(A1),3)="480", a1, LEFT(TRIM(B1),3)="480"),b1,"999"))
Excelchat Expert
15/05/2018 - 01:57
This is the formula, but needs testing
Excelchat Expert
15/05/2018 - 01:57
If No match in A , look in B, if no match in either "999"
Excelchat Expert
15/05/2018 - 01:58
if this is a 1 time thing, you can put the simple formula in separate coulmns to test A and B, then paste together in another column, since you will have to paste anyway you go.
User
15/05/2018 - 02:01
okay! Thank you so much!
Excelchat Expert
15/05/2018 - 02:01
Ok; thanks for using the service. Please stop by again.
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.
