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

Identify Part Of String In One Column And Return Entry In Neighboring Column?

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

Return Dates As String In Values Only

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

Look For Two Values And Return A Text String

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

Compare A String Of Values And Return The Header

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

VLookup To Compare Two String Values And Return Value Of A Cell

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

Finding String In Excel Using VB6 ADO - Return Column And Row

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

Search Partial String, Return Value In Next Column, Vba

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

Lookup Partial String To Return Data From Corresponding Column

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

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

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

Formula To Look Up Column And Return Values And Corresponding Value

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

How To Return Column Headers For Where Certain Values Appear In Each Row

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

Populate And Return Values Up To Column

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

Search Column And Return Values

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

Return Column Values Based On Criteria?

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

Return Value In Third Column If Values In First Two Columns Matching?

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

Find Records And Return Values To New Column

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

Search And Return Values From Column In Order?

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

Return Values From Multiple Column If Exist

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

Vlookup A Column To Return Multiple Values

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

Find Values In 2 Columns And Return A Value In A 3rd Column

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

If Text Found In String Return String

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

Look Up Two Values In Same Column And Return A Value From Another Column

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

ID First And Last Non Blank Values In Row And Return That Cells Column Header?

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

CountIf: Return Column C In The INDI LEG VALUES Table

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

Lookup & Return Values Of Adjacent Column To Textbox

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

Lookup Column Values In Another & Return Value If Match Found

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

Lookup Column Values On Another Sheet & Return Adjacent Cells

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

Lookup/Match Column Values On Another Worksheet & Return Adjacent Cells

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

Formula To Find Matching Values In One Column And Return Totals For All Rows That Match

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







Copyrights 2005-15 www.BigResource.com, All rights reserved