Column Compare

rabalam · Aug 9, 2010

So I will try and explain to my best ability as to what I am trying to acomplish.

U have two sepreate spreadsheet for name sake we'll call them SheetA and SheetB.

I have information in Column a, b, C, and D in sheetA
I have information in Column a, B, c in sheetB

The format of information on sheetA column A 1901234 unsorted

The format of the information on sheet be column A abcdefg1901234 unsorted

I am tryin to compare the two documents and the columns and actully try and match them up so that they can appear in one document side by side.

I am curently going line by line from sheet A doing a search and find and the equivalent last 7 digits and adding the information maually. Well I have over 500 devices I have to this for. I knwo there got to be a short cut. Any help would be greatly appreciated.

Thanks,

mgirvin · Aug 10, 2010

Are you trying to get my posted example to work and it does not work? Or are you trying to apply the posted formula to your data set. The formulas I posted will not work on your data set without some adaptation. It seemed like the data set that you posted was not complete, and so I created a new data example.

As far as getting the example I posted to work, try this example:

Try copying and pasting this to a sheet named "SheetA":

Excel Workbook

A

B

C

D

E

F

1

Room #

Cube #

Old jack #

X-con Y/N

Switch ID / port

Device ID (NWAM#)

2

135a

2

6235

1817046

3

112

3

6236

1847576

4

108

2

6287

1850699

5

13

2

6288

1901674

6

12

1

6286

1036158

7

135

3

6289

1905849

8

425

2

6245

1905835

SheetA

Copy and paste this to a sheet named "SheetB":

Excel Workbook

A

B

C

D

1

NTWK Drop

Current DNS Name

Room #

Cube #

2

6235

nwlvkam1817046.some.domain.com

135a

2

3

6236

nwlvkam1847576.some.domain.com

112

3

4

6287

nwlvkam1850699.some.domain.com

108

2

5

6288

nwlvkam1901674.some.domain.com

13

2

6

6286

nwlvkam1036158.some.domain.com

12

1

7

6289

nwlvkam1905849.some.domain.com

135

3

8

6245

nwlvkam1905835.some.domain.com

425

2

SheetB

Then create these formulas and copy down (these are the formulas for the green cells above):

Excel Workbook

A

1

NTWK Drop

2

6235

SheetB

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook

C

1

Room #

2

135a

SheetB

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook

D

1

Cube #

2

SheetB

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Can you get that to work?

As I said before, this should work with data like:

ddsdsds15874.some.com

not like this:

ddsdsds15874.some.com434523

I do see some other data that has more than one number in it, but I thought your question was specific to this statement:

The End result would then look like the below:

6245 nwlvkam1905835.some.domain.com 425 2

rabalam · Aug 11, 2010

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Crabalam%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="Preview" href="file:///C:%5CUsers%5Crabalam%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_preview.wmf"><link rel="themeData" href="file:///C:%5CUsers%5Crabalam%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Crabalam%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> <table class="MsoNormalTable" style="width: 325pt; margin-left: 4.4pt; border-collapse: collapse; border: medium none;" border="1" cellpadding="0" cellspacing="0" width="433"> <tbody><tr style="height: 15pt;"> <td style="width: 59pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

#VALUE!<o

></o

>

</td> <td style="width: 178pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvk01la-04.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#VALUE!<o

></o

>

</td> <td style="width: 44pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#VALUE!<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

#N/A<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

lvkmedrpt2.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

6235<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1817046.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59"> 135a<o

></o

>
</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

2<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

6236<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1847576.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

112<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

3<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

6287<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1850699.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

108<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

2<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

6288<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1901674.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

13<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

2<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

6286<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1036158.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

12<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

1<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

6289<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1905849.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

135<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

3<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

6245<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1905835.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

425<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

2<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

#N/A<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1905837.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

#N/A<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1905800.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

#N/A<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1905810.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> </tr> <tr style="height: 15pt;"> <td style="width: 59pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="79">

#N/A<o

></o

>

</td> <td style="width: 178pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="237">

nwlvkam1905818.wa.kp.org<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> <td style="width: 44pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="59">

#N/A<o

></o

>

</td> </tr> </tbody></table> <o

>
</o

>
<o

>okay so I used you data set and it worked perfect. I decided I would then take my information from my original spreadsheet and plug it into the newly created spreadsheet. Funny thing happened the line immediately following you data set started stating "#N/A".
</o

>
<o

>Not sure why the information did not work.. I even created one spreadsheet with two worksheets. This way I would not have to modify the formula at all.</o

>
Your thoughts?

~r
<o

></o

>

mgirvin · Aug 11, 2010

I still do not know what the logic is for extracting the code (lookup value or value to match) in the first place. As I said before:

this should work with data like:

ddsdsds15874.some.com

not like this:

ddsdsds15874.some.com434523

Meaning, from this:

ddsdsds15874.some.com

the formula can get this:

15874.

However if you need to start with this:

nwlvk01la-04.wa.kp.org

and extract this:

01la-04

to use as the lookup value (value to match), then see the amended formulas below.

The #VALUE! error is simply saying that a zero can't be added to a word. The formulas below address this problem.

The #N/A error simply says that the lookup value is not in the table of SheetA.

The key for you in explaining what you are trying to do is to give examples of the "Current DNS Name" and what part of that should be used as the lookup value or value to be matched in SheetA.

But first try this:

Excel Workbook

A

B

C

D

E

F

1

Room #

Cube #

Old jack #

X-con Y/N

Switch ID / port

Device ID (NWAM#)

2

135a

2

6235

1817046

3

112

3

6236

1847576

4

108

2

6287

1850699

5

13

2

6288

1901674

6

12

1

6286

1036158

7

135

3

6289

1905849

8

425

2

6245

1905835

9

56

5

3555

01la-04

SheetA

Excel Workbook

A

B

C

D

1

NTWK Drop

Current DNS Name

Room #

Cube #

2

6235

nwlvkam1817046.some.domain.com

135a

2

3

6236

nwlvkam1847576.some.domain.com

112

3

4

6287

nwlvkam1850699.some.domain.com

108

2

5

6288

nwlvkam1901674.some.domain.com

13

2

6

6286

nwlvkam1036158.some.domain.com

12

1

7

6289

nwlvkam1905849.some.domain.com

135

3

8

3555

nwlvk01la-04.wa.kp.org

56

5

9

#N/A

sasdd55144dasdads

#N/A

10

#N/A

11

6245

nwlvkam1905835.some.domain.com

425

2

SheetB

formulas to be copied down column:

Excel Workbook

A

1

NTWK Drop

2

6235

SheetB

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook

C

1

Room #

2

135a

SheetB

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook

D

1

Cube #

2

SheetB

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

rabalam · Aug 11, 2010

Can I send you my data set?

mgirvin · Aug 11, 2010

You can upload it to a public site, and then send me the link so that I can download it.

One note, though: sometimes when the information created by an Excel solution is crucial, it can dangerous to use formulas or VBA that you do not know anything about. That said, many people do it all the time.

I will take a look and do my best.

rabalam · Aug 11, 2010

Hey mgirvin so I decided to try some debugging myself and looked at the calculated steps. What I found looking at the output was that the formula is looking in sheetA but it's only looking at 6 values and not the in the entire column "F" on sheetA not sure that makes sense.

mgirvin · Aug 11, 2010

That is good detective work! Now, just change the range so that it looks at the whole range and see if that works.

mgirvin · Aug 11, 2010

After looking at your data, I do not see a pattern that I can exploit to create an extract formula that can be used to match the two data sets. Here is some of the data (and there are lots of blanks):

lvk-oh-2
lvk-scan-06
1906012
1906014
lvk-recept-1
1847154
1906657
LVK-FP-8
LK-FP-3
NONE
NWLVKOIFP-13
REGISTER
REGESTER 7
NO LABEL

So what you are asking is how to extract from this:

lvkmedrpt1.wa.kp.org
nwlvk01lalvk-recept-1.wa.kp.org
lvkmedrpt2.wa.kp.org
nwlvkam1817046.wa.kp.org
nwlvkam1847576.wa.kp.org
nwlvkam1850699.wa.kp.org
nwlvkam1901674.wa.kp.org

and get this:

1.wa.kp
lvk-recept-1
t2.wa.kp
1817046
1847576
1850699
1901674

to use in a matching lookup to retrieve data.

I don't see the pattern.

Does anyone see a pattern, or know how to do a Fuzzy Match?

rabalam · Aug 11, 2010

I' really only intersted in the "nwlvkam1817046.wa.kp.org" pattern the others I can do by hand bu the bulk of the devices are "nwlvkam1817046.wa.kp.org" so if I can just get those it will save me about 75% of the work.

mgirvin · Aug 11, 2010

The formulas above will work for the numbered ones, if you change the range. Maybe these will work:

A2:

Change this:

=INDEX(SheetA!$C$2:$C$9,MATCH(MID(B2,MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0),MATCH(9.99E+307,MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)+0)-MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0)+1),SheetA!$F$2:$F$9&"",0))

