Thread: Pulling Information in seperate sheet based on data in current sheet

Hello all!

I've got a bit of a quandary here that my novice programming skills can't seem to solve.

Here is the scenario. I work for a photographic library. We have a spreadsheet that contains 5 or so columns, and a couple of thousand rows. Essentially each row contains an imagine code (the number we use to file away the imagine, ie. US_NY_NYC_1 ) in the first cell, and the image information in the following cells (ie metadata, keywords, photographer, date, etc).

so a typical row would look as follows:

Filename | Keywords | Photographer | Metadata | Location |

all the keyword, photographer, metadata, and location information can only be tied to the one image code whose cell begins the row.

Now, here is my dilemma. This sheet (lets call it the master sheet) contains all the images we have in a given batch. However, when a client orders images, we will send them most images in that batch, but not all, and we may send a few additional. So I have a separate sheet that contains only those image codes that the client requested. What I need to do is essentially take a code from the second sheet, find that same code in the master sheet, and copy over all the cells associated with that code's row. If the number does not exist in the master sheet, simply leave that row with only the code in the first cell, and the rest blank, and move on.

so for example, in my second sheet lets say I have the following code: A_B_C . I would like for excel to find that same code in the master sheet, and then copy over the metadata, photographer, date, etc, associated with that code, and paste it into the second sheet, so that that row in the second sheet now looks identical to the row with the same image code in the master sheet . If A_B_C does not exist in the master spreadsheet, then I would like excel to simply leave that code in its own row, leave the rest of the row blank, and move on to the next code.

My apologies that this is a bit wordy, but I am really in a bind here, and appreciate the help greatly. Thank you kindly!

It sounds eminently possible, but it would be helpful for anyone here if you could attach a small workbook illustrating a sample of your data as is, and your desired results.

Hello Stephen,

Thank you for the prompt reply. I've created a sample of the sort of data I am looking at:

samplemaster.xls is a sample of what the master sheet might look like (but obviously much truncated in the row number)

samplesecond.xls is a sample of the second sheet that contains only the file numbers I am trying to sniff out.


So given this example, in samplesecond.xls, one of the numbers is "ANG_E4_2" which also exists in sample master. I would like to copy the remaining data from that row (row 8) in samplemaster.xls (namely: the "Caption" "CreditLine", "Model Release", and "Keywords" column) to the row in samplesecond.xls to make it look identical in data to the equivalent row in samplemaster.xls

You could use VLOOKUP for this. Here is some code for you try if you wanted to automate it. It goes in the samplesecond file.

Code:

Sub x()

Dim rng As Range, wb As Workbook, rng2 As Range

Set wb = Workbooks("samplemaster.xls")
Set rng2 = wb.Sheets(1).Range("B2", wb.Sheets(1).Range("F" & Rows.Count).End(xlUp))
Sheet1.Activate
For Each rng In Range("A1", Range("A1").End(xlDown))
    rng.Offset(, 1).Resize(, 4).Formula = _
        "=VLOOKUP(" & rng.Address & ",[samplemaster.xls]Sheet1!" & rng2.Address & ",COLUMN(),FALSE)"
Next rng
Range("B:F").SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents

End Sub

Stephen, thank you, that works brilliantly.

I was wondering, is there any way to adapt the script to dynamically change the source and destination? more specifically, to use "sheet 1" (the first tab at the bottom of a workbook) of the current workbook as the "Master Sheet" and use "Sheet 2" (the second tab) of the same workbook as the new list with the codes to be appended to? In this way, I can pass this script around the office as a macro, and have them use it on whatever batch they are currently working on.


Thank you again, Stephen, very much appreciated.

Try this:

Code:

Sub x()

Dim rng As Range, rng2 As Range

Set rng2 = Sheet2.Range("B2", Sheet2.Range("F" & Rows.Count).End(xlUp))
Sheet1.Activate
For Each rng In Range("A1", Range("A1").End(xlDown))
    rng.Offset(, 1).Resize(, 4).Formula = _
        "=VLOOKUP(" & rng.Address & ",Sheet2!" & rng2.Address & ",COLUMN(),FALSE)"
Next rng
With Range("B1", Range("B1").End(xlDown)).Resize(, 5)
    .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
    ' Uncomment line below if you want to replace formulae with values
    ' .Value = .Value
End With

End Sub

I got the two sheets the wrong way round, but also you changed the layout of your sheet1 data.

Code:

Sub x()

Dim rng As Range, rng2 As Range

Application.ScreenUpdating = False
Set rng2 = Sheet1.Range("A2").CurrentRegion
Sheet2.Activate
For Each rng In Range("A1", Range("A1").End(xlDown))
    rng.Offset(, 1).Resize(, rng2.Columns.Count - 1).Formula = _
        "=VLOOKUP(" & rng.Address & ",Sheet1!" & rng2.Address & ",COLUMN(),FALSE)"
Next rng
With Range("B1", Range("B1").End(xlDown)).Resize(, rng2.Columns.Count)
    .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
    ' Uncomment line below if you want to replace formulae with values
    ' .Value = .Value
End With
Application.ScreenUpdating = True

End Sub

Hmm.. Perhaps I'm simply running the code incorrectly, but I'm still having no luck. It no longer removes those columns, but it doesn't seem to do anything now. Thank you, Stephen, for your patience, by the way.

I think that's because none of the items in sheet2 occur in sheet1! EDIT: have just noticed the sheet2 names have the jpg extension, but sheet1 does not. If that is how things are, the the code will need amending.

A Ha! Indeed, the error exists between the keyboard and the chair...

Let me go find some data that actually works....

Alrighty, I grabbed some of the filenames from Column 1 in sheet one and used those as the data for sheet 2. Given that there are no .jpg extensions, it all seems to be peachy!

Will continue testing further.

It works beautifully. Thanks Stephen. Can't tell you how much time you've saved us!