Find First/Last Value In Row & Return Corresponding Description Above/Below

Apr 8, 2008

I'm trying to find

1) the last and

2) first values in a time series of data in which the the data points are scattered all over the place

AND I'd also like to

3) get the corresponding year ( Heading name) for both the

3.1) last and the

3.2) first data point

I've found solutions for all but 3.2. Thus the question remains:

How could I lookup the heading description for the first value on a row?

example:

row #; lookup; year1; year2; year3; year4;
1; year3; ; ; 23; ;
2; year2; ; 21; ; 53;
3; year1; 12; ; 45; 2;

In case it helps anyone looking for same information or providing help to solve my problem 3.2 the other solutions I've found are:

1) lookup last value in row

i) Find Last Number In Row & Return Heading
=LOOKUP(9.99999999999999E+307,R5:AI5)
... also an additional VB solution

ii) Find Last Number In Row & Return Heading
=INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))
... page also includes solution for 2), below

iii) Find Last Number In Row & Return Heading
... 2 VB solutions

2) Lookup first value in row
Find Last Number In Row & Return Heading

=INDEX(M12:500,MATCH(TRUE,INDEX(M12:M500<>0,0,0),0))
... page also includes solution for 1), above

and

3) Lookup heading of last populated cell in row
Find Last Number In Row & Return Heading

=LOOKUP(2,1/(I9:CN9<>""),I$5:CN$5)

4) Could I e.g. modify the above to lookup the year of first value?

View 4 Replies


ADVERTISEMENT

Formula: If Value Return Is = To Value Of Cell Above Then Find Return Next Value

Feb 17, 2010

Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?

View 6 Replies View Related

How To Extract Qty Min/Max From Description

Jan 31, 2009

Example: Table ...

View 9 Replies View Related

How To Combine The Description In One Line

Jun 17, 2014

Attached is a file containing stock codes and description. The stock code is repeated but i would like to have one line description per stock code.

View 4 Replies View Related

Lookup In A Description (color)

Mar 13, 2009

I have a column with a description that includes color in the description, I want to sort by color with breaking up the description, is there a formula that will look at the description such as "Am Cannoe black tecan" and in the cel next to it come back with "black". Then I would be able to sort by color column, otherwise I would have to text to column and break up the description, etc,,, ect....

So column a1 has the description, I want a formula that will put in column b1 "black" I have 3 colors, so I can do one at a time or add to same formula.....

View 6 Replies View Related

Sum Turnover Where Name Is PART Of The Description

May 14, 2009

I am looking for a way to use the sum.if formula, combined with the find.spec (I am not sure if this is the correct formule, at the moment I only have the Dutch Excel version, it is meant to find a word as part of a cell)

The part I am struggling with is how to find the correct cells to "sum" when the criterium is part of the cell contents. e.g. Paul is the description in am looking for in "Paul is riding his bike" If so, then add the turnover to Paul. I have made a demo to clarify what I mean.

View 4 Replies View Related

Split Description Into 3 Columns

Dec 9, 2009

I need help to split text (shown below) into 3 columns, now all text is in one cell.

e.g.,

HIPP 1 PLUS PROBIOTIK 600 G MILCHNAHRUNG PULVER 1607000286

I have over 30,000 rows of data which contains different number of spaces within a cell. Please see attached file.

View 14 Replies View Related

Pull Down Option Description

Jul 29, 2009

I have created a pull down menu but cannot figure out how to add descriptions with them. The items in the pull down menu are codes instaed of making people look up their definition in a printed out table,I am hoping to figure out how to make it so that as people scroll through the pull down menu a corresponding definition for the "code" listed appears.

View 9 Replies View Related

How To Display Description Of Options (list)

Feb 24, 2014

Any way to display the description of options (list) which in this case is a numbering code (like ECG201) in a data validation cell.

The length of the cell is not long enough for the users to know the appropriate description of the selected lists. I am now thinking there should be a way to display the description of each item so that users will know that ECG201 means "International College of Excellence", for example, instead of having to go from one worksheet (area) to another to know what an option means.

View 1 Replies View Related

Description Column In Pivot Table

