Filtering Multiple Entries

[george08755]

I have a worksheet where we keep track of submittals. In the worksheet, the history of all submittals are kept. So if it took us three times to have a submittal approved, the workbook shows the first two rejected, and the third approved.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I am looking for a way to filter out all of the duplicates, so that only the most recent version of the submittal is displayed. Hope this makes sense. Thank you in advance.<o></o>

 

[jasmith4]

If column A has the submittals, and column B has rejected/approved, just use data filtering on column B.

 

[george08755]

Column A has the submittal, column B has the version of the submittal, column C has the status of the submittal. I want to be able to view the status of the latest version of the submittal if it is not approved. To do this, I would need to filter out multiples of submittals, with only the one with the most recent submittal date shown. I am not sure how to do this, or if it is even possible.

 

[c_m_s_jr]

Column A has the submittal, column B has the version of the submittal, column C has the status of the submittal. I want to be able to view the status of the latest version of the submittal if it is not approved. To do this, I would need to filter out multiples of submittals, with only the one with the most recent submittal date shown. I am not sure how to do this, or if it is even possible.

Perhaps a helper column and then you can filter on that 1 column?

NOTE: the formula is an array and will be expensive to run depending on the size of your data.

Excel Workbook
	A	B	C	D
1	Submittal	Version	Status	Filter
2	11885	1	Voided	0
3	11885	2	Reject	0
4	11885	3	Pending	1
5	11886	1	Voided	0
6	11886	2	Pending	1
Sheet1

 

[jasmith4]

Make a pivot table!

1. Select the three columns and choose Insert / Pivot Table / Pivot Table from the ribbon.
2. On the Create Pivot Table dialog box your Table/Range is chosen already (Sheet1!$A:$C) -- choose New Worksheet
3. In the Pivot Table Field List drag:
   • Submittal to Row Labels
   • Status to Column Labels
   • Version to Values, where you'll see Count of Version.
4. Click on Count of Version, choose Value Field Settings, and change Count to Max.
5. Back on the ribbon, under Pivot Table Tools, choose Design / Grand Totals / On for Rows Only, so you'll see grand "totals" (actually grand maxima) on the right side, not the bottom.
6. On the top row of the table itself drop down Column Labels and remove the checks from (blank) and Approved.

The right side will then show the highest non-approved version number of each submittal.