**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.*