Aug 10, 2009

I often use pivot tables to summarize accounting data. I wish to summarize the data by account number, but also wish to display the account description next to each account number. Both the account number and account description are separate columns in my original table of data.

I've always managed to do this by the use of lookup formulas after the formation of the pivot table in a column outside the pivot table, but it would be preferable to have those descriptions as part of the table.

If I designate both the account number and account description as row labels, they land on two different lines.

View 7 Replies View Related

Excel - Formula Map With The Description Of Another Sheet

Jul 3, 2014

I'm trying to map the cases present in the sheet 1 to Sheet 2. Here the sheet2 I have highlighted the rows yellow color that needs to be updated by using excel formulas.Here the sheet should be updated with the description below mentioned along with the formulas..Highlighted cells in the sheet2 is B,C,I,J,T,U. I have designed the below condition in the same order

B cells should be updated with the reference of Sheet1 with the below condition:

Identify the "(B Value)"Claim with below condition (D Value)

C cells should be updated with the reference of Sheet1 with the below condition:

Verify whether (I Value) Mapped to the below Coverage in CAS (K value)

C cells should be updated with the reference of Sheet1 with the below condition:

Verify whether the Incident (Q value)is below for the Coverage (K Value)

J cells should be updated with the reference of Sheet1 with the below condition:

Verify the the Exposure type(P) is below for the Coverage (K)

T cells should be updated with the reference of Sheet1 with the below condition:

Verify the cost created the reserve coverage (K value) is below (N value)

U cells should be updated with the reference of Sheet1 with the below condition:

verify the line category of the payment done on the coverage

(X value of all the conditions for Sheet1 value)

View 1 Replies View Related

User Defined Function Description

Nov 25, 2008

I have created a custom add-in with custom functions and am trying to make it user friendly for other users. By running the following macro I can add functions to different categories, but I was wondering if there was a way to do this automatically so that anyone that adds this add-in will have the functions show up in the proper category without having to manually run this macro.

View 6 Replies View Related

Insert Columns Based On Tab Description

Apr 14, 2009

I have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.

View 4 Replies View Related

Multiple Sums Depending On A Description

Dec 28, 2005

I really don't know how easy or difficult it might be, but here it is:

I have something like this (example) :

--------------------------------------
| A | B | C | D |
--------------------------------------
1 | 15| W1 | | |
2 | 5| W2 | | |
3 | 7| W1 | | |
4 | 9| W3 | | |
5 | 21| W4 | | |
6 | 14| W2 | | |
--------------------------------------

I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)

View 9 Replies View Related

Excel Match Text In Description

Sep 2, 2013

I have about 70K rows of data. This is a pull from a CRM system and one of the columns contains Free text which may or may not contain a reference ID.

So basically, I need to be able to identify which (if any) of the 60 or so Reference ID appears somewhere in the text. It may be at the beginning, somewhere in the middle or at the end.

So i created two formulas:

=ISNUMBER(SEARCH("MEBIG3",$P:$P))

I created 60 or so of these columns, all with a different Reference ID (MEBIG3 being the ID in this instance). I put the Reference ID in the Cell 1 of each of the columns. I then got a True in the cells wherever I matched the Ref ID to in the description field.

I then created another column with this:

