finding data in an other worksheet

[dwc]

Hello,

I have to do following task:
I have 2 Spreadsheets open; Cip Numbers and Master. In Clip Numbers I have only the clip numbers of my videos, in Master I have clipnumber plus the full description.
Currently, my workflow looks like this: Both sheets open, I go to one cell (clip number) copy, then highlight the column A in Master Ctrl F and paste the copied number in the Box of Find.
When it is found, I copy the data into Clip Numbers sheet.
This is a rather clumsy way to do that. I don't need the data copied into the clipnumbers sheet, I just need to quick find it in the mastersheet. Because the data there is sometimes in different columns. Before I did not need to switch between sheets, it was just looking in every sheet open, but I don't remember how I did it.

For any advice, thank you in advance!

Dorian

[vba_php]

more than likely, you can use every single one of these coding statements to do exactly what you need to do:

SHEET
https://docs.microsoft.com/en-us/off...xcel.worksheet

FOCUS
https://docs.microsoft.com/en-us/off...tivate(method)

FIND
https://docs.microsoft.com/en-us/off...cel.range.find

COPY
https://docs.microsoft.com/en-us/off...cel.range.copy

PASTE
https://docs.microsoft.com/en-us/off...e.pastespecial

[Norie]

Are you only trying to find out if the clip numbers on the Clip Number sheets exist on the Master sheet?

[dwc]

Yes, and of course the row with the data in it

[millz]

When it is found, I copy the data into Clip Numbers sheet.

You can use VLookup formula, e.g. in column B of Clip number

=Vlookup(A1,Master!A:B,2,0)

This will give you the data in column B when there's a matching index found in column A.

[dwc]

You can use VLookup formula, e.g. in column B of Clip number

=Vlookup(A1,Master!A:B,2,0)

This will give you the data in column B when there's a matching index found in column A.

I am trying to figure out what is wrong: lets say, I copy the formula in B:43 in Clip Number, it copies the cell B1 of the Clip Number sheet

Does it matter: the Clip Number has an extension.xlsm and the master an extension .cvs?

[Norie]

You could use MATCH for that.

[millz]

That's for inserting into cell B1. If your data starts from B43, then change "A1" to "A43". Or better yet, attach a sample workbook so people can understand your problem better and give a better solution.

[dwc]

here is a sample of 2 worksheets. As I mention, the matching clipnumber in "Master" could be anywhere from A1-A40000

the actual files would be: Clipnumber.xlsm and Master.csv

[dwc]

I think, I have to add something additional: All I need to see is if in "Master" is the same number as I select in "Clipnumbers" and where, so I can copy paste the data into the worksheet "Clipnumbers"

[millz]

Here I combined both workbooks into 1 workbook with 2 different sheets, so the formula will remain correct as I attach them here.

in Column B, it can find a matching row's data (of any column of your choice)
in Column C, it shows the row number where the match is found, as suggested by Norie
if you see #N/A, it means no matches were found

[dwc]

Thank you millz. But it would be very difficult to copy the data of workbook 2 into the workbook 1 since 2 has about 40.000 entries. Also the data of master is not always in column B, sometimes C or D. Therefore, all I would need is a quick find of a matching clipnumber, the same way if you use find next and it highlights the matching number

[millz]

Also the data of master is not always in column B, sometimes C or D

Right, so this info is conflicting with what you mentioned in the first post, as you said what you do manually is only searching in column A.
So what do you do after you find a matching clip number? Do you input any info in the clip number sheet? or Master sheet?
If so, show a sample sheet with the results you're expecting when there's matches or no matches. It would be weird for macro to do nothing after finding or not finding any matches.

[dwc]

