Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Index, match, duplicates in array - how to pick up the last/bottom line

Hi
I have tried to find solution for my problem but so far no luck. My formula with defined names is this

=INDEX(zflexSTATdelDte,MATCH(KHL_AUK_ID,zflexAUKid,0))

If in array there are duplicates it always picks up the first line. Its great as thats what I need. But now I need exactly the same formula but now I would like it to pick up the last line from array. That is if there are duplicates.
Can someone advise me how to change the formula. I will give an example

---zflexAUKid------------zflexSTATdelDte
180081418310------------ 14/04/2011
180081418410------------ 11/04/2011
180083049910------------ 01/03/2011
180083049910------------ 01/03/2011
180083049910------------ 01/04/2011
180083066010------------ 29/03/2011
180083064710------------ 09/04/2011

So as you can see in array column there are x3 duplicates highlighted in red. Current formula picks up the date in green. The new formula that I'm after should pick up the date in blue.

Any ideas?

Kind regards,
Rain

So if in array zflexAUK there are some duplicates


Post your answer or comment

comments powered by Disqus
Hi!

I am trying to find a function to look up the last and last but one cells in a column. My array is constantly being updated with new rows of data but I want a function to find the latest value and compare it to the one before so I can work out the change. (The array updates via a web query every 10 mins)

I have this function that finds the last value in the column L

=LOOKUP(99^99,L:L)

Does anybody know how I would make it look up the last but one (preceeding) value?

Thnaks for any wisdom you can share!

Steve

I have given a range (say a1.a100 which contain IF function in every cell) to
my users. They are to enter value into the cell everyday continuously.

Can anyone help me with VBA codes for me to pick up the last entered value
(say A42, A43 onwards are still empty) on anyday, to be linked to another
workbook?

I used the SpecialCells(xlCellTypeLastCell) feature, it doesn't work. It
always return A100, possibly because A100 contains an IF statement.

Thanks.

I've been using the following formula as an array (see column E of my sample):

{=IF(ROWS($E$3:$E3)>MATCH(REPT("Z",255),$A$3:$A$2901),"",INDEX($A$3:$A$2901,MATCH(REPT("Z",255),$A$3 :$A$2901)-MIN(10,MATCH(REPT("Z",255),$A$3:$A$2901))+ROWS($E$3:$E3)))} and it works great!

I've been trying to use this formula to pickup the last 10 entries which are spread out every 7 cells in columns B and C (see FORUM SAMPLE 6) and getting a return of #NA –

Is there a formula or how could I modify the one I'm using that would pick up the last 10 equally spread out every 7 cells?

How to sum up the data which match two criteria?

e.g.
A Home BB Call 80
B Office Mobile 8370
C Home Mobile 870
D Home BB Call 860
E Office BB Call 850
G Home Mobile 480
H Office Mobile 380
I Home BB Call 10

Is it possible to sum up all match "Home" and "Mobile" by a formula?
870 + 480

Thanks a lot

When printing a matrix with long text write-ups - The last few lines of a
cell will be cut off. Is there a way to extend the parameters of a cell to
not do this? Or - are there other solutions?

Hi which formular to use to pick up the last word from right end?

601 St Kilda Road Suncorp/ AAMI Level GD Melbourne
Inala Avenue Inala Plaza Shopping Centre Shop C7(B)&C8, Inala,
Coles Liquor Group Liquorland North Lakes 1 Macmillans Road North Lakes

Dear all,

I have to 2 worksheets. One of them is used for storing up the data
and the other worksheet is to display specified type of data in worksheet
one. I'd written the worksheet formula for it. But my problem is I have to
sort the data in worksheet two according to one of column. Since I use the
Index formula to pick up the necessary data, I can't use the sort function
to so. Anyone know how to implement sorting by worksheet functions? Please
help. Thanks

Best Rdgs
Ellis

Hello!

I have a spreadsheet that has Jan-Dec as the column heading. Each month, I enter a number into the row under the month heading. I need a formula that will pick up the number in the current month cell, but ignore any that are empty.

For example, I may have a number of 50 under January, 55 for February, 45 for March, but April thru Dec. are still blank. I need the formula to pick up the number of 45 from March, then the next month, I would need the April number. (I cannot total the 12 months because the number I enter is already the year to date number).

After that I need to divide....
There is a "target" number for the year. I need to take the current month total, divide by the target for the year to get a result to show percentage of how close or far off the number is from the target.

I tried using an "if" statement but Excel only allows up to seven if statements, I need 12.

Help please

All,

I have this huge data base which consists of a quote date, a contract date,a dn a price. How do I use a formula to pick up a price corresponding to a particular quote date and contract date?

Colomn A: Quote Date
Colomn B: Contract Date
Colomn C: Price

Thanks,

In Macros, I would like to save or open file name as YYYYMMDD or pickup name
from a cell where date is mentioned as =today() with format as YYYYMMDD. or
any other alternative to pick up the file with naming convention as mentioned
above.

