Search from master list

[Jake975]

I have an workbook I’m having an issue with on the first sheet1 I have a list of current names inA, on the second sheet2 I have the master list with search names inA and correct names inC.
Here is my issue:
Sheet1 inA i get a list of names these have unique text in the name.
Sheet2 inA i have the same list without unique identity text but matches part of the name from Sheet1.
i have tried Index & Match and Vlookup but i get mostly right answers and some wrong ones. For example if I put yum.pdf on sheet1 it’s returning a name that it shouldn’t. (Yum isn’t in master list)
i have also tried search but it can’t look in a different sheet.
Basically I want this to happen:
if part of the text in A1 on sheet1 match text from range on Sheet2 return info from same row two columns to the right where text was found. And so on down list on sheet1

 

[mumps]

Try this macro:

Sub CompareNames()
    Application.ScreenUpdating = False
    Dim Rng As Range, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, fnd As Range
    Set WS1 = Sheets("Sheet1")
    Set WS2 = Sheets("Sheet2")
    For Each Rng In WS2.Range("A2", WS2.Range("A" & WS2.Rows.Count).End(xlUp))
        Set fnd = WS1.Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlPart)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = Rng.Offset(, 2)
        End If
    Next Rng
    Application.ScreenUpdating = True
End Sub

 

[Jake975]

That has worked the only thing I had to do was set a macro to sort columnA on sheet1 Z-A then run this and it pulls all of the names thank you for your help it was making me crazy for days trying to figure it out.
Thank you again!

 

[mumps]

You are very welcome.

 

[Jake975]

How do i get this macro to return the value to the right?
When i step through it it is looking like it is matching the correct names but is not returning a value to the right

Sub CompareNames()
    Application.ScreenUpdating = False
    Dim Rng As Range, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, fnd As Range
    Set WS1 = Sheets("Temp")
    Set WS2 = Sheets("Data List")
    For Each Rng In WS2.Range("C2", WS2.Range("C" & WS2.Rows.Count).End(xlUp))
        Set fnd = WS1.Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlPart)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = Rng.Offset(, 2)
        End If
    Next Rng
    Application.ScreenUpdating = True
End Sub

 

[mumps]

The macro was based on the screen shots in your original post. That data had the search name in column A. The revised macro you posted has the search name in column C. Use the XL2BB add-in (icon is in the menu) to post screen shots of your actual data.

 

[Jake975]

This is the first page like the one before:

exceltest1 (Working)0.xlsm
	A	B	C	D	E	F	G	H
1	Current Name	New Name					Folder Path	F:\Test\auto
2	Untitled_20200206_023255.pdf	Daily(scanned).pdf
3	trial_balance3763092.pdf	Trial Balance.pdf
4	test1.pdf
5	taxexempt3762977.pdf	Guest by Tax.pdf
6	stat_dmy_seg3763091.pdf	Market Code.pdf
7	nacc_authorization3762976.pdf
8	ihg_paidout_receipt_logo3762987.pdf	Paid Out(Other).pdf
9	ihg_logo_folio3763012.pdf
10	hkooobyreason3762986.pdf	Out of Order.pdf
11	guest_ledger3762979.pdf	Guest Ledger.pdf
12	gl_trial_balance3763090.pdf	Guest Trial Balance.pdf
13	giratevariance3762988.pdf	Rate Variance.pdf
14	gi_c_h3762978.pdf	Guest -in COMP.pdf
15	gi_authlimit3762985.pdf	Credit Limit alltypes.pdf
16	finpayments3762982.pdf	Cashier Aud.pdf
17	finopbalall3762980.pdf	Open Balance.pdf
18	finjrnlbytrans3762981.pdf	Journal by TRA.pdf
19	deposit_ledger3762983.pdf	Deposit Ledg.pdf
20	creditcard_history3762975.pdf	Credit Card History.pdf
21	artransfer3762989.pdf
22	aragingdet3762984.pdf
23	013_677487.pdf	Guest Ledger Detail.pdf
24	010_657038.pdf	Paid Out.pdf
25	003_428525.pdf	Manager Report.pdf
26	002_595993.pdf	New Trial Balance.pdf
27	001_692159.pdf	Title.pdf
28
Home

This is the master list I need to pull the numbers from. It is similar to the one before but now has the final order number listed for each file:

exceltest1 (Working)0.xlsm
	B	C	D
