Solved

Help with Excel Formula

Posted on 2013-02-06
11
189 Views
Last Modified: 2013-02-06
We have four columns A-D. Column A and B are Keys. While column C and D are values.

Column A has number sequence of that ranges from 1-100
Example:
1
2
3
4
etc...

Column B has the coordinated text value of Column A's  number sequence.
Example:
ABC
BFG
AZw
etc...

In column C and D have more repetitive similar values from column A.

How do I replace each value in column C and D with the column B's value.
0
Comment
Question by:AIX25
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 38861119
A formula can't replace existing values - you need VBA for that

....but you could use a formula in columns E and F and then copy > paste special  > values over columns C and D, i.e. in E1

=VLOOKUP(C1,$A$1:$B$100,2,0)

copy across to F1 and then down both columns

regards, barry
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38861130
Not sure I follow.

Can you give more examples of what is actually in C and D and how you want to replace them?
0
 
LVL 4

Expert Comment

by:Michal_Zyzak
ID: 38861282
Hi,

I'm not sure I understood the problem.
You want to have same data in each row in column C and D as in column B ?
Like:
1  ABC  ABC  ABC
2  BFG  BFG  BFG
3  AZw  AZw  AZw
?

If yes then simply put =B1 in C1 and D1 mark both cells and doble-click the fill handle
0
 

Author Comment

by:AIX25
ID: 38861316
I have attached a sample. If you look at the key, where ever there is a number sequence that is in Column C or D, reference Column A and replace the value to Column B and put it in Column E. Is this possible?
Book1.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38861378
What are some expected results in column E?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:Michal_Zyzak
ID: 38861386
in Column E:

=concatenate(VLOOKUP(C2;A$2:B$11;2;0);VLOOKUP(D2;A$2:B$11;2;0))

Please just move data in columns C and D one row down so that the formula will not try to catch the merged cell A1
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38861413
Did you try using the method I suggested above? - see attached

regards, barry
vlookup.xlsx
0
 

Author Comment

by:AIX25
ID: 38861477
@harryhoudini, its not working for me as your output looks good. I have attached mine.
Book1.xlsx
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 38861514
You have only put the formula in the top row - you need to "drag" the formula down,

To do that select both cells E1 and F1 together then put cursor on the bottom right of F1 until you see a black "+" the hold down left mouse button and drag the formula down the column

regards, barry
0
 

Author Comment

by:AIX25
ID: 38861578
Ok, it worked. However this was just a sample spreadsheet I attached with sample numbers. My spreadsheet is much more advanced and convoluted. Can you explain the formula so that I can adapt it to my spreadsheet?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38861652
Sure...

In this formula

=VLOOKUP(C1,$A$1:$B$100,2,0)

C1 is the "lookup value" and that is always searched for in the first column of  the "table array" $A$1:$B$100, so the first column is $A$1:$A$100. C1 is searched for in that range and the formula returns a value from the column designated by the "column index" - 2 in the above formula - so that's the second column of $A$1:$B$100, so if C1 matches A14 then the formula returns B14.

The zero at the end (or you can use FALSE) designates an "exact match", so C1 has to exactly match a value in $A$1:$A$100.

If C1 doesn't match any value in $A$1:$A$100 the formula returns #N/A

Excel help also explains VLOOKUP quite well......

regards, barry
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now