Need some formula help!

Ron99

Active Member
Joined
Feb 10, 2010
Messages
338
Office Version
  1. 2016
Platform
  1. Windows
I have a problem here,

I have spreadsheet where the data is in the given order


<TABLE style="WIDTH: 431pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=572><COLGROUP><COL style="WIDTH: 215pt; mso-width-source: userset; mso-width-alt: 10459" width=286><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" span=2 width=74><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 215pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=286>ACTIVITY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=74>due date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=74>Sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Ron</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=74>Shawn</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Negotiate and sign LOI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Pending</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Collect Verified volume of technical stores</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/15/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Collect Verified volume</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2/11/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>completed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Receive quote of 1600 items (1st PO)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Place 1st purchase orders</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/15/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>completed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Send revised RFQ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>4/2/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Compare prices of bal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>4/6/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Receive proforma Invoice for 1st PO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/2/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Training schedule for sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/6/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>completed</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Process for overseas</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/18/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>completed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>procedure guidelines</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/26/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21>Send PO for calculate savings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/30/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Pending</TD></TR></TBODY></TABLE>



I need the data in the below given way, is this possible by using a formula ? I need the data only which are pending. I have manually created the below table.

<TABLE style="WIDTH: 312pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=415><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9325" width=255><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow; WIDTH: 65pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21 width=86>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 191pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=255>Activity</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=74>due date</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Negotiate and sign LOI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3/1/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Receive quote of 1600 items (1st PO)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1/1/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Receive proforma Invoice for 1st PO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3/2/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Send PO for calculate savings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3/30/2011</TD></TR></TBODY></TABLE>

Regards,
Ron
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Assuming your data table resides in A1:E13 and your summary table resides in A16:C20, try:

Code:
=INDEX(A$2:A$13,SMALL(IF($C$1:$E$1=$A17,IF($C$2:$E$13="Pending",ROW(A$2:A$13)-ROW(A$2)+1)),ROWS(B$17:B17)))
In B17, committed with CTRL+SHIFT+ENTER and copy down and across.

If your summary table is potentially longer than the records being returned, wrap the above formula in IFERROR() to hide the #NUM! errors. IFERROR will only work with Excel 2007 and beyond. If you don't have Excel 2007, post back and I will provide an alternative method for hiding these errors.

Matty
 

Ron99

Active Member
Joined
Feb 10, 2010
Messages
338
Office Version
  1. 2016
Platform
  1. Windows
Hi, thank you for your reply, when I used the formula I got the result as below,
<TABLE style="WIDTH: 464pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=618><COLGROUP><COL style="WIDTH: 217pt; mso-width-source: userset; mso-width-alt: 10569" width=289><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9325" width=255><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 217pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=289>Negotiate and sign LOI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 191pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=255 align=right>3/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=74>Pending</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Receive quote of 1600 items (1st PO)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Receive proforma Invoice for 1st PO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3/2/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>pending</TD></TR></TBODY></TABLE>

but the result I would like is as mentioned in the first thread, just to clarify, I need the name of the person, then activity and due date, I do not need the word pending, coz the formula should identify the word pending and then extract the person name in column A, Activity in column B and due date in columnC, I should be able to do this for others as well, if formula is not possible I wouldnt mind going with VBA code, thanks in advance!!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Let A1:E13 house the sample you posted, the headers included...

<TABLE style="WIDTH: 414pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=552><COLGROUP><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9216" width=259><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" span=2 width=82><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2048" width=58><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2531" width=71><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 194pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=259>ACTIVITY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82>due date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82>Sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 43pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=58>Ron</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=71>Shawn</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Negotiate and sign LOI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Pending</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Collect Verified volume of technical stores</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>15-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Collect Verified volume</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>11-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>completed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Receive quote of 1600 items (1st PO)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Place 1st purchase orders</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>15-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>completed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Send revised RFQ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2-Apr-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Compare prices of bal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>6-Apr-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD></TR>

<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2661642 class=xl66 height=19>Receive proforma Invoice for 1st PO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Training schedule for sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>6-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>completed</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Process for overseas</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>18-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>completed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>procedure guidelines</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>26-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Send PO for calculate savings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>30-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>-</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>pending</TD></TR></TBODY></TABLE>

Let's do the required processing for Sam in H:J...

<TABLE style="WIDTH: 306pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=407><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2304" width=65><COL style="WIDTH: 190pt; mso-width-source: userset; mso-width-alt: 8988" width=253><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3157" width=89><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65>Pending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 190pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=253> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=89> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65>Sam</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 width=65>Idx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 190pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=253>Activity</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 67pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=89>due date</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>Negotiate and sign LOI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>1-Mar-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>Receive quote of 1600 items (1st PO)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>1-Jan-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>Receive proforma Invoice for 1st PO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>2-Mar-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>Send PO for calculate savings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>30-Mar-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD></TR></TBODY></TABLE>

H1: Pending

H2: Sam

H3, just enter:
Rich (BB code):
=COUNTIF(INDEX(C2:E13,0,MATCH(H2,C1:E1,0)),H1)

H5, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS(H$5:H5)<=$H$3,SMALL(IF(INDEX($C$2:$E$13,0,
    MATCH($H$2,$C$1:$E$1,0))=$H$1,ROW($C$2:$E$13)-ROW($C$2)+1),
      ROWS(H$5:H5)),"")

I5, just enter, copy across, and down:
Rich (BB code):
=IF(N($H5),INDEX($A$2:$B$13,$H5,MATCH(I$4,$A$1:$B$1,0)),"")
 

Ron99

Active Member
Joined
Feb 10, 2010
Messages
338
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Aladin,

The first two formula works fine, just the last formula isn't giving the result.


=IF(N($H5),INDEX($A$2:$B$13,$H5,MATCH(I$4,$A$1:$B$1,0)),"")</PRE>
I guess the match function, the lookup value is refering to a blank. Can you plez help.

Regards,
Ron
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hi Aladin,

The first two formula works fine, just the last formula isn't giving the result.



=IF(N($H5),INDEX($A$2:$B$13,$H5,MATCH(I$4,$A$1:$B$1,0)),"")
</PRE>
I guess the match function, the lookup value is refering to a blank. Can you plez help.

Regards,
Ron

Do you have the header values as you posted and as I used?

The processing area contains:

H4: Idx

I4: Activity

J4: due date

The data area contains:

A1: ACTIVITY

B1: due date

So, the formula in I5, which is:

=IF(N($H5),INDEX($A$2:$B$13,$H5,MATCH(I$4,$A$1:$B$1,0)),"")

should return the desired values.
 

Ron99

Active Member
Joined
Feb 10, 2010
Messages
338
Office Version
  1. 2016
Platform
  1. Windows
Thanx mate!!...it works fine now!..Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,109,494
Messages
5,529,192
Members
409,857
Latest member
KailuaTown
Top