Finding a ref # in two excel worksheets

August 26, 2012 at 20:16:00
Specs: Windows XP
Hi, I have to excel spreadsheets. One is a manual register and one is an export from a system. The columns are obviously different and I need to check that the system has all of the information being contained in the manual spreadsheet. The only common information is reference number but the problem is that this reference number is contained in a sentence on the system export. So what I need is formula that will look at the reference # on the manual spreadsheets and search through the system export spreadsheets and tell me which row and column contains the reference number. Can you please assist?

See More: Finding a ref # in two excel worksheets

Report •

#1
August 27, 2012 at 05:34:50
If the sentences in the system report are formatted the same, then you might be able to use the Data...Text-To-Columns feature to split the sentences into separate cells, placing the "reference numbers" all in one column. Once that's done, there are various lookup functions that could be used to compare the 2 sheets, using the reference numbers as the key.

e.g. If you have something like this:

                 A
1 This is Reference Number 85 of 100
2 This is Reference Number 42 of 100
3 This is Reference Number 54 of 100
4 This is Reference Number 12 of 100

You could use Data..text-To-Columns to get this:

                A              B        C
1 This is Reference Number     85     of 100
2 This is Reference Number     42     of 100
3 This is Reference Number     54     of 100
4 This is Reference Number     12     of 100

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 28, 2012 at 16:16:22
The sentences are not the same which is why I am trying to locate it.. below is an example

Spreadsheet 1 - eg 1 - Defects Notice - DF_0079
eg 2 - DF_0022- MIP00020- Kedron BR 701

Spreadsheet 2 - contains a list of the DF number like DF_0079

I need the formula to look from spreadsheet 2 through a certain column (J) and find the DF number in the sentence and tell me where it is located or preferably the inspection number that is in column B.


Report •

#3
August 28, 2012 at 19:55:00
If you are starting with this...

                J
1   Defects Notice - DF_0079
2   DF_0022- MIP00020- Kedron BR 701

...then this formula should extract the DF number:

=MID(J1,FIND("DF_",J1),7)

If you put this in Sheet2!A1 - perhaps a new Column A if you already have data in Column A - and drag it down, then you should be able to VLOOKUP on Sheet 1 to return the value from any other column in Sheet 2.

Note: I used the string "DF_" to hopefully ensure that no other string like "DF" or an underscore causes the FIND function to return the wrong start_num argument for the MID function.

e.g.

MP_P00020 - DF_0022
or
Defects Notice DF443 - DF_0079

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question