Formula Help - Column Listing

[srezac]

Hi,

I'm trying to create a formula that will allow me to automatically create a new list of items in a separate sheet or section on an excel sheet that meets a certain criteria. Like shown below; I want to be able to list the items in List 1 in a new list (like shown in List 2) in order and starting from row 1 in list 2. So I need it to look for all values in column B with a value of "N" and place that value in column A of the matching row in the new list. So it should look like this:

List 1

---A---------------B
---Item------------Animal?
1--Cat--------------Y
2--Dog--------------Y
3--Rug--------------N
4--Kangaroo--------Y
5--Chair------------N
6--Koala------------Y


List 2

----A
1--Rug
2--Chair
3
4
5
6

Hope this makes sense and hope someone can help.
Thanks,
Shaun

 

[jbeaucaire]

Here's one way by adding a key column to your dataset to watch your LIST sheet to see what you want to list:

Excel Workbook
	A	B	C
1	Item	Animal?	KEY
2	Cat	Y	0
3	Dog	Y	0
4	Rug	N	1
5	Kangaroo	Y	1
6	Chair	N	2
7	Koala	Y	2
List1

Excel Workbook
	A	B	C
1	Criteria	N
2	Count	2
3
4	List	Rug
5		Chair
6
7
8
9
10
11
12
13
14
List2

 

[MikeWx]

Is an array formula like this something you can use? Press Control-Shift-Enter after the formula in Cell D2. Copy it donw and it will show empty cells after there are no more Column A values that have an 'N' in Column B.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>List 1</TD><TD> </TD><TD> </TD><TD>List 2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Item</TD><TD>Animal</TD><TD> </TD><TD>Rug</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Cat</TD><TD>Y</TD><TD> </TD><TD>Chair</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Dog</TD><TD>Y</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Rug</TD><TD>N</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Kangaroo</TD><TD>Y</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Chair</TD><TD>N</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Koala</TD><TD>Y</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>{=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8="N",ROW($B$3:$B$8)),ROW(A1))-2),"")}</TD></TR><TR><TD>D3</TD><TD>{=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8="N",ROW($B$3:$B$8)),ROW(A2))-2),"")}</TD></TR><TR><TD>D4</TD><TD>{=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8="N",ROW($B$3:$B$8)),ROW(A3))-2),"")}</TD></TR><TR><TD>D5</TD><TD>{=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8="N",ROW($B$3:$B$8)),ROW(A4))-2),"")}</TD></TR><TR><TD>D6</TD><TD>{=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8="N",ROW($B$3:$B$8)),ROW(A5))-1),"")}</TD></TR><TR><TD>D7</TD><TD>{=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8="N",ROW($B$3:$B$8)),ROW(A6))-1),"")}</TD></TR><TR><TD>D8</TD><TD>{=IFERROR(INDEX($A$3:$A$8,SMALL(IF($B$3:$B$8="N",ROW($B$3:$B$8)),ROW(A7))-1),"")}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Mike

 

[T. Valko]

Hi,

I'm trying to create a formula that will allow me to automatically create a new list of items in a separate sheet or section on an excel sheet that meets a certain criteria. Like shown below; I want to be able to list the items in List 1 in a new list (like shown in List 2) in order and starting from row 1 in list 2. So I need it to look for all values in column B with a value of "N" and place that value in column A of the matching row in the new list. So it should look like this:

List 1

---A---------------B
---Item------------Animal?
1--Cat--------------Y
2--Dog--------------Y
3--Rug--------------N
4--Kangaroo--------Y
5--Chair------------N
6--Koala------------Y


List 2

----A
1--Rug
2--Chair
3
4
5
6

Hope this makes sense and hope someone can help.
Thanks,
Shaun

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3