Solved

Add a new value based on adjacent value

Posted on 2014-12-22
3
62 Views
Last Modified: 2015-01-05
The lookup values are cube numbers produced by checking against other values in the spreadsheet and are part of one of the following 4 groups (A1, A2, A3, A4) where A represents an area and C represents a cube number.

C1-C280 - member of area 4
C282-C448 - member of area 3
C454-C648 - member of area 2
C659-C894 - member of area 1

The lookup values (cube numbers) are in column W, and I need area numbers to fill for column V once a cube number exists in column W.  If a cube number does not yet exist in column W, then I want for nothing to occur in column V.

Assistance is greatly appreciated.
0
Comment
Question by:frugalmule
  • 2
3 Comments
 
LVL 10

Assisted Solution

by:Ray
Ray earned 166 total points
ID: 40513228
Will the values in Column W be a number (such as: 282 or 648 etc) or will it contain a 'C' plus a number?  
And you then want a number (1,2,3 or 4) in Column V (blank if nothing in W)?
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 334 total points
ID: 40513324
I presume that cube numbers are listed as "C###" and you want the area values returned as A1-A4.  Because the area numbers are grouped by sequential sets of cube numbers you can use a lookup function to resolve this.

1) Set up a lookup table (on another sheet, ideally).  In one column you'll have a cube numbers, but only the starting numbers for each area.  In the second column you'll show the appropriate area number.  Like so:
cube lookup
Note that I've also included an "error" result in case a cube value is found exceeding the area definitions.

2) In column V you'll insert a lookup formula like so (note the sheet name where I put the lookup table from the above step is titled "Lookups"
=VLOOKUP(VALUE(MID(W2,2,10)),Lookups!$A$2:$B$6,2,TRUE)

This is looking at the numerical value of the cube (after the "C") and returning the ordered/sorted result in the lookup table.

See the example workbook.  It randomly generates cube numbers so you can see the results change.

Regards,
-Glenn
EE-Q28585336.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 334 total points
ID: 40514323
I just realized that your area definitions have some gaps in the cube numbers, so I've updated the lookup table to handle that.  If a Cube number appears between valid area definitions (ex., C650), the word "Invalid" will display.  See attached revised example file.

-Glenn
EE-Q28585336.xlsx
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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

867 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

18 Experts available now in Live!

Get 1:1 Help Now