Compressing Table To Capture Latest Instance Based On Each ID

Feb 6, 2012

I have a table that I am trying to compress to capture the latest instance based on each ID.

For example, let us say this is my data set:

ID___ | Name | Level_ | Score | Date
ID-32 | John | Beginr | 56.00 | 27-Jan
ID-33 | Jill | Beginr | 32.00 | 28-Jan
ID-34 | Bill | Beginr | 78.00 | 28-Jan
ID-34 | Bill | Interm | 39.00 | 29-Jan *
ID-33 | Jill | Beginr | 62.00 | 31-Jan
ID-32 | John | Interm | 61.00 | 02-Feb
ID-33 | Jill | Interm | 67.00 | 03-Feb *
ID-35 | Mike | Beginr | 82.00 | 03-Feb
ID-35 | Mike | Interm | 85.00 | 04-Feb
ID-35 | Mike | Advncd | 89.00 | 05-Feb *
ID-32 | John | Advncd | 24.00 | 05-Feb
ID-32 | John | Advncd | 52.00 | 08-Feb *

From this data set I wish to extract only the 4 entries with a "*" next to them. These entries give me the updated latest status for each student.

So the output table would look like:

ID___ | Name | Level_ | Score | Date
ID-32 | John | Advncd | 52.00 | 08-Feb
ID-33 | Jill | Interm | 67.00 | 03-Feb
ID-34 | Bill | Interm | 39.00 | 29-Jan
ID-35 | Mike | Advncd | 89.00 | 05-Feb

View 7 Replies


ADVERTISEMENT

Hlookup: Capture The First Instance Of A Value(ie 1) And Reflect The Top Row Of The Value (date)

Nov 19, 2009

Im trying to capture the first instance of a value(ie 1) and reflect the top row of the value (date). I cant seem to make Hlookup work. I got it to work on the first line using index and match, but when i tried to copy it on the succeeding cells, it gives me the wrong data.

I also tried the following

StartedDate started1-Dec2-Dec3-Dec4-Dec5-Dec6-Dec7-Dec8-Dec9-Dec10-Dec
aaaaaaayes12/1/200911
bbbbbbyes1/0/1900111
b2b2b2b2b2b2no
ccccccccyes111

View 2 Replies View Related

Compressing Large Pivot Table File

Jun 7, 2013

My pivot table file is currently 12.7 MB and I need to get it much smaller so I can email it and it still be functional with the data.

I have tried right clicking on the pivot table, going to PivotTable Options, clicking on the data tab, unchecking the 'save source data with file' box, and checking the 'refresh data when opening file' box. This shrunk the file perfectly but when i email the file the Pivot Table was unusable because there was no data to back up the function.

The Error messages I got were 'Cannot open PivotTable source file'..... and 'Pivot Table report was saved without the underlying data. Use the Refresh Data command to update the report'. When I click on Refresh Data I still get the same error message.

Any way I can make this File smaller and still functional.

View 4 Replies View Related

VBA To Capture Filter Value From Pivot Table

Jun 23, 2014

I want my report title is more dynamic which is based on the pivot table's <Status Date> filter. It mean if I select the <Status Date> filter option as 6-23-2014. Then the report title will automatic update as "Statistic Report 6-23-2014" instead of I have to change the date each time I run the report.

View 1 Replies View Related

Picking Latest Entry In A Table?

Nov 7, 2013

I have a table like this:

Book, Loaned to, Date
A Tale of 2 Cities, John, 1/1/2010
Oliver Twist, Mary, 2/3/2011
Great Expectations, James, 4/6/2011
Oliver Twist, Greg, 6/2/2010

I want to make it such that, when I input the name of the book, it will tell me the person whom I lent it to last and the date of the transaction. Preferably, this should be done without any assumptions on how the table is ordered. For example:

Book= Oliver Twist
Loaned to= Greg
Date= 6/2/2010

View 2 Replies View Related

Return Second Latest Value In Excel Table?

Aug 5, 2012

I have a table with different values on different dates. To get the newest value on the lastest date I'm using this formula; =INDEX(F5:K5;MATCH(9,99999999999999E+160;F5:K5)).

However, how I can get the second latest value? In this example I want a formula to to return the value previous price;

Latest observation
Latest observation date
Previous price
previous price change date
01.08.2012
02.08.2012
03.08.2012
04.08.2012
05.08.2012
06.08.2012

[code]....

View 8 Replies View Related

Selecting Latest Entry In Table?

Nov 7, 2013

I have a table like this:

Book, Loaned to, Date
A Tale of 2 Cities, John, 1/1/2010
Oliver Twist, Mary, 2/3/2011
Great Expectations, James, 4/6/2011
Oliver Twist, Greg, 6/2/2010

I want to make it such that, when I input the name of the book, it will tell me the person whom I lent it to last and the date of the transaction. Preferably, this should be done without any assumptions on how the table is ordered. For example:

Book= Oliver Twist
Loaned to= Greg
Date= 6/2/2010

View 7 Replies View Related

Capture Average From Grand Total In Pivot Table

Nov 6, 2013

