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.

 

[Peter_SSs]

av3ng3r85

Welcome to the Mr Excel board!

You may need to clarify this a bit more.

I run a report that ...

Can you detail this procedure?

... when I paste my report in

Where does the report come from? How is it pasted (manual/Excel macro/something else)?

there are 2 values for each name so I need it to always resolve to the right name

Do you mean you need to replace AA1 and/or AB1 with Bailey etc?

By the way have you explored using Colo's HTML Maker for showing your sheet sample on this board? It's easier for us than tracking to another site. It also allows us to copy the sample and/or formulas from your sample to our sheets. Here's how to do it:
http://www.mrexcel.com/board2/viewtopic.php?t=92622

 

[av3ng3r85]

The report runs a macro in another program that dumps information into a CSV file.The CSV file looks like this:

Book1.xls
	A	B	C	D
1	AA1	103.6	800	68%
2	AB2	91.2	1490	50%
3	AA3	76.5	4600	22%
4	JA1	56.4	1000	85%
5	JB2	66.0	7900	11%
6	JB3	73.3	10300	12%
7	MB1	91.9	21200	5%
8	MA2	86.1	22700	1%
9	MB3	92.2	1640	32%
Sheet2

I paste that information in the appropriate places in a separate xls file that looks like this.

Book1.xls
	A	B	C	D	E	F
1			Groups	Value1	Value2	Value3
2		Region1
3
4
5
6
7		Region2
8
9
10
11
12		Region3
13
14
15
16
17
Sheet1

When I paste the values in I want some way for them to be able to be changed from AA1 or MB3 to human readable names like Bailey or Sanders.Thanks for your prompt response.If you need any more clarification, please just ask.

 

[av3ng3r85]

More clarification...

The intermediate product looks like this,after I've pasted the data from the csv file into the appropriate spots on the xls file.(I had to move some columns around so it doesn't look exactly like what I posted last time.)

Book1.xls
	A	B	C	D	E
1			Value1	Value2	Value3
2		Region1
3		AA1	103.6	800	68%
4		AB2	91.2	1490	50%
5		AA3	76.5	4600	22%
6
7		Region2
8		JA1	56.4	1000	85%
9		JB2	66.0	7900	11%
10		JB3	73.3	10300	12%
11
12		Region3
13		MB1	91.9	21200	5%
14		MA2	86.1	22700	1%
15		MB3	92.2	1640	32%
Sheet1

And this is what I want the finished product to look like.Normally to achieve this result I have to manually type in the names but I would like a way to automatically convert the alpahanumeric group values to actual names.

Book1.xls
	A	B	C	D	E
1			Value1	Value2	Value3
2		Region1
3		Bailey	103.6	800	68%
4		Johnson	91.2	1490	50%
5		Madison	76.5	4600	22%
6
7		Region2
8		Parker	56.4	1000	85%
9		Evans	66.0	7900	11%
10		Metz	73.3	10300	12%
11
12		Region3
13		Hill	91.9	21200	5%
14		Reynolds	86.1	22700	1%
15		Sanders	92.2	1640	32%
Sheet3

Hope this further clears up some things.

 

[jindon]

av3ng3r85,

How do we know;

AA1 = Bailey
AB2 = Johnson

etc.

 

[larrydunn]

First put your lookup data in a sheet called Data.
Start at cells A1 and B1:
AA1 Bailey
AB1 Johnson
etc.

Then, in cell A3 type this formula and copy down:
=VLOOKUP(B3,Data!$A$1:$B$500,2,FALSE)
This should produce a list of the desired names.

Then just copy column A values to column B, and
Edit - Paste Special - Values to remove the formulas.

 

[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

In response to jindon, this is the list that I derive the group codes to name associations from.

larrydunn,this is a awesome start. However when there is no value in the B2 column it displays N/A in the A column. Is there a way to just display a blank when there is no value in the B column. Let me see if I can show you what I mean.

Book1.xls
	A	B	C	D	E
2		Region1
3	#N/A		103.6	800	68%
4	Johnson	AB2	91.2	1490	50%
5	Madison	AA3	76.5	4600	22%
6
7		Region2
8	Parker	JA1	56.4	1000	85%
9	Evans	JB2	66.0	7900	11%
10	Metz	JB3	73.3	10300	12%
11
12		Region3
13	Hill	MB1	91.9	21200	5%
14	Reynolds	MA2	86.1	22700	1%
15	Sanders	MB3	92.2	1640	32%
Sheet1

Also, is there a way to lock and hide the sheet called Data,to prevent tampering and make the report look cleaner.Thanks.

 

[jindon]

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

Are they in one column?

 

[av3ng3r85]

This is the Data sheet where the group codes and names are associated.

Book1.xls
	A	B	C	D	E
1	AA1	Bailey
2	AB1	Bailey
3	AA2	Johnson
4	AB2	Johnson
5	AA3	Madison
6	AB3	Madison
7	JA1	Parker
8	JB1	Parker
9	JA2	Evans
10	JB2	Evans
11	JA3	Metz
12	JB3	Metz
13	MA1	Hill
14	MB1	Hill
15	MA2	Reynolds
16	MB2	Reynolds
17	MA3	Sanders
18	MB3	Sanders
Data

 

[jindon]

OK
here's a vba code, if you like...

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