HOW TO: Display specific data dependant on a condition by using formulas not filters?

A

abupu

New Member
Joined
Aug 17, 2011
Messages
17
Hi Guys,

This is the problem im having:

<table border="0" cellpadding="0" cellspacing="0" width="281"><colgroup><col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt;width:86pt" height="17" width="114">Status</td> <td class="xl1131" style="width:48pt" width="64">Task</td> <td class="xl1131" style="width:77pt" width="103">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">a</td> <td class="xl1130" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">b</td> <td class="xl1130" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">c</td> <td class="xl1130" align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">d</td> <td class="xl1130" align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">e</td> <td class="xl1130" align="right">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">f</td> <td class="xl1130" align="right">6</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">g</td> <td class="xl1130" align="right">7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">h</td> <td class="xl1130" align="right">8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">i</td> <td class="xl1130" align="right">9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">j</td> <td class="xl1130" align="right">10</td> </tr> </tbody></table>
I have this data table on Sheet 1 and i want to display ONLY the rows that show "Active" on another sheet. Also, i want to be able to display only the columns I would like on another sheet.

An example would be I want to display all rows with the Status as "Active" and only the People column on Sheet 2. This output should look like this:

<table border="0" cellpadding="0" cellspacing="0" width="103"><colgroup><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;width:77pt" height="17" width="103">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">10</td> </tr> </tbody></table>
I require to do this without the use of filters and the data should update (increase or decrease in rows) if the Status of other rows are changed to "Active" on Sheet 1.

Any ideas?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
You could try this, on your second sheet in cell A2 add

=IF(Sheet1!A1="Active",Sheet1!B1,"") and drop down to as far as you need to go

Assuming your first cell with Active/Discontinued is in A1 and the first 'people' are in B1

The in sheet 2 in cell B2 add

=IF(ISNUMBER(SMALL(A2:A30,ROW()-ROW(A$1))),SMALL(A2:A30,ROW()-ROW(A$1)),"")
 
Upvote 0
This first IF statement finds which are active and displays the Task, but how do i ignore all the blank rows in between?

I get this:

<table border="0" cellpadding="0" cellspacing="0" width="245"><col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> <col style="width:48pt" span="2" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl1133" style="height:12.75pt;width:88pt" height="17" width="117">Status</td> <td class="xl1133" style="width:48pt" width="64">Activity</td> <td class="xl1133" style="width:48pt" width="64">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td class="xl1132">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td class="xl1132">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td class="xl1132">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">c</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">d</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" height="17">
</td> <td class="xl1133">j</td> <td>
</td> </tr> </tbody></table>
Can i do this without any blank rows in between?
 
Upvote 0
That is what the second formula will do, it will arrange them without blanks, is that not what you want ?
 
Upvote 0
This works for numbers but not for text, and i think its ordering it accending.

Another problem that I might have is when I do the same for a second colomn (ie: checking if active and copying across to Sheet 2 the Activity and People columns), this is an example of that instance:

<table border="0" cellpadding="0" cellspacing="0" width="167"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;width:48pt" height="17" width="64">Activity</td> <td class="xl1132" style="width:77pt" width="103">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt" height="17">c</td> <td class="xl1131" align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt" height="17">d</td> <td class="xl1131" align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt" height="17">j</td> <td class="xl1131" align="right">10</td> </tr> </tbody></table>
 
Upvote 0
abupu said:
Hi Guys,

This is the problem im having:

<table border="0" cellpadding="0" cellspacing="0" width="281"><colgroup><col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt;width:86pt" height="17" width="114">Status</td> <td class="xl1131" style="width:48pt" width="64">Task</td> <td class="xl1131" style="width:77pt" width="103">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">a</td> <td class="xl1130" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">b</td> <td class="xl1130" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">c</td> <td class="xl1130" align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">d</td> <td class="xl1130" align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">e</td> <td class="xl1130" align="right">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">f</td> <td class="xl1130" align="right">6</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">g</td> <td class="xl1130" align="right">7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">h</td> <td class="xl1130" align="right">8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">i</td> <td class="xl1130" align="right">9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">j</td> <td class="xl1130" align="right">10</td> </tr> </tbody></table>
I have this data table on Sheet 1 and i want to display ONLY the rows that show "Active" on another sheet. Also, i want to be able to display only the columns I would like on another sheet.