1	Example Name	Correct Name	Final Order
2	artransfer3762989.pdf	AR Settlement.pdf	15
3	finpayments3762982.pdf	Cashier Aud.pdf	20
4	011_194372.pdf	Cashier Audit.pdf	20
5	creditcard_history3762975.pdf	Credit Card History.pdf	7
6	gi_authlimit3762985.pdf	Credit Limit alltypes.pdf	9
7	Untitled_20200206_023255.pdf	Daily(scanned).pdf	2
8	deposit_ledger3766421.pdf	Deposit Ledg.pdf	12
9	depled_321427.pdf	Deposit Ledger.pdf	12
10	detaging_053783.pdf	Detailed Aging.pdf	14
11	gi_c_h3762978.pdf	Guest -in COMP.pdf	21
12	taxexempt3762977.pdf	Guest by Tax.pdf	16
13	gihcomp_506317.pdf	Guest COMP.pdf	21
14	013_677487.pdf	Guest Ledger Detail.pdf	18
15	guest_ledger3762979.pdf	Guest Ledger.pdf	19
16	gl_trial_balance3763090.pdf	Guest Trial Balance.pdf	17
17	finjrnlbytrans3762981.pdf	Journal by TRA.pdf	3
18	003_428525.pdf	Manager Report.pdf	4
19	manager_report3766426.pdf	Manager Report.pdf	4
20	stat_dmy_seg3763091.pdf	Market Code.pdf	11
21	002_595993.pdf	New Trial Balance.pdf	5
22	024_492359.pdf	Night Auth.pdf	8
23	finopbalall3762980.pdf	Open Balance.pdf	10
24	hkooobyreason3762986.pdf	Out of Order.pdf	22
25	010_657038.pdf	Paid Out.pdf	23
26	ihg_paidout_receipt_logo3762987.pdf	Paid Out(Other).pdf	23
27	giratevariance3762988.pdf	Rate Variance.pdf	13
28	001_692159.pdf	Title.pdf	1
29	trial_balance3763092.pdf	Trial Balance.pdf	6
30	ihg_logo_folio3763012.pdf
Data List

Here is the temp list that is created to export as CSV

exceltest1.xlsm
	A	B
1	Location	Final Order#
2	F:\Test\self\Aging.pdf
3	F:\Test\self\ARset.pdf
4	F:\Test\self\Cashier.pdf
5	F:\Test\self\Comp.pdf
6	F:\Test\self\CRE all types.pdf
7	F:\Test\self\CRE History.pdf
8	F:\Test\self\Deposit.pdf
9	F:\Test\self\GLD.pdf
10	F:\Test\self\Guest ledger.pdf
11	F:\Test\self\Guest tax.pdf
12	F:\Test\self\Guest Trial.pdf
13	F:\Test\self\Jou.pdf
14	F:\Test\self\Manager.pdf
15	F:\Test\self\Market.pdf
16	F:\Test\self\New Trial.pdf
17	F:\Test\self\Night Auth.pdf
18	F:\Test\self\Openbal.pdf
19	F:\Test\self\Out of Order.pdf
20	F:\Test\self\Paid.pdf
21	F:\Test\self\Ratevar.pdf
22	F:\Test\self\RM9006.pdf
23	F:\Test\self\Title.pdf
24	F:\Test\self\Trial.pdf
Temp

