Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › [SOLVED] Need an Excel 2010 function, formula, or macro
New Posts  All Forums:Forum Nav:

[SOLVED] Need an Excel 2010 function, formula, or macro

post #1 of 16
Thread Starter 
So, I'm kinda stuck. Seeing as I'm at work, my repository (*cough* Internet) is fairly limited.


I need to create dynamic/unique names in a cell or a series of cells using information gathered from other cells

EG:

I need to create this name: NAZTURELXDDE143 using cells that have this information spread across other cells.

NAZTURELXD is standard and will not change. The D is a changing variable using information from a cell that is a string which specifies line of business. The E and 143 are changing variables using a cell which contains both pieces of information that demote cube location.


The whole thing will create PC names for tickets that we have in our queue.

This is something that we will use as a much needed luxury, so it's not at the top of my priorities at this time.

Everything I found was defining a name for a cell, or creating additional sheets. Which is why I come to OCN for help.
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #2 of 16
You could use the command "CONCATENATE" to merge the information across all the cells into one cell.

i have the file containing the list of all the excel formulas along with uses. Will attach it tommo at work
Edited by huzzug - 3/21/13 at 11:35am
post #3 of 16
Thread Starter 
Didn't know about that one. Thanks!


However, it doesn't do what I need. :\


I should further elaborate, the D, or the 11th character, is defined by a cell which has the string "DEFAULT." If that cell in the row had "HOSS," the LOB designator would be S. If the cell has "CCVIC," the designator would be V.

The 12th character is defined by LOB and cube location, and we use the first 8 letters of the alphabet to designate (ABCDEFGH). This one, I know will be tricky, and I have no issues doing this one manually. The site spans 2 buildings, and uses 01A, 01B, 02A, and 02B for the cube locations in both buildings.

Building 9060 is as such; 01A - A, 01B - B, 02A - C, 02B - D.
Building 9072 is as such; 01A - E, 01B - F, 02A - G, 02B - H.

Characters 13-15 are the 3 digit cube number, starting at 001.
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #4 of 16
Quote:
Originally Posted by huzzug View Post

You could use the command "CONCATENATE" to merge the information across all the cells into one cell.

i have the file containing the list of all the excel formulas along with uses. Will attach it tommo at work
Alternatively you can concatenate cells by ampersanding them together (similar to how you would a numerical formula)

eg
Code:
// addition //

A1 [123]
A2 [456]

=A1 + A2 [579]

// concatenation: //

A1 [hello]
A2 [world]

=A1 & A2 [helloworld]


// You can get a little more complicated if you wish: //

=A1 & " " & A2 & "!" [hello world!]
Quote:
Originally Posted by legoman786 View Post

Didn't know about that one. Thanks!


However, it doesn't do what I need. :\


I should further elaborate, the D, or the 11th character, is defined by a cell which has the string "DEFAULT." If that cell in the row had "HOSS," the LOB designator would be S. If the cell has "CCVIC," the designator would be V.

The 12th character is defined by LOB and cube location, and we use the first 8 letters of the alphabet to designate (ABCDEFGH). This one, I know will be tricky, and I have no issues doing this one manually. The site spans 2 buildings, and uses 01A, 01B, 02A, and 02B for the cube locations in both buildings.

Building 9060 is as such; 01A - A, 01B - B, 02A - C, 02B - D.
Building 9072 is as such; 01A - E, 01B - F, 02A - G, 02B - H.

Characters 13-15 are the 3 digit cube number, starting at 001.
All of that can be done, but it sounds like you're making things needlessly complicated (and I'm not just saying that because I'm struggling to grasp the order of all the different elements you're trying to incorporate)
post #5 of 16
Thread Starter 
So, I work in a call center, right? ~2000 computers, too many lines of business to count. They all have their own set of software required for the call agents to use the computers.

The company wants to standardize their computer names worldwide (Parent company is an international bank). I can't change the first 10 characters in the name, NAZTURELXD. They all have purpose for auditing. The name does change, however, when a desktop is requested for the retail cards line of business.

They gave us characters 11-15 for us to use, to create names as we see fit. After staring at spreadsheets all day long, we tend to get a little dyslexic. lol

I know there is need for a logic based function/formula/macro here, but I don't know how to adequately explain it.


So, we create spreadsheets from the ticketing systems for ease of following them. In these spreadsheets, we have to add our own data which specify line of business, department, and cube number. We have to do this by going through each ticket. This data helps us load software onto the desktops as needed.

For example, someone in the DEFAULT department requested a computer, and their cube is 01A-153. That would warrant a name of NAZTURELXDDE153.

If someone in the CCVIC department ordered a desktop at cube location 02B-093, that would warrant a name of NAZTURELXDVH093.

We are going through the character that designates LOB department. We've run into issues of names with transposed letters. :\

I hope I've cleared it up a bit.
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #6 of 16
Quote:
Originally Posted by legoman786 View Post