Hi,

I have a series of dates in "Daily!" worksheet, cells C2:IV2, starting from
1-Jan-05 and running through till 11-Sep-05.

In cells A6:A62, I have various cost centres which can be allocated costs,
such as sales, rent, overheads, miscellaneous, etc.

So I therefore have a grid (C6:IV62) in which I can allocate costs against
the appropriate cost centre & date.

In "Weekly!" worksheet, I have the same list of Cost Centres again appearing
in cells A6:A62. However, instead of having daily dates in row 2, I have the
start of the week headings (i.e. in C2 is 02-Jan-05, in cell D2 is 09-Jan-05,
in cell E2 is 16-Jan-05).

In cell C6, I have the following formula:

=SUM(OFFSET(Daily!$D6,0,(COLUMN(C$2)-COLUMN($C$2))*7,1,7))

which I can copy across and down and will pick up the weekly totals for each
cost centre (note it starts at "Daily!$D6 as this is the first Sunday in
2005).

However, I also have a "Monthly!" worksheet, which is set up in exactly the
same way, and in which I want to put a formula in C6 which I can copy across
and down to pick up the monthly totals for each cost centre. However, I
can't think how to do it as the number of days in each month is not constant
(because the number of days per week is always 7, the Cols and [width]
parameters in the OFFSET function above were relatively easy to write).

Any help greatly appreciated,

Regards,

Gary T.

I've been struggling with this for a couple hours and I haven't been able to figure this out. I'm trying to make a basic receipt-tracking excel template in 2007. Basically there's one sheet that has my general budgeting (income and expense categories listed with budgeted amounts). Then there's a second sheet where I can put in receipts/payments, so the columns are category, date, and amount.

After reading a lot of info online I learned how to create a pivot table where the rows are my different categories (i.e. groceries, insurance, restaurants, etc), and my columns are year, then month (I used the MONTH() and YEAR() functions to extract that from the date I entered for each receipt). This pivot table allows me to look up how much I spent in each category for a given month.

Now here's the next step. There are a lot of things I want to be able to do with this setup, but the first is to add a column to my general budget page that has this month's expenditures in each category right next to the budget for each category. I figured out how to get today's year and month (by using MONTH(TODAY()) and YEAR(TODAY())) but I cannot figure out how to look up the values in my pivot table. I can use lookup() to find a specific category, but then I couldn't seem to nest lookup()s to find a value using category, year, and month.

Please share your wisdom with me! I'm attaching my spreadsheet as it is and all data is fake, so no personal info is given.

I'm trying to work out how I can get a cell to pick up the last non-zero value in that row. I can move the data manually but would appreciate any ideas on how to do it more efficiently.

Any thoughts much appreciated!

Hi,

I have a series of dates in "Daily!" worksheet, cells C2:IV2, starting from
1-Jan-05 and running through till 11-Sep-05.

In cells A6:A62, I have various cost centres which can be allocated costs,
such as sales, rent, overheads, miscellaneous, etc.

So I therefore have a grid (C6:IV62) in which I can allocate costs against
the appropriate cost centre & date.

In "Weekly!" worksheet, I have the same list of Cost Centres again appearing
in cells A6:A62. However, instead of having daily dates in row 2, I have the
start of the week headings (i.e. in C2 is 02-Jan-05, in cell D2 is 09-Jan-05,
in cell E2 is 16-Jan-05).

In cell C6, I have the following formula:

=SUM(OFFSET(Daily!$D6,0,(COLUMN(C$2)-COLUMN($C$2))*7,1,7))

which I can copy across and down and will pick up the weekly totals for each
cost centre (note it starts at "Daily!$D6 as this is the first Sunday in
2005).

However, I also have a "Monthly!" worksheet, which is set up in exactly the
same way, and in which I want to put a formula in C6 which I can copy across
and down to pick up the monthly totals for each cost centre. However, I
can't think how to do it as the number of days in each month is not constant
(because the number of days per week is always 7, the Cols and [width]
parameters in the OFFSET function above were relatively easy to write).

Any help greatly appreciated,

Regards,

Gary T.

Hoping someone out there can help! I have been stuck on this problem for hours.

I currently have a worksheet (Excel 2007) that keeps track of our receipt and use of inventory. Each item in our inventory has a “stock number,” and every time we receive a new shipment of the stock we assign it a “load tag” as well. We track each use (“pull”) and return of the stock. Sometimes, we make partial pulls and returns of the stock numerous times, meaning the load tag appears multiple times in the inventory spreadsheet. Other times, we pull and use it all at once. As such, the corresponding columns of the spreadsheet look like this:

LOAD TAG STOCK # QUANTITY
AH00015 43750062879 25,000
AH00015 43750062879 -25,000
AH00016 43750062879 27,600
AH00016 43750062879 -27,600
AH00016 43750062879 23,000
AH00016 43750062879 -23,000
AH00775 43750062879 27,600
AH00775 43750062879 -27,600
AH00775 43750062879 18,400
AH00775 43750062879 -18,400
AH01071 43750062879 27,600

