• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automating Report

teeprodnuk

teeprodnuk

New Member
Hello everyone!

I am working with Excel more and more these days, and I am not even close to a ninja at this point. But I do have a functional question for you all, if you would be so kind!

I have a spreadsheet, very basic, with multiple columns of data that comprises about 500 rows of data total. I am needing to write a formula/use code to automate part of the report. What needs to happen is I need Excel to return a specified value based on meeting certain criteria in one column, which is why I thought I needed to use an IF formula. The problem I am having is that there are 51 unique values amongst the 500 rows of data (which I found easily enough by removing the duplicates in a separate worksheet), and the values are of a complex nature (i.e. IBF-D-T04), and the portion of that value which corresponds to the data I need it to calculate is the numerical portion at the end. Basically, if the numerical portion of the above value is between a certain range, it needs to display a numerical value, i.e. $500. I cannot change the value (as given in the example above), so any ideas as to how I can accomplish this task and automate the process? Much appreciation! - Tee
 
Hi ,

Isolating the numeric portion is not an issue , as long as it follows a standard format ; if the numeric portion will always be the right-most 2 characters , then you can use the RIGHT function to extract the digits.

Narayan
 
Of course! Here it is!

Bearing in mind that the scale I am trying to match the values in column D to is a simple, 0-4, 5-7 type of scale.
 
NARAYANK991 said:
Hi ,

Isolating the numeric portion is not an issue , as long as it follows a standard format ; if the numeric portion will always be the right-most 2 characters , then you can use the RIGHT function to extract the digits.

Narayan
Click to expand...

Thank you! Unfortunately, the right-most 2 characters aren't ALWAYS the same...in some cases there is a letter where there might be a zero (i.e. T05 instead of 005). In those cases, there is a different scale that corresponds to the letter/number which requires a different value than if it were just the number. Does that make any sense?
 
Hi ,

Even if the numeric portion comprises 3 digits , since the left-most digit is 0 , it should not matter ; we can always look at the right-most 2 digits.

Narayan
 
Unless I'm mistaken, he's saying that the left most digit is not always 0. Sometimes it is a letter, such as TSG-Y-H05, rather than TBD-X-004 etc. (the leftmost digit of the three rightmost digits)
 
NARAYANK991 said:
Hi ,

Even if the numeric portion comprises 3 digits , since the left-most digit is 0 , it should not matter ; we can always look at the right-most 2 digits.

Narayan
Click to expand...

So even if the right-most portion of the data has letters, either at the end or beginning, it should not matter?

I.E. TBD-A-014,
TBD-Y-T07, and
TSX-H-17A?
 
NARAYANK991 said:
Hi ,

In the last example that you have posted ( TSX-H-17A ) what should be returned ? 17 ?

Narayan
Click to expand...

No. In each set of (ABC-D-12A), the number in the last set of three characters is what determines what value needs to be returned. However, each set of characters as a whole determines which value needs to be returned. For example, the set TBD-A-017 would return a value of $500 on one scale, whereas TSX-S-17A would return a value of $2000 on a completely different scale. The first 3 letters basically determine which scale is to be used.
 
Hi ,

Things are still not clear !

If we take the example as : TSX-S-17A , the first 3 characters are TSX ; the last 3 characters are 17A ; the numeric portion in the last 3 characters is 17.

Which of these 3 outputs is to be used , and how ?

Narayan
 
NARAYANK991 said:
Hi ,

Things are still not clear !

If we take the example as : TSX-S-17A , the first 3 characters are TSX ; the last 3 characters are 17A ; the numeric portion in the last 3 characters is 17.

Which of these 3 outputs is to be used , and how ?

Narayan
Click to expand...

Okay, sorry. I know this is very confusing, so let me try to clarify the best I can:

It's a combination of all three outputs. The first 3 characters, TSX, determine which table of values needs to be referenced. (There are two tables involved here.) The middle value, a letter, has no bearing on the desired output. The last 3 characters either have all numerical values, such as 004, or a combination of letter and numbers, such as 17A or H05. The numerical portion of the last 3 characters determines which value on the corresponding table (as determined by the first 3 characters) should be returned.

Does that help?
 
Hi ,

We have come full circle !

I am again asking the same question ; the right-most characters may be any of the following :

1. Three digits , let us represent these by 999

2. Two digits followed by a letter , as in 99X

3. A letter followed by 2 digits , as in X99

All I am asking is , in any of the above combinations are we only interested in :

1. 999

2 and 3. 99

or are we interested in all 3 characters , irrespective of whether they are numeric or they are alpha ?

Narayan
 
NARAYANK991 said:
Hi ,

We have come full circle !

I am again asking the same question ; the right-most characters may be any of the following :

1. Three digits , let us represent these by 999

2. Two digits followed by a letter , as in 99X

3. A letter followed by 2 digits , as in X99

All I am asking is , in any of the above combinations are we only interested in :

1. 999

2 and 3. 99

or are we interested in all 3 characters , irrespective of whether they are numeric or they are alpha ?

Narayan
Click to expand...

I apologize for the confusion!

To answer your question, with regard to the right-most 3 characters, we are only interested in the two digits, or 99 in your example. (In your example, the first 9 in 999 is invariably a 0 and does not matter.)

However, to complicate matters, the first 3 characters of the entire string (i.e. TBD-A-99X), the TBD, is important because it decides if the last 3 characters (the 99X) will return one of two possible values. To explain further: If the string starts with TBD and ends with 99X, the adjacent column should return a value of 100. But if the string starts with TSX and still ends with 99X (the exact same suffix), because it starts with TSX and not TBD, it should return a completely different value - 500.

I hope that makes sense. We may have to consider the entire string of characters (all 7 of them) in order for this to work, I think!
 
You must log in or register to reply here.
Back
Top