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

auto.pilot

Well-known Member
Joined
Sep 27, 2007
Messages
734
Office Version
  1. 365
Platform
  1. Windows
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
AB
1CodeIdentifier
2AG124H
3MF3132F
4TA44R
5XO723A
6MF2209U
7ML350T
8MF3456N
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
AB
1CodeIdentifier
2MF3132F
3MF2209U
4ML350T
5MF3456N
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Ok,
Here's some code that will parse the data the way you are looking for it.

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top