Capture an average from the Grand Totals in a Pivot Table? If so what are the steps.

View 5 Replies View Related

Capture Which Pivot Table Field User Changed

Sep 12, 2007

I have a code that i want to run after an item in a page field on a Pivot table is changed. I have put the code in the PivotTableUpdate event. However I would like the code to run only if the user changes an item on a specific page field i have set up on the pivot table layout.

So, if I have 2 page fields on my pivot table pf1 and pf2 :If the user changes an item in pf1 the pivot table data changes but my code within the PivotTableUpdate event doesn't runIf the user changes an item in pf2 the pivot table data changes, my code detects this page field has changed, and my specific code runs.

View 3 Replies View Related

Compressing Multi Used Variables

Jul 3, 2009

What i would like to know is, In the code im using to make a phone call from a userform the varibles are set in each and every sub. It all works well though could they be compressed into only being used once like an option variable or somethink like that.

i know that more that one variable can be decleared at the same time of the same type "dim x, Y, z as long.

View 7 Replies View Related

Subtract First Instance Of Data From Last Instance In Columns

Mar 7, 2014

I need a formula in Column E to find the results for Column E or subtract A2 from C3, C3 from D3, A4 from C3, B5 from D5 and A6 from D6 .

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;

[Code]....

View 5 Replies View Related

Excel 2010 :: Macro To Select Latest Date In Dropdown Box Of Pivot Table?

May 9, 2013

I am trying to create a macro using the recorder that will select the latest date in a drop down list. The dates upate each day in the pivot table after the table is earlier refreshed. Using the recorder simply picks the same date each time. I want it to select the latest date. Am using Excel 2010 and have some very limited experience in vba for editing macros.

View 1 Replies View Related

Excel 2010 :: Macro To Select Rolling / Latest 6 Weeks In Pivot Table

Nov 13, 2013

I have a raw data in one file and then another file contains pivot table linked to the raw data. In the dashboard i need to show last 6 weeks trend which is linked to the pivot table. Currently i am manually unselecting previous week and selecting the latest week.

Ex: This week i select weeks 5, 6, 7, 8, 9, 10 from the pivot for the dashboard. When new data is updated next week, i want to select 6, 7, 8, 9, 10, 11. I cannot delete as i need the historical data.

I am using Microsoft Excel 2010.

View 1 Replies View Related

Capture Values Based On Number Of Columns?

Jun 7, 2012

My data comes from 3 different sources

Source A = 4 columns (3rd one is important)
Source B = 8 columns (3rd one is important)
Source C = 5 columns (4th one is important)

Is there a slick way to capture values based on the number of columns in that row?

View 1 Replies View Related

Removing Duplicates Based On Latest Revision?

Aug 20, 2012

I am importing some data from a source which every time I just want to have latest revision of documents and I want it to be dynamic so that every time I import data the deletion would be automatic the data I import is something like this

DOC NO.
DOC DEScription
status

[Code]....

and as you see I have duplicate documents with different revisions and I want to have the latest last revision only.

View 8 Replies View Related

Capture And Calculate Data Based On Work Week In VBA Or Formulas

Jul 21, 2014

I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.

Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.

For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.

here is the distribution of columns per week as reference.

