Search Column B for String Found in Column A, Then Copy Matches To Next Column

N

nickf829

New Member
Joined
Nov 18, 2011
Messages
15
I have 2 columns of data, Column A has a file number and Column B has a list of files that each contain a file number that may or may not appear in Column A.
Before:
Excel Workbook
ABC
1File #File NameFile # Found within Column B
26201File 6201 - Encoding Inv.xml
36202File 6202 - Submission Tag Mismatch.xml
46203File 6203 - Submit after submission deadline.xml
56204File 6204 - Submission Type Tag Inv.xml
66205File 6204 - Submission Type WARNING.xml
76206File 6204 - Submission Type VALID.xml
86207File 6205 - Submission Type Inv.xml
96208File 6206 - Submission Version Tag Inv.xml
106209File 6207 - Submission Version Inv.xml
116210File 6208 - Submission Tag Inv.xml
126211File 6209 - File Audit Data Tag Inv.xml
136212File 6210 - File Number WARNING - length.xml
146213File 6210 - File Number Invalid Data - null.xml
156214File 6210 - File Number WARNING2 - space.xml
16File 6210 - File Number Invalid Data - type.xml
17File 6211 - File Number Tag Inv.xml
18File 6212 - Number of Files - Invalid Data - length.xml
19File 6212 - Number of Files - Invalid Data - null.xml
20File 6212 - Number of Files - VALID - space.xml
21File 6212 - Number of Files - VALID2 - type.xml
22File 6213 - Number of Files Tag Inv.xml
23File 6214 - Create Date Inv WARNING.xml
24File 6214 - Create Date Inv 011-dd-yyyy.xml
25File 9214 - Create Date Inv 02-30-yyyy.xml
26File 8214 - Create Date Inv VALID.xml
27File 7214 - Create Date Inv 06-31-yyyy.xml
Before
#VALUE!
Excel 2010



Any help would be greatly appreciated!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Try
C2: =IF(ISNA(VLOOKUP(MID(B2,6,4)+0,A:A,1,FALSE)),"Not Found",MID(B2,6,4)+0)
and copy down
 
Upvote 0
Thanks for the quick reply! I posted an example that happens to only have 4 digits in all of the file numbers and a very standardized file name format.

The File numbers(Col A) will be anywhere from 3-6 digits in the actual data, and the file name(Col B) will not be in as standardized of a format as the example I posted. Some will be in the format of "##### xxxxxxx.xml" or "xxxxxxxx #### xxxxxxx.xml" or any number of other possible formats, so the formula you posted wouldn't really work for what I need since the file names and numbers will not be standardized.

Thanks.
 
Upvote 0
since the file names and numbers will not be standardized.
Click to expand...
That will make it more challenging indeed.
are you expecting a formula solution or a vba solution?
Can you post samples of the most complex file names?
Get those to work, and everything else should work.
 
Upvote 0
Here's one approach which seems to work with your posted data, the rows showing File# in column C are the ones where no matching data was found and Index returned the Column A label.

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 315px"> <col style="WIDTH: 191px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="BACKGROUND-COLOR: #c0c0c0">File #</td> <td style="BACKGROUND-COLOR: #c0c0c0">File Name</td> <td style="BACKGROUND-COLOR: #c0c0c0">File # Found within Column B</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: right">6201</td> <td>File 6201 - Encoding Inv.xml</td> <td style="TEXT-ALIGN: right">6201</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: right">6202</td> <td>File 6202 - Submission Tag Mismatch.xml</td> <td style="TEXT-ALIGN: right">6202</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: right">6203</td> <td>File 6203 - Submit after submission deadline.xml</td> <td style="TEXT-ALIGN: right">6203</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: right">6204</td> <td>File 6204 - Submission Type Tag Inv.xml</td> <td style="TEXT-ALIGN: right">6204</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: right">6205</td> <td>File 6204 - Submission Type WARNING.xml</td> <td style="TEXT-ALIGN: right">6204</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: right">6206</td> <td>File 6204 - Submission Type VALID.xml</td> <td style="TEXT-ALIGN: right">6204</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: right">6207</td> <td>File 6205 - Submission Type Inv.xml</td> <td style="TEXT-ALIGN: right">6205</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: right">6208</td> <td>File 6206 - Submission Version Tag Inv.xml</td> <td style="TEXT-ALIGN: right">6206</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: right">6209</td> <td>File 6207 - Submission Version Inv.xml</td> <td style="TEXT-ALIGN: right">6207</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: right">6210</td> <td>File 6208 - Submission Tag Inv.xml</td> <td style="TEXT-ALIGN: right">6208</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: right">6211</td> <td>File 6209 - File Audit Data Tag Inv.xml</td> <td style="TEXT-ALIGN: right">6209</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: right">6212</td> <td>File 6210 - File Number WARNING - length.xml</td> <td style="TEXT-ALIGN: right">6210</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</td> <td style="TEXT-ALIGN: right">6213</td> <td>File 6210 - File Number Invalid Data - null.xml</td> <td style="TEXT-ALIGN: right">6210</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</td> <td style="TEXT-ALIGN: right">6214</td> <td>File 6210 - File Number WARNING2 - space.xml</td> <td style="TEXT-ALIGN: right">6210</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</td> <td> </td> <td>File 6210 - File Number Invalid Data - type.xml</td> <td style="TEXT-ALIGN: right">6210</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</td> <td> </td> <td>File 6211 - File Number Tag Inv.xml</td> <td style="TEXT-ALIGN: right">6211</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</td> <td> </td> <td>File 6212 - Number of Files - Invalid Data - length.xml</td> <td style="TEXT-ALIGN: right">6212</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</td> <td> </td> <td>File 6212 - Number of Files - Invalid Data - null.xml</td> <td style="TEXT-ALIGN: right">6212</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</td> <td> </td> <td>File 6212 - Number of Files - VALID - space.xml</td> <td style="TEXT-ALIGN: right">6212</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</td> <td> </td> <td>File 6212 - Number of Files - VALID2 - type.xml</td> <td style="TEXT-ALIGN: right">6212</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</td> <td> </td> <td>File 6213 - Number of Files Tag Inv.xml</td> <td style="TEXT-ALIGN: right">6213</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</td> <td> </td> <td>File 6214 - Create Date Inv WARNING.xml</td> <td style="TEXT-ALIGN: right">6214</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</td> <td> </td> <td>File 6214 - Create Date Inv 011-dd-yyyy.xml</td> <td style="TEXT-ALIGN: right">6214</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</td> <td> </td> <td>File 9214 - Create Date Inv 02-30-yyyy.xml</td> <td>File #</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</td> <td> </td> <td>File 8214 - Create Date Inv VALID.xml</td> <td>File #</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</td> <td> </td> <td>File 7214 - Create Date Inv 06-31-yyyy.xml</td> <td>File #</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C2</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B2,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C3</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B3,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C4</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B4,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C5</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B5,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C6</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B6,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C7</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B7,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C8</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B8,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C9</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B9,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C10</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B10,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C11</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B11,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C12</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B12,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C13</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B13,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C14</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B14,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C15</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B15,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C16</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B16,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C17</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B17,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C18</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B18,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C19</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B19,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C20</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B20,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C21</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B21,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C22</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B22,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C23</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B23,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C24</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B24,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C25</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B25,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C26</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B26,1)),ROW($A$1:$A$15))),1)}</td></tr> <tr> <td>C27</td> <td>{=INDEX($A$1:$A$15,MIN(IF(ISNUMBER(FIND($A$1:$A$15,$B27,1)),ROW($A$1:$A$15))),1)}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
nickf829 said:
I have 2 columns of data, Column A has a file number and Column B has a list of files that each contain a file number that may or may not appear in Column A.
Before:
Excel Workbook
ABC
1File #File NameFile # Found within Column B
26201File 6201 - Encoding Inv.xml
36202File 6202 - Submission Tag Mismatch.xml
46203File 6203 - Submit after submission deadline.xml
56204File 6204 - Submission Type Tag Inv.xml
66205File 6204 - Submission Type WARNING.xml
76206File 6204 - Submission Type VALID.xml
86207File 6205 - Submission Type Inv.xml
96208File 6206 - Submission Version Tag Inv.xml
106209File 6207 - Submission Version Inv.xml
116210File 6208 - Submission Tag Inv.xml
126211File 6209 - File Audit Data Tag Inv.xml
136212File 6210 - File Number WARNING - length.xml
146213File 6210 - File Number Invalid Data - null.xml
156214File 6210 - File Number WARNING2 - space.xml
16File 6210 - File Number Invalid Data - type.xml
17File 6211 - File Number Tag Inv.xml
18File 6212 - Number of Files - Invalid Data - length.xml
19File 6212 - Number of Files - Invalid Data - null.xml
20File 6212 - Number of Files - VALID - space.xml
21File 6212 - Number of Files - VALID2 - type.xml
22File 6213 - Number of Files Tag Inv.xml
23File 6214 - Create Date Inv WARNING.xml
24File 6214 - Create Date Inv 011-dd-yyyy.xml
25File 9214 - Create Date Inv 02-30-yyyy.xml
26File 8214 - Create Date Inv VALID.xml
27File 7214 - Create Date Inv 06-31-yyyy.xml
Before
#VALUE!
Excel 2010



