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

[nickf829]

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
	A	B	C
1	File #	File Name	File # Found within Column B
2	6201	File 6201 - Encoding Inv.xml
3	6202	File 6202 - Submission Tag Mismatch.xml
4	6203	File 6203 - Submit after submission deadline.xml
5	6204	File 6204 - Submission Type Tag Inv.xml
6	6205	File 6204 - Submission Type WARNING.xml
7	6206	File 6204 - Submission Type VALID.xml
8	6207	File 6205 - Submission Type Inv.xml
9	6208	File 6206 - Submission Version Tag Inv.xml
10	6209	File 6207 - Submission Version Inv.xml
11	6210	File 6208 - Submission Tag Inv.xml
12	6211	File 6209 - File Audit Data Tag Inv.xml
13	6212	File 6210 - File Number WARNING - length.xml
14	6213	File 6210 - File Number Invalid Data - null.xml
15	6214	File 6210 - File Number WARNING2 - space.xml
16		File 6210 - File Number Invalid Data - type.xml
17		File 6211 - File Number Tag Inv.xml
18		File 6212 - Number of Files - Invalid Data - length.xml
19		File 6212 - Number of Files - Invalid Data - null.xml
20		File 6212 - Number of Files - VALID - space.xml
21		File 6212 - Number of Files - VALID2 - type.xml
22		File 6213 - Number of Files Tag Inv.xml
23		File 6214 - Create Date Inv WARNING.xml
24		File 6214 - Create Date Inv 011-dd-yyyy.xml
25		File 9214 - Create Date Inv 02-30-yyyy.xml
26		File 8214 - Create Date Inv VALID.xml
27		File 7214 - Create Date Inv 06-31-yyyy.xml
Before

#VALUE!

Excel 2010



Any help would be greatly appreciated!

 

[nickf829]

Thank you for the help. The formula worked great, but is there a cleaner way to do this in VBA rather than just have the code copy/paste the formula where neccessary?

 

[VoG]

Thank you for the help. The formula worked great, but is there a cleaner way to do this in VBA rather than just have the code copy/paste the formula where neccessary?

Did you try this http://www.mrexcel.com/forum/showpost.php?p=2976662&postcount=8 ?

 

[nickf829]

Did you try this http://www.mrexcel.com/forum/showpost.php?p=2976662&postcount=8 ?

VoG. I somehow missed that. Blame it on the holidays!

Thank you very much! It did mostly what I needed. I modified it a bit to fit what I'm truely doing, but I'm still stuck on one thing.

"B" is a list of file names, "C" is a list of "A" values that appear in "B". What I need is to have the remaining "B" values that do not contain an "A" value to display "N/A" in the "C" column beside the corresponding "B" filename.

I need this so that I can group all "C" values that don't contain an "A" value and prompt the user to keep or remove these files names as they most likely will not be needed.

Notice in the example, the last three cells of the "C" column are empty, but there are filenames listed in the "B" column. Since these null "C" cells are due to the filenames not containing an "A" column value, I want the corresponding "C" cells to display "N/A"

Example:

Excel Workbook
	A	B	C
1	File #	File Name	File # Found within Column B
2	6201	File 6201 - Encoding Inv.xml	6201
3	6202	File 6202 - Submission Tag Mismatch.xml	6202
4	6203	File 6203 - Submit after submission deadline.xml	6203
5	6204	File 6204 - Submission Type Tag Inv.xml	6204
6	6205	File 6204 - Submission Type WARNING.xml	6204
7	6206	File 6204 - Submission Type VALID.xml	6204
8	6207	File 6205 - Submission Type Inv.xml	6205
9	6208	File 6206 - Submission Version Tag Inv.xml	6206
10	6209	File 6207 - Submission Version Inv.xml	6207
11	6210	NEED XML(S) CREATED	6208 NEED XML(S)
12	6211	File 6209 - File Audit Data Tag Inv.xml	6209
13	6212	File 6210 - File Number WARNING - length.xml	6210
14	6213	File 6210 - File Number Invalid Data - null.xml	6210
15	6214	File 6210 - File Number WARNING2 - space.xml	6210
16	6215	File 6210 - File Number Invalid Data - type.xml	6210
17	6216	File 6211 - File Number Tag Inv.xml	6211
18		File 6212 - Number of Files - Invalid Data - length.xml	6212
19		File 6212 - Number of Files - Invalid Data - null.xml	6212
20		File 6212 - Number of Files - VALID - space.xml	6212
21		File 6212 - Number of Files - VALID2 - type.xml	6212
22		File 6213 - Number of Files Tag Inv.xml	6213
23		File 6214 - Create Date Inv WARNING.xml	6214
24		File 6214 - Create Date Inv 011-dd-yyyy.xml	6214
25		NEED XML(S) CREATED	6215 NEED XML(S)
26		NEED XML(S) CREATED	6216 NEED XML(S)
27		File 9214 - Create Date Inv 02-30-yyyy.xml
28		File 8214 - Create Date Inv VALID.xml
29		File 7214 - Create Date Inv 06-31-yyyy.xml
After Macro is Run

Excel 2007

 

[taurean]

This is standard Loop inside Loop:

[color=darkblue]Public[/color] [color=darkblue]Sub[/color] FindUnmatched()
[color=darkblue]Const[/color] csErr     [color=darkblue]As[/color] [color=darkblue]String[/color] = "#N/A"
[color=darkblue]Dim[/color] lLastRowA   [color=darkblue]As[/color] [color=darkblue]Long[/color], lLastRowB [color=darkblue]As[/color] [color=darkblue]Long[/color]
[color=darkblue]Dim[/color] r           [color=darkblue]As[/color] Range
Application.ScreenUpdating = [color=darkblue]False[/color]
lLastRowA = Range("A" & Rows.Count).End(xlUp).Row
lLastRowB = Range("B" & Rows.Count).End(xlUp).Row
[color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] lLastRowA
    [color=darkblue]For[/color] j = 2 [color=darkblue]To[/color] lLastRowB
    [color=darkblue]If[/color] InStr(Range("B" & j).Value, Range("A" & i).Value) > 0 Then _
    Range("C" & j).Value = Range("A" & i).Value
    [color=darkblue]Next[/color] j
[color=darkblue]Next[/color] i
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] Next
Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Value = csErr
Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]