Way To Return Column String Values
Feb 11, 2008
I have made this function that returns the string value for a column based on it's number but it starts getting more complicated after I've been thru the alphabet once and have to return two string values to move across the chart.
PHP
Function colnum_string(ByVal num As Integer) As String colnum_string = "IV" If num = 1 Then colnum_string = "A" End If If num = 2 Then colnum_string = "B" End If If num = 3 Then colnum_string = "C" End If If num = 4 Then colnum_string = "D" End If If num = 5 Then colnum_string = "E" End If If num = 6 Then colnum_string = "F" End If If num = 7 Then colnum_string = "G" End If If num = 8 Then colnum_string = "H" End If If num = 9 Then colnum_string = "I" End If If num = 10 Then colnum_string = "J" End If If num = 11 Then colnum_string = "K" End If If num = 12 Then colnum_string = "L" End If If num = 13 Then colnum_string = "M" End If If num = 14 Then colnum_string = "N" End If If num = 15 Then colnum_string = "O" End If If num = 16 Then colnum_string = "P" End If If num = 17 Then colnum_string = "Q" End If If num = 18 Then colnum_string = "R" End If If num = 19 Then colnum_string = "S" End If If num = 20 Then colnum_string = "T" End If If num = 21 Then colnum_string = "U" End If If num = 22 Then colnum_string = "V" End If If num = 23 Then colnum_string = "W" End If If num = 24 Then colnum_string = "X" End If If num = 25 Then colnum_string = "Y" End If If num = 26 Then colnum_string = "Z" End IfEnd Function
Is there a better, more simple way to return the string value for a column from it's number reference?
View 9 Replies
ADVERTISEMENT
Feb 28, 2014
I have a spreadsheet of several thousand named items (in column B) with values associated with them in column A). The "name" field is a string of several alternative names for the item.
I have a list of ~50 items that I am trying to find the values for. Each uses one of the alternate names.
What I want is a function that will return the associated value in column A when one of my shortlist names is found.
For example, my big spreadsheet looks like:
AB
1dallas, DFW. 12345
2Atlanta, ATL, 23456
3Boston, BOS, 34567
4Chicago, CHI, 45678
5Los Angles, LA, LAX, 56789
And my list looks like
Atlanta
CHI
I want the output to look something like:
Atlanta2
CHI 4
Is the feasible? IT seems like there should be a way to do it....
View 2 Replies
View Related
Jun 24, 2013
Formula that will return a string of dates (in values so that I use the cell for additional formula).
For example:
A1 B1 C1:to whatever cell needed, to be autofilled with day, date, month, year, weekdays only.
Start Date 2013/07/04 Monday, July 1, 2013
It would be really nice if it highlights the local holidays too.
View 13 Replies
View Related
Jul 13, 2009
Say I have a table like this:
Value 1---Value 2---Name
1---------1---------Potential client
1---------3---------Loyal client
2---------5---------Happy client
Now I have two separate columns with values for "Value 1" and Value 2". How can I tell Excel to look in the table above and return the name?
View 9 Replies
View Related
Dec 19, 2013
Basically I have a string of dates, I want to get the max of the values that are before a specific date and return the header. Look at the file I provide for better understanding.
View 2 Replies
View Related
Oct 22, 2012
I need to compare a string from say A1 to a range of cells in another workbook and if found return the value of a cell on the same row.
Something like - look up string value in A1 in other workbook.sheet1.A1:A65535 if matched return the value of matched row column b.
OR
IF Otherworkbook.sheet1.A20 = A1 return the value of B20
View 6 Replies
View Related
Jul 13, 2011
I am trying to use ADO via VB6 to find a specific input in row A of my excel sheet.
I then want it to return the Column and Row to me separately.
I have a code I already use for returning specific values from cells:
Code:
Function Look(ClosedWorkbookFullName As String, _
SheetName As String, RangeAddress As String) As Variant
Dim conn As Object, rs As Object, SQL As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
[Code] ......
Now I want something like this I can use as a function, but it obviously needs tweaks.
View 1 Replies
View Related
Feb 13, 2008
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA".
I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
View 3 Replies
View Related
Mar 22, 2007
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.
Col A Col B
20567 3M Electrical, Inc.
I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...
Col A Col B
20567 3M Electrical, Inc.
21789 3M Tape Division
Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
View 9 Replies
View Related
Jan 31, 2008
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
View 9 Replies
View Related
Jan 31, 2014
find attached my spreadsheet. I want the excel to look at j2.. search for it in A coloumn and return its corresponding B coloumn values. There might me multiple values with the same name in A2. I would like the excel to return all the multiple values with their corresponding B coloumn values.
View 3 Replies
View Related
Mar 16, 2014
I want to return the column headings (the months) where there is a 'No' value, per person. How to do this?
Here's the screenshot
Screen Shot 2014-03-17 at 11.45.34 am.png
And here's a dummy version:
Example Spreadsheet.xlsx
View 3 Replies
View Related
Oct 21, 2008
I have my lookup value in column A. The vlookup result in Column B.
=VLOOKUP(A1,ALL!B2:Y10482,2,FALSE)
I now want to drag this across to populate and return values up to column Y.
When I drag the formula or copy it to Column C it does this:
=VLOOKUP(B1,ALL!C2:Z10482,2,FALSE)
The lookup value, table array, and column index number have now changed to column B, C:Z, and 2, when I need it to increment like this:
=VLOOKUP(A1,ALL!B2:Y10482,3,FALSE)
The only thing I want to change as I paste it into the adjacent field is the col_index_number.
Is there a way to do this horizontally without manually changing the formula 26 times?
View 2 Replies
View Related
Jul 20, 2009
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area.
here is an example of my form.
COLA | COLB |
--------------
1 | 324 |
--------------
2 | 325 |
--------------
1 | 327 |
---------------
this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
View 2 Replies
View Related
Jan 31, 2014
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
View 2 Replies
View Related
Mar 20, 2014
I have a two different workbooks book 1 and book 2 with some data.
If values of first two columns ( Column A and B) of both workbooks are matching then I want a formula that can return the value of third column (column c) of book 2 in book 1 column c.
example:
Book 1
column A / Column B / Column C
100 / 200 /
Book 2
Column A / Column B / Column C
100 / 200 / 300
now I want a formula that can return in column C of book 1 to display 300.
And the data runs across some 100 rows in both workbooks.
View 4 Replies
View Related
Nov 20, 2009
I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".
Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom
Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom
I'm sure this is elementary, but I can't seem to figure it out.
View 4 Replies
View Related
Feb 5, 2013
Ok, imagine this data (say column A). I want to have a cell that automatically searches for the least value (will always be the first number from top) and returns it in another cell. And then repeat the same for the second number from the top.
Beg Search STA
-
-
300
-
-
1050
-
-
2200
View 5 Replies
View Related
Sep 20, 2013
I have a challenge which I am trying to solve without VBA and I am uncertain how to approach
If I have the following spreadsheet consisting of three columns:
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
D.........................
E.........................
F........6........4......
The result I would like to achieve is
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
F........6........4......
So, it means I would like to return the rows where Columns B and C have a value. Is this possible with strictly formulas?
View 8 Replies
View Related
Jul 6, 2007
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 3 Replies
View Related
Jan 30, 2008
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies
View Related
Jan 31, 2008
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 Replies
View Related
Sep 29, 2012
I have written many Index/Match and cannot figure out why I cannot create a formula for this.
View 1 Replies
View Related
Mar 13, 2014
I have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.
View 7 Replies
View Related
Jan 20, 2010
I know would like to return column C in the INDI LEG VALUES table if the following occurs
View 4 Replies
View Related
Mar 28, 2008
I have an excel sheet with cols of information e.g IDNo, FirstName,LastName etc. I have created a form in VB where you enter the IDNo (& Click on a command button called View) It should populate designated textboxes on the Form. Thus it should check the col where the IDNo is stored, find the IDNo and populate the adjacent cols i.e FirstName, LastName in designated textboxes on the form.
View 3 Replies
View Related
May 28, 2008
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".
The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).
I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.
Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
View 8 Replies
View Related
Jun 11, 2008
Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b.
We have two worksheets, sheet a and sheet b
compare sheet b, column c (email addresses) to sheet a, column x, (email addresses).
If they match, move sheet a, columns d, e, f, g, and h to sheet b.
View 3 Replies
View Related
Jul 1, 2008
I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.
View 5 Replies
View Related
Dec 26, 2013
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
View 3 Replies
View Related