to this:

=INDEX(SheetA!$C$2:$C$660,MATCH(MID(B2,MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0),MATCH(9.99E+307,MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)+0)-MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0)+1),SheetA!$F$2:$F$660&"",0))

C2:

Change this:

=INDEX(SheetA!$A$2:$A$9,MATCH(MID(B2,MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0),MATCH(9.99E+307,MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)+0)-MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0)+1),SheetA!$F$2:$F$9&"",0))

to this:

=INDEX(SheetA!$A$2:$A$660,MATCH(MID(B2,MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0),MATCH(9.99E+307,MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)+0)-MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0)+1),SheetA!$F$2:$F$660&"",0))

D2:

Change this:

=INDEX(SheetA!$B$2:$B$9,MATCH(MID(B2,MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0),MATCH(9.99E+307,MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)+0)-MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0)+1),SheetA!$F$2:$F$9&"",0))

to this:

=INDEX(SheetA!$B$2:$B$660,MATCH(MID(B2,MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0),MATCH(9.99E+307,MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)+0)-MATCH(TRUE,ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)),0)+1),SheetA!$F$2:$F$660&"",0))

I hope that helps.

Column Compare

rabalam

New Member

mgirvin

Well-known Member

Excel Facts

rabalam

New Member

mgirvin

Well-known Member

rabalam

New Member

mgirvin

Well-known Member

rabalam

New Member

mgirvin

Well-known Member

mgirvin

Well-known Member

rabalam

New Member

mgirvin

Well-known Member

Similar threads

Forum statistics

Share this page

Column Compare

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock