Solved

Help with Excel Formula

Posted on 2013-02-06
11
192 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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