(I know file names don't match it was for testing only when all done it will be in one single file)
Basically I need the Final Order numbers to show up in the Temp page next to their right name to be able to custom sort in a fixed order every time. before I export
I have also attached the code that makes the temp sheet sit helps

Sub ListAllFileedit()
    Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet
Dim sPath As String
Dim lrA As Long
    Dim lrB As Long
    Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ws = Worksheets.Add
ActiveSheet.Name = "Temp"
Sheets("Home").Activate
'Get the folder object associated with the directory
sPath = ActiveSheet.Range("H1")
Set objFolder = objFSO.GetFolder(sPath)
Sheets("Temp").Activate
ws.Cells(1, 1).Value = "Location"
ws.Cells(1, 2).Value = "Final Order#"
    'ws.Cells(1, 3).Value = "The file Size is:"
    'Loop through the Files collection
For Each objFile In objFolder.Files
'If objFile.Name Like "*.pdf" Then
lrA = Range("A" & Rows.Count).End(xlUp).Row
'lrB = Range("B" & Rows.Count).End(xlUp).Row
ws.Range("A" & lrA + 1).Value = objFile.Path
'The line below is where I need to add the code to fill final order #
'ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
'ws.Range("C" & lrB + 1).Value = objFile.Size
'End If
Next
'ws.Cells(1, 1).delete
'Clean up!
Stop
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Temp").Copy
ActiveWorkbook.SaveAs Filename:=objFolder & "\" & "Temp.csv", FileFormat:=xlCSV
ActiveWorkbook.Close True
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Call delete
End Sub

I thank you again for all the help you have provided.?

 

[mumps]

Looking at your macro in Post #5, it looks like you want to compare column C in Data List to column A in Temp and return column D of Data List to column B of Temp. If this is correct, what is the purpose of the Home sheet? Based on the latest screenshots, could you please explain again referring to specific sheets and cells, which columns you want to match. Can you post a screen shot of the Temp list showing the expected results in column B?

 

[Jake975]

I apologize for the confusion the home page is not important for what i need to do.
Here is the data list i need to pull the numbers from:

exceltest1 (Working).xlsm
	A	B	C	D
1	Search Name	Example Name	Correct Name	Final Order
2	trial_balance	trial_balance3763092.pdf	Trial Balance.pdf	6
3	001_	001_692159.pdf	Title.pdf	1
4	giratevariance	giratevariance3762988.pdf	Rate Variance.pdf	13
5	010_	010_657038.pdf	Paid Out.pdf	23
6	ihg_paidout_receipt_logo	ihg_paidout_receipt_logo3762987.pdf	Paid Out(Other).pdf	23
7	hkooobyreason	hkooobyreason3762986.pdf	Out of Order.pdf	22
8	finopbalall	finopbalall3762980.pdf	Open Balance.pdf	10
9	024_	024_492359.pdf	Night Auth.pdf	8
10	002_	002_595993.pdf	New Trial Balance.pdf	5
11	stat_dmy_seg	stat_dmy_seg3763091.pdf	Market Code.pdf	11
12	003_	003_428525.pdf	Manager Report.pdf	4
13	manager_report	manager_report3766426.pdf	Manager Report.pdf	4
14	finjrnlbytrans	finjrnlbytrans3762981.pdf	Journal by TRA.pdf	3
15	gl_trial_balance	gl_trial_balance3763090.pdf	Guest Trial Balance.pdf	17
16	guest_ledger	guest_ledger3762979.pdf	Guest Ledger.pdf	19
17	013_	013_677487.pdf	Guest Ledger Detail.pdf	18
18	gi_c_h	gi_c_h3762978.pdf	Guest -in COMP.pdf	21
19	gihcomp_	gihcomp_506317.pdf	Guest COMP.pdf	21
20	taxexempt	taxexempt3762977.pdf	Guest by Tax.pdf	16
21	detaging_	detaging_053783.pdf	Detailed Aging.pdf	14
22	depled_	depled_321427.pdf	Deposit Ledger.pdf	12
23	deposit_ledger	deposit_ledger3766421.pdf	Deposit Ledg.pdf	12
24	Untitled_	Untitled_20200206_023255.pdf	Daily(scanned).pdf	2
25	gi_authlimit	gi_authlimit3762985.pdf	Credit Limit alltypes.pdf	9
26	creditcard_	creditcard_history3762975.pdf	Credit Card History.pdf	7
27	011_	011_194372.pdf	Cashier Audit.pdf	20
28	finpayments	finpayments3762982.pdf	Cashier Aud.pdf	20
29	artran_	artransfer3762989.pdf	AR Settlement.pdf	15
30	ihg_logo_folio	ihg_logo_folio3763012.pdf		25
Data List

I need the number to the right of the file name

exceltest1 (Working).xlsm
	A	B
1	Location	Final Order#
2	F:\Test\NEW\auto - Copy\Trial Balance.pdf	6
3	F:\Test\NEW\auto - Copy\Title.pdf	1
4	F:\Test\NEW\auto - Copy\Rate Variance.pdf	13
5	F:\Test\NEW\auto - Copy\Paid Out.pdf	23
6	F:\Test\NEW\auto - Copy\Out of Order.pdf	22
7	F:\Test\NEW\auto - Copy\Open Balance.pdf	10
8	F:\Test\NEW\auto - Copy\Night Auth.pdf	8
9	F:\Test\NEW\auto - Copy\New Trial Balance.pdf	5
10	F:\Test\NEW\auto - Copy\Market Code.pdf	11
11	F:\Test\NEW\auto - Copy\Manager Report.pdf	4
12	F:\Test\NEW\auto - Copy\Journal by TRA.pdf	3
13	F:\Test\NEW\auto - Copy\ihg_logo_folio3766175.pdf
14	F:\Test\NEW\auto - Copy\ihg_logo_folio3765986.pdf
15	F:\Test\NEW\auto - Copy\Guest Trial Balance.pdf	17
16	F:\Test\NEW\auto - Copy\Guest Ledger.pdf	19
17	F:\Test\NEW\auto - Copy\Guest Ledger Detail.pdf	18
18	F:\Test\NEW\auto - Copy\Guest COMP.pdf	21
19	F:\Test\NEW\auto - Copy\Guest by Tax.pdf	16
20	F:\Test\NEW\auto - Copy\Detailed Aging.pdf	14
21	F:\Test\NEW\auto - Copy\Deposit Ledger.pdf	12
22	F:\Test\NEW\auto - Copy\Daily(scanned).pdf	2
23	F:\Test\NEW\auto - Copy\Credit Limit alltypes.pdf	9
24	F:\Test\NEW\auto - Copy\Credit Card History.pdf	7
25	F:\Test\NEW\auto - Copy\Cashier Audit.pdf	20
26	F:\Test\NEW\auto - Copy\AR Settlement.pdf	15
27	F:\Test\NEW\auto - Copy\Adjustment Log.pdf
Temp

This is how i need the numbers to show up on the temp sheet
This is important because when i short the temp list by number small to large it will always list the file locations in the correct order.
I hope that helps any questions please ask

 

[Jake975]

Basically i need to match a cell in column a on Temp sheet to a matching value in Data List column c and return the value(to the right) from Column D to the Temp sheet column B Im defiantly having trouble explaining it but i hope you can understand with the help of the pictures