Looking up data based on text and a date

[huffywork]

Hello, I know just the bare basics in Excel, but I really need some help.

I have a spreadsheet I fill out daily for invoices from my office. I need to be able to create quick reports that can do the following.

Take a single 'item' and give me multiple columns of the same row but the criteria is it must be the most recent date.

For Example.

Employee - Company - Rig - Work - Invoice # - Date - Equipment - Comments

I want to be able to pick one of these columns, and then get the last row based on date.

Example: I choose a rig name, and get the last date that rig name shows up on the spreadsheet showing all the information 'employee - company - invoice, ect'

Or I can choose Equipment, and find the last row date for that equipment. Letting me know where that equipment was used most recently.


Can you help me figure out how to do this?

 

[cyrilbrd]

Hi, have you tried to extract your data within a pivot table?

 

[huffywork]

Hi, have you tried to extract your data within a pivot table?

Would that allow me to keep adding new data to the original sheet and update itself?
I add about 30 to 75 rows of data a day.

 

[huffywork]

Would that allow me to keep adding new data to the original sheet and update itself?
I add about 30 to 75 rows of data a day.

Ohh, for more information.

I currently have 6000+ rows of data I have to pull this information from. I noticed some things about a pivot table that makes me think I can't keep adding more data without rebuilding the pivot table again.

I saw another post where someone used a vlookup (max) type setup, as well as a Match command. I just can't seem to figure out how to adjust that to what I need done.

I have so many different reports I have to build each day.

Such as:
Employee Hours over the last week, with daily totals.
Last known location/use of equipment
Last known status of a rig
List of invoices missing signatures from customer

And a few more. At the moment, I simply have to go through the 80 records I input each day, and update each sheet that exists for these reports, then print it out. This of course takes a lot of time, and often I get invoices turned in so late it affects a report that was printed out weeks ago. That is not a huge issue, but I'm really hoping to have something that updates itself as I type in new rows of data.

Thanks for the help so far, but I can't see how a pivot table is going to work for what I need without rebuilding it every day.

 

[Excelaron]

Use a an Excel table for your source data the table will grow dynamically and all you have to do is refresh the pivot table. I use pivot table with source table of 18,000+ rows

 

[huffywork]

Use a an Excel table for your source data the table will grow dynamically and all you have to do is refresh the pivot table. I use pivot table with source table of 18,000+ rows

Thanks for the help.

I tried this, and have been experimenting with the pivot table. I'm looking at help files online, but every one of them seems focused on amounts and totals. I don't need a sum of anything. I simply need a way to filter out records quickly. I'm just not seeing how to setup this pivot table to give me the entire row of information based on picking just the most recent date and a single criteria from another column. Is there any advice you can give on how to setup the pivot table to display everything in the row?

 

[cyrilbrd]

I think pivot may do the trick, either as suggested by Excelaron within a table or straight from a sheet. You can select a range bigger than your actual data, refresh is the only thing needed to 'add' new dataset, no need to 'rebuild', FYI I am using dataset of +30T rows and +1000 columns and the pivots work perfectly.

 

[cyrilbrd]

...I don't need a sum of anything. I simply need a way to filter out records quickly. I'm just not seeing how to setup this pivot table to give me the entire row of information based on picking just the most recent date and a single criteria from another column...

Place the field names in the Row Labels instead of the ∑Values, and set the layout to Tabular. You may opt to place some specific Fields in the Report Filter for easier and faster filtering, although you may filter within the Rows Labels as well.
For 'cosmetic' you may opt to hide the subtotal as to keep a linear presentation.

Did this clarify things a bit?

 

[huffywork]

Place the field names in the Row Labels instead of the ∑Values, and set the layout to Tabular. You may opt to place some specific Fields in the Report Filter for easier and faster filtering, although you may filter within the Rows Labels as well.
For 'cosmetic' you may opt to hide the subtotal as to keep a linear presentation.

Did this clarify things a bit?

Thanks! I put all the fields into rows instead of having something up in the filter and it looks so much better now. There is just one remaining problem.

How do I only get the most recent date to show up? I can filter for the last week or the last two weeks or something, but that's just a bit more data than I want to display. Is there a way to filter just with the last date?

 

[huffywork]

Nevermind!!! I just had to use the date in the value field, and set it to MAX!

Thanks for everyones help!