=IF(AO4=TRUE,$AO$1,IF(AP4=TRUE,$AP$1,IF(AQ4=TRUE,$AQ$1,IF(AR4=TRUE,$AR$1,IF(AS4=TRUE,$AS$1,
IF(AT4=TRUE,$AT$1,IF(AU4=TRUE,$AU$1,IF($AV$2=TRUE,AV3,IF($AW$2=TRUE,$AW$1,IF(AX4=TRUE,$AX$1,
IF(AY4=TRUE,$AY$1,IF(AZ4=TRUE,$AZ$1,IF(BA4=TRUE,$BA$1,IF(BB4=TRUE,$BB$1,IF(BC4=TRUE,$BC$1,
IF(BD4=TRUE,$BD$1,IF(BE4=TRUE,$BE$1,IF(BF4=TRUE,$BF$1,IF(BG4=TRUE,$BG$1,IF(BH4=TRUE,$BH$1,

[Code] ........

So wherever there was a True, it returned the Cell at the top of the column which has the Ref ID in it. I put a pivot table and it worked, but the problem is that Excel just can't really handle the number of calculations. The file keeps crashing and becoming corrupt (There are 70K rows, times 60 or so columns). Also I have actually run out of columns I can add in the nesting, I really need more.

Any way of doing the whole piece of analysis all in one Cell. So basically it says "If ADM1 appears anywhere in Column P return ADM1, If ADM2 appears anywhere in Column P return ADM2 and so on".

View 8 Replies View Related

Import CSV With Commas In Description Field?

Dec 2, 2013

One of the fields I am importing has got a description with commas in. When I import the description is wrongly split. How can I fix that? KS14411011,22490030,"Airconditioner,Wall",5512

Code:
Private Sub cmdImportFile_Click()
Dim ImpRng As Range
Dim Filename As String

[Code].....

View 2 Replies View Related

Looking Up Data And Match The Description And Then Insert The Value

Jan 21, 2008

I have a table I down load from SAP with the following headers;
Route, Description, DChl, YF21 (SD),YF31 (SD),YF21 (MM),YF31 (MM),YF10 (MM),YF11 (MM),YF12 (MM).

All I need to do is look up and match the Description and then insert the value (they are percentages) found under the columes YF21 onwards.

I have tried VLOOKUP until I am blue in the face and cannot get it to work.

View 9 Replies View Related

Insert Row & Description On Each Color Change

Aug 24, 2008

I have a list of data in column A. This is grouped by a set of key information and then subsets. The beginning of the key data is signified by the cell colour blue and then the subsets signified by the colour yellow. I want to insert a row just below the colour blue and enter the value " Header" and then when each colour is yellow, insert a row and enter "Subset 1" for the first and then "Subset 2" for the second etc.. The number of subsets can vary.

ABC Customer (Colour Blue)
Info
Info
Product 1 (Yellow)
Info
Product 2 (Yellow)
Info
Info
Product 3 (Yellow)
Info
XYZ Customer (Colour Blue)
Info
Product 1 (Yellow)
Info
Info
Product 2 (Yellow)
Info

Should be:..................

View 5 Replies View Related

Find And Return The Value?

Feb 17, 2014

I need find the date 1/1/2014 in Row A and from the same colum get the average from row B:z.

View 1 Replies View Related

Macro To Assign Task Description To New Sheet?

Jan 8, 2013

User inputs cost in a cell then clicks macro button that will run a macro that ask for a task description. The user inputs the task info that relates to the cost (i.e. weld pipe). This data is stored on a seperate sheet in the corresponding cell (cost is on sheet "hours" and task description is on sheet "task description" both in same cell. This allows a copy of the cost sheet to reflect the task description. I have tried recording macros but can't get anywhere.

View 1 Replies View Related

Description Outputs And Inputs Cells In Workbook

Feb 1, 2014

I have a small problem here with my worksheet. In my college i need to make a project about sales in a wooden floor shop.

I have done one part of work but actually i'm stuck at the specifications of my sheets.

What I need to do i added the screenshot with design Screenshot 2014-02-02 01.29.03.png

For my project i need to describe everything in my workbook as you can see in screenshot, i have made some notes but i'm note sure if its really right my teacher don't want or can't explain me well this part of work, i don't know why...

Now the question> i would ask some nice people if they can check my sheets and show me where i need to put inputs and outputs notes in the design sheet. Also in the screenshot I surrounded a part with red line there is a very strange thing no one know what is it 3 teacher telling different things.

View 1 Replies View Related

Align The Red Figure With The Corresponding Description In The Yellow Cells

Apr 16, 2009

Is there a way where i can have the figures in Red align with the corresponding description.

As it is when i run my report the account code will come on top of the description and i want to find a way to align them in the yellow cells.

View 6 Replies View Related

If And Vlookup Formula (bring Up The Corresponding Product Description )

Jun 15, 2009

I'm trying to get a cell {in the attached spreadsheet 'Order Form' Sheet- Cell 'B13'}, to bring up the corresponding product description {'Products' Sheet- Column C}. For the cell that is to bring up the product description {'Order Form' Sheet- Cell 'C13'}, the following is the formula I'm trying to use, but is coming up "#N/A":
=IF(C13>1000,VLOOKUP(A13,Products!$A$2:$D$12,3),IF(C13>287,VLOOKUP(A13,Products!$A$14:$D$25,3),IF(C1 3>0,VLOOKUP(A13,Products!$A$28:$D$38,3)," "))).

I haven't attempted the "Cost/Bottle" formula yet {'Order Form' Sheet- Cell 'D13'}, but it needs to correspond the product number {'Order Form' Sheet- Cell 'A13'} and the quantity ordered {'Order Form' Sheet- Cell 'C13'}, with the cost/bottle on the that quantity found on 'Products' Sheet, Column D.

View 5 Replies View Related

Update Description Of Item If Text Matches Using IF?

Mar 20, 2014

I've got 2 columns.

Column A, Item Description
Column B, Item Cost

In Column B, it will display the item cost as either a numerical value, (example $1.00) or it will say "disco" for discontinued.

I want to update the item description in Column A by adding the work "disco" if Column B display's "disco". If column B display's any numerical value (0-999999999) then I would it to do nothing.

what IF formula I need to accomplish this?

View 6 Replies View Related

Added XLSX File With More Detailed Description?

May 3, 2014

I've got several huge information bloks to handle.

Would you be so kind as to give the matter a reasonable consideration?

I've added an xlsx file with more detailed description.

View 1 Replies View Related

Filter Rows With Same Dates And Keyword From Description

Aug 7, 2008

I am having difficulty in filtering information from a table.

Essentially, I would first like to filter by same Expiry Dates (Column D). Then based on these same dates, filter by keywords (Column A) within text of those common dates.

The outcome should display entries that have "same expiry date: (Column D) and key word in common - e.g. common word may be "Fleet" (from Column A)

Attached spreadsheet explains further (2 worksheets).

View 14 Replies View Related

Mid Function :: Match Account Number With Description

Apr 20, 2009

I am trying to use a Mid function to help me in my vba problem.

In my first table I have a list of account numbers. E.g. 'ZZ500543'

In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath'

I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?

View 14 Replies View Related

Highlighted Description Whilst Hovering Over Lists

May 21, 2009

As part of a working spreadsheet there is a section which records faults by way of a numerical list in a dropdown menu. When a number is chosen the fault description appears in the adjacent cell. Is it possible when the mouse hovers over a number in the list the first 20 characters of the fault description could be shown before the fault number is chosen? The attached file is a simple example of the fault numbers and descriptions, in reality some of the faults have longer descriptions with more technical detail.

View 5 Replies View Related

Match Description Which Might Be Because Of ByVal Target As Range

Oct 22, 2008

I get and error which says Procedure declaration does not match description which might be because of ByVal Target As Range if this can not be used than what can be the exact solution for this as i have to get the Target address

Private Sub Workbook_SheetCalculate(ByVal Sh As Object, ByVal Target As Range)
Dim sht As Worksheet
Dim shtChild As Worksheet
Dim lngRow As Long
Dim intCol As Integer
Dim strValue As String
Dim Target As Range
Dim rng As Range
Application.ScreenUpdating = True
Application.StatusBar = False
Set sht = Sh............

View 9 Replies View Related

Count Formula For Description Text Field

Sep 25, 2006

I import a database with mostly unique records which has a description field that includes, among other things: Model Name, followed by predominant color. Unfortunately, there are no seperate fields for the Model Name or Color, e.g.:

DESCRIPTION
8070 CALLING WATER RED
508001 EYEDAZZLER LILIANA CARAMEL
502034 EYEDAZZLER LILIANA RED
#402022 DIAMONDS IN THE DARK RED
402024 DIAMONDS IN THE DARK PALOMINO

Is there a way to create a summary table which would count the number of occurences of the Model Name and the number of occurences of the color?

Fortunately, there are no more than 25 Model Names, and no more than 10 colors or color/combinations, so we could compare against two lists.

View 9 Replies View Related







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