An example would be I want to display all rows with the Status as "Active" and only the People column on Sheet 2. This output should look like this:

<table border="0" cellpadding="0" cellspacing="0" width="103"><colgroup><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;width:77pt" height="17" width="103">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">10</td> </tr> </tbody></table>
I require to do this without the use of filters and the data should update (increase or decrease in rows) if the Status of other rows are changed to "Active" on Sheet 1.

Any ideas?
Click to expand...
A different approach...

A1:C11 on Sheet1 houses the sample you posted, including the headers...

<TABLE style="WIDTH: 208pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=277><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4664" width=131><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 48pt" width=64><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: 98pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=131>Status</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=82>Task</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>People</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=xl64 height=19>Discontinued</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=xl64>a</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=xl64 align=right>1</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=xl64 height=19>Discontinued</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=xl64>b</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=xl64 align=right>2</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=xl64 height=19>Active</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=xl64>c</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=xl64 align=right>3</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=xl64 height=19>Active</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=xl64>d</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=xl64 align=right>4</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=xl64 height=19>Discontinued</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=xl64>e</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=xl64 align=right>5</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=xl64 height=19>Discontinued</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=xl64>f</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=xl64 align=right>6</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=xl64 height=19>Discontinued</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=xl64>g</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=xl64 align=right>7</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=xl64 height=19>Discontinued</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=xl64>h</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=xl64 align=right>8</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=xl64 height=19>Discontinued</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=xl64>i</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=xl64 align=right>9</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=xl64 height=19>Active</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=xl64>j</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=xl64 align=right>10</TD></TR></TBODY></TABLE>

Since we want the formula system on Sheet2 to adjust automatically to changes in the above data area, we will first set up some dynamic named ranges.

Define Lrow by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=MATCH(REPT("z",255),Sheet1!$A:$A)

Define Status as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Lrow)

and Data as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$C:$C,Lrow)

Note that (a) the definition of Status also includes the corresponding header and (b) the definition of data comprises the status range as well as the heders.

Sheet2, column A, contains the processing...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><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: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=64>Active</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64> </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 align=right>3</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=xl65 height=19>People</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 align=right>3</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 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></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 align=right>10</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> </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></TBODY></TABLE>

A1: Active (which is a status value)

A2, just enter:
Code: 
=COUNTIF(Status,A1)

A3: People (records of interest)

A4, control+shift+enter, not just enter, and copy down"
Code: 
=IF(ROWS($A$4:A4)<=$A$2,INDEX(Data,SMALL(IF(Status=$A$1,
    ROW(Status)-ROW(INDEX(Status,1,1))+1),ROWS($A$4:A4)),
      MATCH($A$3,INDEX(Data,1,0),0)),"")
 
Upvote 0
whats the best way to also include the corresponding "Task" next to each row with "People"?
 
Upvote 0
abupu said:
whats the best way to also include the corresponding "Task" next to each row with "People"?
Click to expand...

Sheet2...

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><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: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=64>Active</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64> </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 align=right>3</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=xl65 height=19>Idx</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>People</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>Task</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 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 align=right>3</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>c</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 align=right>5</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>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>d</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 align=right>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 align=right>10</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>j</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> </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></TBODY></TABLE>

A1: Active

A2, just enter:
Code: 
=COUNTIF(Status,A1)

A3: Idx (indexing field)

B3:C3 house People and Task, the fields of interest.

A4, control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS($A$4:A4)<=$A$2,SMALL(IF(Status=$A$1,
   ROW(Status)-ROW(INDEX(Status,1,1))+1),ROWS($A$4:A4)),"")

B4, just enter, copy across, and down:
Code: 
=IF(N($A4),INDEX(Data,$A4,MATCH(B$3,INDEX(Data,1,0),0)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Forum statistics

Threads
1,224,586
Messages
6,179,710
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top