Any help would be greatly appreciated!
Click to expand...
C2, just enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$15&" "," "&$B2&" "),$B$2:$B$15)
 
Upvote 0
tweedle said:
That will make it more challenging indeed.
are you expecting a formula solution or a vba solution?
Can you post samples of the most complex file names?
Get those to work, and everything else should work.
Click to expand...

I need a VBA solution, but if I get a formula that works, it shouldn't be to hard to convert.

Unfortunately, I do not know what the most complex file name would be. Many people within my company will be using the tool I'm designing, and they all have different naming conventions for the XML files they are creating. The only common denominator between everyone who will use the tool is that a file number will be included, everything else is personal preference

Also, to make matters a bit more confusing, I will not always have only 15 File #'s in Column A, sometime there will be hundreds, other times just a few. The number of File Names can be in the thousands also. So ideally, a solution that is not specific to a number of rows would be best.

Aladin, your formula worked if I changed it to:
=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$9999&" "," "&$B2&" "),$A$2:$A$9999)
I have a question though, what is the purpose of the 9.9999999..? Also, is there a way to change the output from the default #N/A when no value is found? I could probably use this formula in VBA then just copy/paste value over the data so that I could use this.

Taureen, I was not able to get your formula to work. I used CTRL+SHIFT+ENTER and the formula just appears as text

Thanks for all the help!
 
Upvote 0
Try

Code: 
Sub test()
Dim LR1 As Long, LR2 As Long, i As Long, j As Long, Found As Boolean
LR1 = Range("A" & Rows.Count).End(xlUp).Row
LR2 = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR1
    Found = False
    For j = 2 To LR2
        If Range("B" & j).Value Like "*" & Range("A" & i).Value & "*" Then
            Found = True
            Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & i).Value
        End If
    Next j
    If Not Found Then
        Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & i).Value & " - Not found"
    End If
Next i
End Sub
 
Upvote 0
nickf829 said:
[...]

Aladin, your formula worked if I changed it to:
=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$9999&" "," "&$B2&" "),$A$2:$A$9999)
I have a question though, what is the purpose of the 9.9999999..? Also, is there a way to change the output from the default #N/A when no value is found? I could probably use this formula in VBA then just copy/paste value over the data so that I could use this...
Click to expand...

1) SEARCH creates an array, consisting of position indication numbers for substrings it finds in the string in B2 and #VALUE! errors for subtrings it cannot find in B2.

2) Then...

LOOKUP(9.99999999999999E+307,{20,3,#VALUE!,#VALUE!},{"X","P","Q","W"})

will yield P which corresponds to the last numeric value in {20,3,#VALUE!,#VALUE!}.

3) The following links has more info on the LOOKUP usage, including the provenance of the big number:

http://www.mrexcel.com/forum/showthread.php?t=102091
http://www.mrexcel.com/forum/showthread.php?t=310278 (Post #7)
 
Upvote 0
When the formula appears as "TEXT" means the cell is formatted as Text. So the formula won't get calculated. So change the formatting to "General" and then CTRL + SHIFT + ENTER this formula.

But Aladin has provided simpler and better formula which shows grasp and depth (y), which is what you should use.

Only part where you need the Error to be handled differently so add this:
=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$9999&" "," "&$B2&" "),$A$2:$A$9999),"NOT FOUND")

which will give you intended TEXT in cells where Data is not found.
 
Upvote 0
You must log in or register to reply here.

Similar threads

ChrisMac1
  • Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
217
ChrisMac1
ChrisMac1
A
  • Question
Automation of Copy and Merge Data in Column A and B of all files in a folder. Order of Data Differs
Replies
2
Views
613
ashish002
A
W
  • Question
VBA - Copy columns to a new spreadsheet based on date filter
Replies
0
Views
996
WilliamA
W
D
  • Question
VBA - Split Source file-Find oldest date in column. Copy all rows with date to new sheet, Repeat on next oldest date. Also, remove some unwanted data.
Replies
0
Views
1K
DK643
D
M
  • Question
Combine TXT, copy to Excel, remove blank rows and rows not starting with numbers, extract number from txt strings, txt-2-column
Replies
0
Views
1K
mkseto
M

Forum statistics

Threads
1,220,932
Messages
6,156,925
Members
451,386
Latest member
leolagoon94

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top