Substituting Values from a list

[av3ng3r85]

Master List:

AA1/AB1-Bailey
AA2/AB2-Johnson
AA3/AB3-Madison

JA1/JB1-Parker
JA2/JB2-Evans
JA3/JB3-Metz

MA1/MB1-Hill
MA2/MB2-Reynolds
MA3/MB3-Sanders

I run a report that generates a list like this:

I need a way to convert the AA1,AA2,etc. in column B1 to the names they represent, when I paste my report in. The values in B1 vary each time the report is ran and there are 2 values for each name so I need it to always resolve to the right name.If anybody has any ideas,like a macro or formula,it would be greatly appreciated.

 

[av3ng3r85]

jindon, I was just thinking about asking if there was some kind of macro or vba code. However I know you can probably tell already but I am a bit of an Excel newbie and I don't know programming too well either but I can follow directions.I'm not sure how to make this VBA code execute on my spreadsheet. When I attempted to,using a tutorial I found on the internet,it gave me a "compile error:invalid or unqualified reference".Could you sort of give me a crash course on how to install this in Excel?Thanks.

 

[jindon]

OK
1) Hit Alt + F11 to open VB Editor
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 to get back to Excel
4) hit Alt + F8 and choose "test" from the dialog then hit "Run"

 

[av3ng3r85]

OK I followed those instruction but like before it gave me the invalid error message with a yellow arrow pointing to "Sub test()" and ".Range" highlighted on line 12 in the "Book1.xls - Module1 (Code)" window.Please help.Thanks.Let me know if I need to send screenshots or maybe we can use MSN IM.

 

[jindon]

Woops

Can you just add . (peirod) in front of Range...

a = .Range("a1",.Range("a" & Rows.Count).End(xlUp)).Resize(,2).Value


previous code will be edited right after this post...

 

[av3ng3r85]

I added a period in front of the "a" on line 6 but it compiles to the same error. Here's what I have so far:

Sub test()
Dim a, i As Long, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
With Sheets("data")
    a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 2).Value
    .Visible = xlVeryHidden
End With
For i = 1 To UBound(a, 1)
    If Not IsEmpty(a(i, 1)) And Not dic.exists(a(i, 1)) Then dic.Add a(i, 1), a(i, 2)
Next
With Sheets("sheet1").Range("b2", .Range("b" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a, 1)
        If dic.exists(a(i, 1)) Then a(i, 1) = dic(a(i, 1))
    Next
    .Value = a
End With
Set dic = Nothing
End Sub

 

[av3ng3r85]

I removed the period in front of range on line 12 and it looks like it's working now. The group codes changed to names and the Data sheet disappeared instantly. Excellent. I will do further testing and see what I come up with.Thanks a million.

 

[larrydunn]

Dear AV+

I'm scratching my head just a little bit here. Usually I'm the first to rush into VBA code, but I think you might only need the VLOOKUP in this case.

What's bothering me is that N/A result. Doesn't that simply mean that you have no entry in your table for that particular code? You then can type in the missing result in your report, as before. Or you can add it to the table, in which case the N/A will go away.

Surely I must be missing something. Since you are generating a report, you should be expecting to supply those final details.

In any case, the following change should make the N/A be replaced by a blank cell.

=IF(VLOOKUP(A3,Data!$A$1:$B$500,2,FALSE) = "","",VLOOKUP(A3,Data!$A$1:$B$500,2,FALSE))

Personally, I would prefer to leave the N/A result as a reminder that I have not provided all the data for the report to run successfully.

 

[larrydunn]

OOPS and OOPS again!

In my original attempt, I put the VLOOKUP in column A and had you copy
the result afterward to the B column. That's not a viable solution, as you
would curse my name every time you had to do the copy and special paste.

The solution is to put the AB1 code in column A and the VLOOKUP in column B.

That's the method referred to by my previous post.

So if you decide to try the formula method again, you must do these hings:

Transfer the AB1 codes to column A.
Put the revised formula in column B.

If you wish to have the codes not show, you can afterward move them to a column outside the print area, or simply hide the column where they appear.

 

[av3ng3r85]

Thanks larrydunn.I do appreciate your suggestion and all the related research you put in to it, however jindon's VBA script works great and it fulfills all my requirements.Thanks again though.If I have any other excel questions I will certainly post in this board because everybody who replied was most helpful. Later.

 

[jindon]

I removed the period in front of range on line 12 and it looks like it's working now. The group codes changed to names and the Data sheet disappeared instantly. Excellent. I will do further testing and see what I come up with.Thanks a million.

Yep, you are right.
Sorry for that.