What I am trying to do is create a separate worksheet where I can type in the stock number and get returned the latest of each load tag and its corresponding quantity (I can handle the formula for the quantity). In other words, I want to return only the last (i.e. latest/most current) entry for each load tag – not all instances of each load tag.

Would really appreciate any help you may have!

Hello

On a daily basis we have a report that is run from an SQL server, the file is called Adhoc Payments Applied.xls. the report is run each day and saved in a file where it adds the number of times it has been saved in the file.

Eg, if the report was first run 2 days ago then it will be called 'Adhoc Payments Applied.xls'
then run yesterday it will be called Adhoc Payments Applied_1.xls

I can locate the file but how do I get it to pick up the last one. I just can't work it out.

this is what I have so far. What it does is looks for one labelled number 1 first, if this does not exist then the file will not have a number label after it (I.e start of month) If it does exists i need it to see how far the numbers go up and select the last one.

Sub check()

Dim fs As Object
Dim strfile As String

Set fs = CreateObject("Scripting.FileSystemObject")
Filename = "Adhoc Payments Applied" & AdhocNum
strfile = "G:MI Reports24th March 2010Finance" & Filename & ".xls"

If fs.FileExists(strfile) Then
MsgBox Filename
Else
MsgBox "NO FILE"
End If

End Sub

Function AdhocNum()

Dim fs As Object
Dim strfile, dash As String

AdhocNum = 1
strfile = "G:MI Reports24th March 2010FinanceAdhoc Payments Applied_"
dash = "_"

Set fs = CreateObject("Scripting.FileSystemObject")

If fs.FileExists(strfile & 1 & ".xls") Then

Else
AdhocNum = ""
End If

End Function
Please can someone help?

Thanks
Rich

Hi all,

I have a worksheet which holds the results of two database queries. I want
to combine the regions programmatically saving the results to a new sheet. I
cannot create a single query to retrieve the combined data.

The initial worksheet looks similar to the following illustration:
Sheet 1:
Region 1:
Header1a Header1b Header1c ... Header1(n)
Row1a Row1b Row1c ... Row1(n)
Row1a Row1b Row1c ... Row1(n)
.... ... ... ... ...
.... ... ... ... ...
Row(N)a Row(N)b Row(N)c ... Row(N)(n)

Region 2:
Header2a Header2b Header2c ... Header2(n)
Row1a Row1b Row1c ... Row1(n)
Row1a Row1b Row1c ... Row1(n)
.... ... ... ... ...
.... ... ... ... ...
Row(N)a Row(N)b Row(N)c ... Row(N)(n)

The regions are separated by one or more blank lines. I'm going to remove
the sorting, grouping, and subtotals being applied to each region. Finally,
I want to grab the data combining it onto a new sheet. The columns and
formatting will alight between the regions. I'm not sure how to pick up the
data.

I receive an email on a daily basis with an excel file which i am required to format into the way i want it.

I was looking to save this file down from the email into a folder on a daily basis and create a macro which formats the report (its always comes in on the same format each day)

I was going to create a macro in a seperate spreadsheet which with the click of a button will open the last modified file from that folder.

I will then be able to run my macro to format the report.

Can anyone help?

Cheers

Hi,

What I have is a spread sheet that doubles up as an annual/vacation leave and Training calendar.

If someone is on leave/vacation they simply enter a ‘1’ or if on training day they enter a ‘T’ into a cell for that date.

I have managed to use conditional formatting to colour code leave/vacation and training days and can add up the number ‘1’ signifying the total leave/vacation days but I need to know how to add up the total number of T’s entered to signifying the total number of training days taken

e.g.

in cell A1 input T

in cell B1 input T

in cell C1 input T

in cell D1 sum = 3 i.e. total number of T's in cells A1:C1

Any help would be greatly appreciated

Excel version 2003

Many thanks

Rob

I have a spreadsheet with several worksheets. The first worksheet does a VLOOKUP on all of the other worksheets. I want to change the colour of the font in some of the cells based on the information in there (this is arbitrary and cannot be worked out by formula) in the other worksheets, but I want the first worksheet that does the VLOOKUP to pick up the colour of the font from any of the other worksheets - is this possible?

I am doing a worksheet. On this chart I want to track the price of an object
sold. Along with that I want to add up the number of the objects that have
been sold, by just adding up the number sold in a column. Is there a way to
do this, beside creating another column and putting a qty in it.

Hi,

If i have a column of numbers, can i pick up the number that falls within a certain range? For example, i want the number that is between 10000 and 20000, is that any formula or function i can use to pick up that number? Appreciate if somebody can help me. thxs.

I don't find how to find automaticly my last data (non blank cell) inside a
table
thanks

What code can I use to pick up the xvalues on a chart?


No luck finding an answer? You could always try Google.