this is what I posted initially:
When it is found, I copy the data into Clip Numbers sheet.
Which means I copy/paste the data in the same row beside the number.
To say it simple: I have one workbook"Clipnumbers" with -lets say 100 entries. Those entries are numbers of video clips. There is no data other than the numbers. I need the data (like place name, subject name etc.) That I have in the workbook "Master" But there are 40.000 rows. I need to find those video clip numbers one at the time. Because when I find the matching number with data (among the 40.000) I will copy/paste it into the row of "Clipnumbers" The way I do it now is quite time consuming. Unfortunately Data in "Master" is not always in the same column. There might be an automated way, that when I put in Column B of Workbook "Clipnumbers" the formula you created before, that it looks in the Workbook "Master" from A1- A40.000 for a match and hilight it in Workbook "Master". Is that possible?

[millz]

Is that possible?

Short answer, yes, and quite easily.

Again, you have to show a proper sample 'Master' workbook, preferably with rows/examples showing that the matching data are in different columns. And if there are headers, you should show them as well, that will aid macro in knowing which data (place/subject) to grab when it finds a match. Finally, show your 'Clip Number' workbook, with the expected results.

[dwc]

I am sorry, I did not want to confuse you! Honestly, I did not realize how many options are in Excel. I will be more detailed in the future.
I hope, my revised version is more clear.

[millz]

This is assuming your Master workbook will only have one column of info to the right of column A, based on the sample you have given.

Open only the clipnumbers file, which contains macro, and you can run it directly; or click the button I have added if you have no idea how to run macros. Please look up on how to run it if you don't know how. Once you run the macro, it will ask you to browse to your Master workbook.

Matches found will take the info directly from Master workbook, non-matches will have the cell highlighted in red.

Sub EF1333410()
    Dim i As Long, r As Range, s As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    s = Application.GetOpenFilename("Excel Files,*.xls*", 1, "Select 'Master' Workbook")
    If s = "False" Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set ws1 = ActiveSheet
    With Workbooks.Open(s)
        Set ws2 = .ActiveSheet
        
        With ws1
            
            For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
                If .Cells(i, 1).Value <> "" Then
                    Set r = ws2.Cells.Find(.Cells(i, 1).Value, lookat:=xlWhole)
                    If Not r Is Nothing Then
                        With .Cells(i, 4)
                            .Interior.ColorIndex = 0
                            .Value = r.End(xlToRight).Value
                        End With
                    Else
                        .Cells(i, 4).Interior.ColorIndex = 3
                    End If
                End If
            Next
        End With
    
        Set ws2 = Nothing
        .Close False
    End With
    Set ws1 = Nothing
    
    
    Application.ScreenUpdating = True
End Sub

[dwc]

WOW! millz,
This is fantastic! have never thought so far. I think, I will reformat all data to one column.
Edit: I noticed, that is not needed at all.
Many thanks for your help! That changes my total workflow.

[FDibbins]

WOW! millz,
This is fantastic! have never thought so far. I think, I will reformat all data to one column.
Edit: I noticed, that is not needed at all.
Many thanks for your help! That changes my total workflow.

Based on this comment, Im going to assume that your negative rep for Millz was in error?
I have reversed that rep, please confirm that this was the action you indended?

[dwc]

I am very sorry, I must have hit the wrong button. Of course I wanted to be a positive rep. Thank you for reversing it.

[dwc]

I am very sorry, I must have hit the wrong button. Of course I wanted it to be a positive rep. Thank you for reversing it.

[FDibbins]

I am very sorry, I must have hit the wrong button. Of course I wanted it to be a positive rep. Thank you for reversing it.

Thanks for the feedback. Mistakes happen, no problem

[kvsrinivasamurthy]

In D2 of clipnumbers then copy down, provided both workbooks are in same folder.

=IFERROR(VLOOKUP($A2,[Master.xlsx]Sheet1!$A$1:$E$62,5,0),"")

[dwc]

Thank you kvsrinivasamurthy! That formula is an easy one. A just to pull down version. I can make use of both very well.
I am glad to have both versions.

[kvsrinivasamurthy]

Thanks for feed back.

[dwc]

Thank you for your understanding! The members of your forum had been really very helpful to me with their formulas and ideas!