I hope I've cleared it up a bit.
That does. Massively. Thank you.
Quote:
and their cube is 01A-153. That would warrant a name of NAZTURELXDDE153.
To grab the last 3 characters of text from cell use the following:
Code:
=RIGHT(cell, 3)
Quote:
For example, someone in the DEFAULT ...hat would warrant a name of NAZTURELXDDE153.
If someone in the CCVIC department ordered a desktop ... that would warrant a name of NAZTURELXDVH093.
This is more complicated as you're having a different value depending on the word (ie it's not always the first character). So you'll need a lookup table:
Code:
// vlookup table: //

A1 [DEFAULT] | B1 [D]
A2 [CCVIC]   | B2 [V]


C1 [DEFAULT]
// in this example C1 will be where you store which department they are in.
// but you'll obviously need to change this when you come to adapt this
// code for your spreadsheet

// now we want to return return either D or V using the value of cell C1.
// (which should hold either "DEFAULT" or "CCVIC" - this lookup is
// case sensitive)

C2 [=VLOOKUP(C1, A1:B2, 2, FALSE)]

// breakdown of parameters:
//     #1: which cell to look up
//     #2: the range of cells which cover your lookup table
//     #3: which value to return you want the 2nd column in the table.
//         (note that it's the 2nd relative value, not the column number.
//          so if your table is D5:E99 then your value would still be 2
//          as it's still the next column along in your lookup table)
//     #4: whether to do a lazy match or not
//         (we want the formula to fail on this occasion as a failed
//          match would mean that your data is wrong somewhere else)

You can then concatenate the results with a formula a bit like the following:
Code:
="NAZTURELXD" & VLOOKUP(C1, A1:B2, 2, FALSE) & RIGHT(D1, 3)
(you could use the CONCATENATE function if you prefer, but I personally think using ampersand looks cleaner when reading back the formula. However that's just personal preference)

You'll obviously need to adapt those formula to work with your spreadsheet. but hopefully those examples are enough to help you understand how to construct the formulas for yourself.

Also, the vlookup table can be on a different sheet tab. but you'll obviously need to add in the sheet reference with the formula (eg "sheet2:A1:B2")

[edit]

There is another character in your examples that you've not accounted for (see red letters):
Quote:

For example, someone in the DEFAULT department requested a computer, and their cube is 01A-153. That would warrant a name of NAZTURELXDDE153.

If someone in the CCVIC department ordered a desktop at cube location 02B-093, that would warrant a name of NAZTURELXDVH093.
Hopefully what I've posted is enough for you to work the rest out for yourself, but I'm happy to guide you through a little more if needed.
Edited by Plan9 - 3/22/13 at 9:56am
post #7 of 16
Thread Starter 
That does help immensely.

The character in red is the building and floor reference designator.

As I has stated previously, we are spread across 2 2-floor buildings; 9060 and 9072. Each floor is divided into A and B. EG: 01A, 01B, 02A, and 02B. The 12th character, the one you bolded and made red, helps us with both. Building and floor reference.

9060
  • 1st floor
    • A/B
  • 2nd floor
    • C/D

9072
  • 1st floor
    • E/F
  • 2nd floor
    • G/H


This may require another VLOOKUP table, which is new to me as well. Appreciated. I'm gonna go play with what you have provided me and await advice on how to proceed with the building/floor designator.

EDIT: In the sheet that we have for our tickets, an entire row is dedicated to the cells that state DEFAULT, CCVIC, etc. Excel is saying I can't use C:C for lookup? I'm an idiot. I figured it out.
Edited by legoman786 - 3/22/13 at 10:43am
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #8 of 16
Yeah, sounds like you will need a 2nd vlookup table. But that's not an issue (in terms of the formula).

Good luck mate smile.gif
post #9 of 16
Thread Starter 
So, I got an error in the function for the building designator.

I created a table, and created the formula as it should be.

In the cell, I have 01A-015. It returns #N/A. If I remove the "-015", it returns the correct result. How do I tell VLOOKUP to reference only "01A" in the cell, and not the entire cell?

EDIT: I changed FALSE to TRUE, after reading the MSKB on the function. It returns the correct result. Thanks again!
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
Q8400 DFI LP DK P45-T2RS eVGA GTX 650 Kingston 
Hard DriveOptical DriveOSMonitor
2.8TB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #10 of 16
Quote:
Originally Posted by legoman786 View Post

So, I got an error in the function for the building designator.

I created a table, and created the formula as it should be.

In the cell, I have 01A-015. It returns #N/A. If I remove the "-015", it returns the correct result. How do I tell VLOOKUP to reference only "01A" in the cell, and not the entire cell?

EDIT: I changed FALSE to TRUE, after reading the MSKB on the function. It returns the correct result. Thanks again!

If it were me, I'd change the cell reference to LEFT(cell, 3) to only pick up the first 3 characters and then change FALSE back to TRUE. So your formula would look something like:
=VLOOKUP(LEFT(cell, 3), A1:B3, 2, FALSE)


Personally I don't trust fuzzy matching as there's a theoretical chance it could do an incorrect match (and after a while you'll get complacent thinking the formula works so might not notice an error in the match). By forcing an absolute match then you know that your results will always be correct.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › [SOLVED] Need an Excel 2010 function, formula, or macro