Question re: display part of a column on a different sheet:

[auto.pilot]

I initially thought this was a VLOOKUP issue, but I can't figure out how to make it work. On one worksheet, I have a list of Codes and their corresponding Identifiers, listed as shown below:

Excel Workbook
	A	B
1	Code	Identifier
2	AG	124H
3	MF	3132F
4	TA	44R
5	XO	723A
6	MF	2209U
7	ML	350T
8	MF	3456N
Sheet1

On a separate page, I'd like to show only the items with a Code of MF and ML, then their Identifiers in the adjacent column as shown here:

Excel Workbook
	A	B
1	Code	Identifier
2	MF	3132F
3	MF	2209U
4	ML	350T
5	MF	3456N
Sheet2

This initially looked like a vlookup, but the first sheet has over 500 rows and the second only 50 or so. How can I list only the desired values on the second worksheet>?

Thanks in advance for any and all replies.

 

[Cbrine]

I would suggest something a little different.

It looks like your identifer is unqiue...if this is true, then the following will work.

1. Copy all your identifiers to the new sheet.
2. Revised your column structure so that your identifier is first or use an index(Match()) instead.
3. Do a vlookup vs your original data and return your codes.
4. Sort by your codes and delete any codes you don't want.

You will be left with only the identifiers you are interested in.

HTH
Cal

 

[auto.pilot]

Thank you. I like idea, however I was hoping for a more automatic/linked solution as sheet one is often updated- rows added and deleted every month.

jim

 

[Cbrine]

Does it need to be a seperate worksheet? Have you thought about using the autofilter? Not sure of your usage, but this maybe an option.
If Aladin's about, he might be able to come up with a formula to help you, at this point, I would fall back on VBA to execute the process for me. Let me know if vba is a viable option for you.

Cal

 

[auto.pilot]

Thanks for the follow up question. Yes, it must be a separate worksheet, because I need to perform some other calculations, lookups, etc once the data is re-posted on the new sheet. Sheet 2 will need some different spacing as well, which I did not include in the original post (each Code separated by 3 rows).

A VBA solution would be fine.

jim

 

[Cbrine]

Ok,
Here's some code that will parse the data the way you are looking for it.

Private Sub CommandButton1_Click()
Dim wb As Workbook, ws As Worksheet, ps As Worksheet
Dim cell As Range

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Source Sheet")
Set ps = wb.Sheets("Destination Sheet")

For Each cell In ws.Range("A2", ws.Range("A65535").End(xlUp))
    If cell = "MF" Then
        ps.Range("A65535").End(xlUp).Offset(4, 0) = cell
        ps.Range("A65535").End(xlUp).Offset(0, 1) = cell.Offset(0, 1)
    End If
Next cell

End Sub

HTH
Cal

 

[auto.pilot]

Thanks kindly for your follow up: I am heading out for the day, and will take a look at this either this evening or tomorrow.

jim