VBA - Find Matching cells and insert Filepath in new sheet

[rtc123]

Hello all,

I am completely new to this forum - so apologies in advance for any redundancies within my question, and thanks in advance for any and all help given.

I currently have two columns in two separate sheets: Column1 (Sheet1) contains random numbers (cusips), while Column2 (Sheet2) has the filepath names of PDF files that are saved with a file name that contains a matching random number from Column1.

Question: How do I create a sub within VBA that will, by using the specific characters within the Column2 string to match with the corresponding characters in Column1, and then copy/paste that entire filepath name to another sheet (Sheet3)?



Thanks again for any provided help, and I look forward to learning much more on this forum.

 

[Peter_SSs]

Welcome to the MrExcel board!

Could we have a small set of dummy data from Column A Sheet1, Column B Sheet2 and the results you would want in Sheet3 from that sample data, together with any further clarification you might have?

My signature block below has a link with suggestions for showing sample data.

 

[rtc123]

Thanks Peter, here is what is displayed on each sheet:

Sheet1:

Excel 2013 32 bit

Cusip

06744CES6

06744CEU1

22550BET6

22550BEN9

22550BEQ2

38148DXD1

38148DXF6

40054LMU5

40054LMX9

40435FCU9

40435FCR6

40435FCQ8

40435FCW5

48126YEL3

48126YEK5

46647MF24

46647MF32

06744CET4

06744CEW7

22550BES8

22550BEP4

22550BER0

38148DXE9

40054LMV3

40054LMY7

40435FCT2

40435FCV7

40435FCR6

48126YEM1

46647ME90

46647MF40

46647MVW0

06744CBB6

17324CKU9

40435FAV9

[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Sheet2:


Excel 2013 32 bit

FilePath

M:\AIG\Projects\test files\PDF Charts\46647MVW0 chart file.pdf

M:\AIG\Projects\test files\PDF Charts\06744CBB6 chart file 2.pdf

M:\AIG\Projects\test files\PDF Charts\17324CKU9 chart file 2.pdf

M:\AIG\Projects\test files\PDF Charts\40435FAV9 chart file 2.pdf

[Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]


Sheet3:


Excel 2013 32 bit

Cusip	Chart 1	Chart 2
06744CES6
06744CEU1
22550BET6
22550BEN9
22550BEQ2
38148DXD1
38148DXF6
40054LMU5
40054LMX9
40435FCU9
40435FCR6
40435FCQ8
40435FCW5
48126YEL3
48126YEK5
46647MF24
46647MF32
06744CET4
06744CEW7
22550BES8
22550BEP4
22550BER0
38148DXE9
40054LMV3
40054LMY7
40435FCT2
40435FCV7
40435FCR6
48126YEM1
46647ME90
46647MF40
46647MVW0
06744CBB6
17324CKU9
40435FAV9

[Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]


So basically, I want a VBA code that helps to match the filepath names in Sheet2 with their corresponding numbers in Sheet1. If there is a match, I want the filepath name to be inserted in Sheet3 under the correct column (Chart #) and row (cusip). Thanks again and I hope this clarifies things....

 

[Peter_SSs]

Could we have ... the results you would want in Sheet3 from that sample data,

Is that what you have posted? Your Sheet3 just looks like a repeat of Sheet1.

 

[rtc123]

Here is the result I'd want In Sheet3:

Excel 2013 32 bit

Cusip	Chart 1	Chart 2
06744CES6
06744CEU1
22550BET6
22550BEN9
22550BEQ2
38148DXD1
38148DXF6
40054LMU5
40054LMX9
40435FCU9
40435FCR6
40435FCQ8
40435FCW5
48126YEL3
48126YEK5
46647MF24
46647MF32
06744CET4
06744CEW7
22550BES8
22550BEP4
22550BER0
38148DXE9
40054LMV3
40054LMY7
40435FCT2
40435FCV7
40435FCR6
48126YEM1
46647ME90
46647MF40
46647MVW0	M:\AIG\Projects\test files\PDF Charts\46647MVW0 chart file.pdf
06744CBB6		M:\AIG\Projects\test files\PDF Charts\06744CBB6 chart file 2.pdf
17324CKU9		M:\AIG\Projects\test files\PDF Charts\17324CKU9 chart file 2.pdf
40435FAV9		M:\AIG\Projects\test files\PDF Charts\40435FAV9 chart file 2.pdf

[Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

 

[Peter_SSs]

Here is the result I'd want In Sheet3:

Thanks. First question is: Do you really need a macro?
Would this formula solution suffice? I have don it on a single sheet to post here but you should be able to adapt it to 2 sheets.

Excel Workbook
	A
1	FilePath
2	M:\AIG\Projects\test files\PDF Charts\46647MVW0 chart file.pdf
3	M:\AIG\Projects\test files\PDF Charts\06744CBB6 chart file 2.pdf
4	M:\AIG\Projects\test files\PDF Charts\17324CKU9 chart file 2.pdf
5	M:\AIG\Projects\test files\PDF Charts\40435FAV9 chart file 2.pdf
Sheet2

Formulas in B2 & C2 copied down. (I have hidden most rows to save space here - they were blank per your sample)

Excel Workbook
	A	B	C
1	Cusip	Chart 1	Chart 2
2	06744CES6
3	06744CEU1
32	46647MF40
33	46647MVW0	M:\AIG\Projects\test files\PDF Charts\46647MVW0 chart file.pdf
34	06744CBB6		M:\AIG\Projects\test files\PDF Charts\06744CBB6 chart file 2.pdf
35	17324CKU9		M:\AIG\Projects\test files\PDF Charts\17324CKU9 chart file 2.pdf
36	40435FAV9		M:\AIG\Projects\test files\PDF Charts\40435FAV9 chart file 2.pdf
Sheet3

 

[rtc123]

That's great Peter, thanks so much. As a novice, I'm still trying to figure out what is best for VBE and what's best just as an excel formula. Thanks so much for your help!

 

[Peter_SSs]

That's great Peter, thanks so much. As a novice, I'm still trying to figure out what is best for VBE and what's best just as an excel formula. Thanks so much for your help!

You are very welcome. Glad it helped. Thanks for letting us know.