(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns

(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns

View 1 Replies View Related

Return Latest Date Based On Matching Criteria

Jul 25, 2014

I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.

=MAX(INDEX($A$1:$C$40,MATCH(A2,$A$1:$C$40,0),3),1)

MakeModelSale Date
AudiA4 11-Jan-14
AudiA4 quattro 12-Jan-14
AudiA4 quattro3-Jan-14
AudiA5 Cabriolet 14-Jan-14
AudiA5 Cabriolet q15-Jan-14
AudiA4 16-Jan-14
AudiA4 quattro17-Jan-14
AudiA4 quattro18-Jan-14
AudiA6 quattro19-Jan-14
AudiA4 10-Jan-14
AudiA4 quattro09-Jan-14
AudiA4 quattro10-Jan-14
AudiA4 11-Jan-14
AudiA4 quattro11-Jan-14
AudiA4 quattro11-Jan-14
AudiA8L 11-Jan-14
AudiA8L 11-Jan-14

View 3 Replies View Related

Removing Duplicates From List Based On Latest Date?

Jan 6, 2014

I am working on a large data file (leasing file), that has many duplicates. The names on the file are duplicated due to the various variable costs associated with leasing. I need to remove the duplicates names based on the latest contract end date.

View 4 Replies View Related

Determine Latest Price Based On Couple Of Criteria

Apr 17, 2014

I have actual date , then I have names of customer and I have product reference. And what I want to find out is the latest price COLUMN D for which this specific product was sold to specific customer (Data in rows E,F,G,H are from another tab). So basically I want is to find out that latest price of product x27 sold to customer A on 15.7.2013 was 70 because on 12.7.2013 we have sold it to customer A for 70.

A
B
C
D
E
F
G
H

[code]....

View 5 Replies View Related

Return Rows Based On Latest Date AND Highest Quantity For Given ID

Dec 10, 2012

I'm trying to return a distinct list of rows that filter based on the latest date and largest quantity for each distinct AccountID and ProductID combination.

I tried some variant of the =max function, but I need two filters.

The purpose of this is to create a data set of all company accounts with the most recent number of products used to upload to a database.

The simplified and original data set is as follows:

AccountID
ProductID
Company
Date

[Code].....

View 7 Replies View Related

Deleting Duplicate Rows Based On Earliest Or Latest Time?

Mar 19, 2014

Below is an example of an Excel sheet I'm working with: [URL]

Basically, I'm trying to delete the duplicate rows by matching ID, Date and Type. If ID, Date and Type are the same, then, I want to only keep the record with the earliest Time in case of Type = In and the latest Time in case of Type = Out.

So, for example, in the case of ID = 1, there are 3 records for In, I only want to keep the one where Time is: 8:01 as this is the earliest. The other 2 records should be deleted.

Similarly, in the case of ID 3, I want to keep the record where Time = 18:05 as this is the later time out of the 2.

Can this be achieved by Conditional Formatting or Macro or VBA?

View 1 Replies View Related

How To Use Pivot Cache To Create Another Pivot Table Instance On A Different Worksheet

Jul 7, 2014

I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.

View 1 Replies View Related

Excel 2003 :: Calculate Sum Based On Partial Text Match And Latest Date

Jun 19, 2014

I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.

A B C
7/1/2012 0:00825-CL-A-41091-REG4.00
7/1/2012 0:00825-CL-A-41091-REG6.25
7/1/2013 0:00825-CL-A-41456-REG4.25
7/1/2013 0:00825-CL-A-41456-REG6.50
1/1/2014 0:00825-CL-A-41640-REG4.25
1/1/2014 0:00825-CL-A-41640-REG6.50
3/1/2014 0:00825-CL-E-41699-REG3.00
3/1/2014 0:00825-CL-E-41699-REG4.00

View 14 Replies View Related

Move Latest Updated Cell In Sheets In A Workbook Based On Time And Date

Mar 1, 2014

I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.

Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.

View 5 Replies View Related

Excel 2010 :: Macro To Open XLSM File Based On Latest Date Found In Filename

Feb 11, 2013

I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.

There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.

Macro open an excel file based on the latest date found in filename.

View 9 Replies View Related

Formula To Pull Latest Date From One Column Based On Entry In Adjacent Column

Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed
Disposal Order
Latest Decision date for D.O.

06/05/2014
D.O.001

[Code] ........

View 6 Replies View Related

How To Capture Data From One Cell To Another Based On Another Cell Value

Apr 18, 2014

i am in need of the following.

A B C

9.30am 120 120
9.31am 119.9
9.32am 119.8
9.33am 121
9.34am 120.9
9.35am 120.8 120.8

Here A and B value is keep on changing from external source, when A1=9.30am, the B1 value 120, then C1 should capture the B1 value and stored in it. like wise it captured and stored for every 5 minit or as per requirement.

View 9 Replies View Related

Returning Value From A Table Based On Last Non Zero Entry In Table

Dec 5, 2013

I am trying to basically search for the last non zero entry in a column and then return a value in the same row as that last non zero entry.

For example:

My first column of the table is time, second column is submission/marking of coursework (denoted by +1 & -1) and the third column is the number of courseworks awaiting to be marked. My aim is to search the third column for the last non zero value then return the time (from first column) that last non zero value appears.

View 6 Replies View Related

Creating A Mat Table Based On A Price Table

Dec 7, 2008

Part 1: Pricelist
The user presses 1 of 3 macro buttons to access the price list.
The buttons represent 1 of 3 parts of a construction project.
The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro,
depending on which button is pressed, hides all but the relevent quantity
col & total col.

Each job phase needs its own total & having it setup this way makes it
easier to enter new items into the table (only have to enter
the item once, rather than 3 times).

The first html shows a simplied example of the "price list" described above
(before & after the quantitiy & price cols are hidden).
The user adjust the quantity & the sheet calculates the price total.
Part 1: Material listThe material list is a list of items & quantities the user selected in the
quantity col of Price list. This list is to be printed & given to the workers.
It does not show price, only item & quantity.

This list, unlike pricelist, must be visiablly broken down by job phase.

I dont want the material list to be the price list repeated vertically
on a sheet 3 times. This would take up too much space & require new
items to be entered once in the pricelist & then 3 times in the mat list.

I would rather the mat list be on a seperate sheet from price list & have
a formula in cell matlistA1 that searches the phase 1 quantity col
(then phase2 & 3) for the first #>0.

When a #>0 is found, the formula
would return an item name into the cell on matlist. Then the formula
would run in matlistA2, & search for the next #>0.
(& so on until a complete mat list table, broken up into phases is created)

Sheet2

ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73
..............................

View 9 Replies View Related

Fill Table Based On Corresponding Table & Being Between 2 Dates

Aug 31, 2009

I have an 'existing results table' as per my attached sample. From this table I need to create a list of 'sold' for each annual date range - i.e. I need a separate list for items sold within that date range. see my example in red. *Note. I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.

View 4 Replies View Related







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